-
busi937Asked on September 4, 2024 at 7:09 AM
Hi there,
I need assistance with integrating an excel spreadsheet with dates of registration and dates of expiry. We would like to have reminders must be sent out automatically to both us, Serenity and our affiliates.
The purpose of this is to prompt affiliates to renew their membership before the specified expiry period which will be captured in the spreadsheet.
-
Arghya JotForm SupportReplied on September 4, 2024 at 8:03 AM
Hi Busi,
Thanks for reaching out to Jotform Support. May I know, do you want to capture dates from an Excel file? Or do you want to send the fill-up dates and reminder dates in an Excel file? You can integrate Google Sheets directly in your form, or you can export your submission data into an Excel file. Can you explain a bit more about what you like to do, so I can get a better idea of what's happening? Also, can you send me the link to your form, so I can take a look at it for you?
Once we hear back from you, we'll be able to help you with this.
-
busi937Replied on September 5, 2024 at 2:03 AM
Hi there,
I would like to capture registration dates from an Excel file and prior to expiry of the registration date, have the system automatically send reminders to affiliates prompting them to renew their registration.
-
Ivy JotForm SupportReplied on September 5, 2024 at 3:55 AM
Hi Busi,
I understand your question, but I’ll need a bit of time to look into this. I’ll get back to you as soon as I can. Thanks for your patience and understanding.
In the meantime, feel free to reach out if you have any questions.
-
busi937Replied on September 5, 2024 at 5:19 AM
Thanks Ivy. I will await your response.
-
Ivy JotForm SupportReplied on September 5, 2024 at 5:59 AM
Hi Busi,
Unfortunately, you can't send data directly from Excel to your form table in Date format. Therefore, you can't send a reminder email via Import Table feature. However, as a workaround, you can convert your Date data in Excel to text format and send it to your form with the Spreadsheet to Form widget. Then, you can convert these texts to dates and send an Autoresponder to the user on that date.
I create a demo Excel sheet. It contains Registration Valid, Registration Expires, RE EXAM (Date Completed), Email columns. Therefore, in order to transfer these elements to the form correctly, you must first convert the written dates to text format by looking at the guide in this link and you must put "-" between the fields. You can check out my Demo Excel Sheet in the screenshot below:
Now, let's send this data into your form:
1. Click on Add Form Element on the left side of the page and select Short Text field.
2. Then, edit its name as in the same way on the Excel sheet.
3. Repeat this steps for all the column in your excel that you want to view in your form.
Now, let's convert the short text field into Date field:
1. Click on Add Form Element and drag and drop the Date Picker element.
2. Type a name for the field.
3. In Form Builder, in the orange navigation bar at the top of the screen, click on Settings.
4. Click on Conditions on the left side of the page and select Update/Calculate the field option.
5. Set up the condition as I show you below and click on Save.
Now, let's set up a Reminder Email day to send:
1. Click on Add Form Element and drag and drop the Date Picker element.
2. Type a name for the field.
3. In Form Builder, in the orange navigation bar at the top of the screen, click on Settings.
4. Click on Conditions on the left side of the page and click on Add Condition and select Update/Calculate the field option.
5. Set up a day before Expired day. (I set up 30 days before Expired Day as an example) Click on Save.
Now, let me show you how to send a data into the form with Spreadsheet to Form Widget:
1. Click on Add Form Element and under the Widgets search for Spreadsheet to Form and drag and drop it into the form.
2. In the menu opens on the right side of the page, upload your Excel Sheet.
3. Set up the Access code column and click on Update Widget.
Now, let's set the Autoresponder Email that will be sent on the Reminder Date we created:
1. In Form Builder, in the orange navigation bar at the top of the screen, click on Settings.
2. Click on Emails on the left side of the page and click on Add Email.
3. Select Autoresponder Email.
4. Click on Advanced tab and in the Send Email Later dropdown menu , select Reminder Date.
5. Scroll down the page and click on Save.
That's it. You can check my result in the screencast below:
Give it a try and let us know if you have any other help.
-
busi937Replied on September 5, 2024 at 1:15 PM
Thanks, Ivy. I will give it a try.