Do Forms always have to add a new line?

I am looking to create a visit tracker for our sales reps. All of their accounts are already in the master data sheet, and I am wondering if there's any way to create a form which would use the dropdown for Account Name as a reference and then update the Last Visited cell, rather than creating a new line each time the form is submitted?

Would appreciate any guidance!

Find more posts tagged with

Forms

Accepted answers

All comments

MarceHolzhauzen

Hi @autumnleaves

There are a few ways to do this.

You can schedule an update request on a regular basis (depending on how often the sales agent visits the client) that require them to update the column. This will ensure that the sales agent gets the request as well as updates existing fields.

Or - here's a video that might inspire you to try something different
https://www.youtube.com/watch?v=oGnSdQBP53o

autumnleaves

https://community.smartsheet.com/discussion/comment/458645#Comment_458645

Thank you @MarceHolzhauzen! How would I do this so it is different recurrence frequencies for each line? For instance, some customers might require a quarterly visit, others monthly, others annually.

MarceHolzhauzen

Add a column to your sheet called "Customer Visit interval" and then capture these intervals in that column.

There are a few ways to do this from here.

You could also add a column called "Next visit due", then take the last visited date and add the correct amount of days to that date to fall within the period

That formula would look something like this:

=[Last Visit Date]@row+if([Customer Visit interval]@row="monthly",30,if([Customer Visit Interval]@row="quartely",90,if([Customer Visit interval]@row="annually",365)))

Formula explained:

[Last Visit Date]@row:
- Refers to the last recorded visit date for the customer.

IF([Customer Visit interval]@row="monthly",30,...):
- This part checks the Customer Visit Interval and adds the appropriate number of days to the last visit date:
  - If the interval is monthly, it adds 30 days.
  - If the interval is quarterly, it adds 90 days.
  - If the interval is annually, it adds 365 days.

Now, trigger your automation to send when the "Next visit due" date is reached.

OR

If you don't want all of these columns, you could create an automation for each of the visit intervals and using the triggering action to trigger at the corresponding intervals

Toodeloo

Andrée Starå

https://community.smartsheet.com/discussion/132163/do-forms-always-have-to-add-a-new-line

Hi,

I hope you're well and safe!

To add to Marcé's excellent advice/answer.

Another option could be to have a main sheet with all the accounts and then a second sheet which collects all the form submissions, and then using cross-sheet formulas you can collect the latest visit for each account.

Make sense?

Would that work/help?

I hope that helps!

Be safe, and have a fantastic day!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅ Did my comment(s) help/answer your question or solve your problem? Please support the Community and me by marking it - Insightful 💡- Vote Up ⬆️ - Aweseome ❤️ - or/and as the accepted answer. It will make it easier for others to find a solution or help to answer! I appreciate it, thanks!

autumnleaves

https://community.smartsheet.com/discussion/comment/458652#Comment_458652

Thank you @Andrée Starå that's helpful too! Could you please give me some guidance on what those formulae should look like?

Many thanks!

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.