Reminder automation

bgarrett

Hi!

I'm trying to set up an automation for weekly reminders if certain conditions are met / not met, and from what I can tell everything has been entered correctly, yet when I run it to test it seems to fail. Any help is appreciated! Here's a description of the situation and what I have set up:

Several farmers markets have to fill out a form every week, in which they provide their market's name and the date of the market for which they are reporting data. They operate every Saturday. They are expected to fill it out within 7 days of market, so the day before their next market (day 6) is when I would like them to receive a reminder.

TRIGGER: When a date is reached / every week on Friday

CONDITIONS: Where MARKET NAME is equal to [Market Name] AND where MARKET DATE is not in the last 6 days

I've tested this with the farmers market that my organization runs, and I know it's a couple weeks behind on reporting and therefore I should receive a reminder when I run the automation, but nothing happens.

JamesB

@bgarrett

I just recreated your scenario in my SS and I think this will work.

=COUNTIFS({Market Form Range 1}, [Market Name]@row, {Market Form Range 2}, [Market Date]@row)

As long as your Market Name and Market Date are the same on both sheets, you get a true or 1 result which will check the box, if they do not then it is false or 0 response, which will uncheck the box.

JamesB

@bgarrett Looking at your automation this would be my question. In the conditions you have where MARKET NAME is equal to [Market Name]. For the purposes of Smartsheet Community confidentiality, did you put Market Name instead of an actual Market Name? If not, the automation is going to look in the Market Name column for a matching name, so it would look for "Market Name" to exist in one of the cells of that column.

bgarrett

@JamesB

Yes. It is set to look in the Market Name column for a matching name. Instead of [Market Name], in the automation it is the actual name of the farmers market for which the automation is set. Like this:

CONDITIONS: Where MARKET NAME is equal to XYZ Farmers Market AND where MARKET DATE is not in the last 6 days

Since posting this, I discovered reminders in my spam folder, so the good news I suppose is that it IS sending something, but I still believe it's not working the way I need it to. One market filled out the form for their most recent market date and then received a reminder for that date, which shouldn't have been the case since they were reporting on time...

JamesB

@bgarrett Can you share a screenshot of your automation and blur out any confidential data. I would also suggest that you create a copy of your sheet and use this as your test sheet. Instead of sending emails to your market reps, send the emails to your self and manually manipulate the dates so that you can test the automation conditions with different rules, in the test of your email make a notation of what automation it is so you can see which one is working as intended.

Also, how is the market date generated on your sheet?

bgarrett

@JamesB The market date is not generated, but instead filled out by the market when they submit the form. It is a date only column.

JamesB

@bgarrett Ok how about the next actual Market Date, can we use that as the criterion. You could create a date column that uses a formula to subtract 1 from the next actual market date and then use that as the trigger for the automation. This theoretically would also negate the reliance on the market operator from sending in a form with a date you need to trigger your automation?

bgarrett

@JamesB I don't think that negates the form as a trigger, unless I'm misunderstanding. If "next actual market date" is a formula that works off of "market date," then it is blank unless "market date" contains a date.... and the only way it contains a date is if the form is filled out.

I get what you're saying, though, about having some sort of a helper column. The problem I see is the sheet isn't for one singular market, it's for a dozen that will be upwards of forty come Spring that operate on various days of the week rather than just Saturday. A static "next market date" column would work if it was a sheet limited to one market....

Which brings me to this related but separate question: I HAD automations that copied rows onto other sheets so that each market had its own sheet. However, I noticed that if a cell was edited on the main sheet then it did not automatically change on the individual market's sheet, so I instead switched all of these to reports. Is that a correct observation/downside of the copy row automation?

JamesB

@bgarrett That is a correct observation on the copy row automation. Getting back to your reminder automation. Trying to understand how everything is generated to set your automation to execute as expected.

Is the market submitting a form on when the next market will be and the date it will occur? or is your team setting the next market date for the market operator?

bgarrett

@JamesB

Is the market submitting a form on when the next market will be and the date it will occur?

-- No, nor will it. Having the market tell us when the NEXT market date will be requires adding to the form the markets are filling out, which we will not do because it is long enough and populated with questions required by the funder, and is also pointless because we already know the markets' schedules.

But even if we are "setting the next market date" for the market, that requires use of a formula that requires the form being filled out, does it not?

JamesB

@bgarrett I would use a helper sheet along with a form sheet. Since you know the market dates then I would use an automation on a sheet that has the dates when the market will occur to to direct the market operator to fill out the form, which would come in on a separate sheet. Then you can use formulas to look for the form being submitted by the market operator to give you a checkbox to show they have submitted the form. You can also use this sheet to send the reminder the day before the market occurs.

I apologize if I am not understanding how the whole process works, I am just trying to think of a workable solution to the issue you are having.

bgarrett

@JamesB No apology needed! It's not the simplest situation. Since there will be so many markets participating in this, the goal is for us to be as hands off with data collection as possible. Data for 1/6 is due by 1/12, data for 1/13 is due by 1/19, etc. The ideal is for them to get the reminder on 1/12, for example, saying "this is the last day before you're late," and then once that automation is functioning properly I can set something up for me, or whoever, to receive a notification a few days later saying "this market was late."

A helper sheet.... Ok, that's a possibility! I'm not entirely sure how that will work, but I'll play around with it and get back to you! I appreciate the help so much.

JamesB

@bgarrett Your helper sheet would be a list of the Markets, Market Owners and Dates. Then the automation would be based off that data. Within the automation, when you send the reminder, provide a link to the form. Within the helper sheet, create a checkbox type column, and then use a if/index/match formula to collect the data based on the market, market owner from the form submission sheet. If it finds the form has been submitted, have it set the checkbox to 1 for checked. You can use this in your automation to negate the form being sent.

bgarrett

@JamesB I'm having trouble figuring out how to set up that formula, though. Any help there?

JamesB

@bgarrett

I assume you know how to do cross-sheet references. Your formula would be like this.

=if(And({Market Name}=[Market Name]@row,{Market Operator}=[Market Operator]@row),1,0)

{Market Name} = Cross sheet reference to your form sheet Market Name Column

{Market Operator} = Cross sheet reference to your form sheet Market Operator Column

For this formula to work though you need symmetry in the Market Name and Market Operator on Both Sheets. If possible I would recommend a drop down list on both sheets with matching names.

if you cannot do this because you do not want the person filling in the form to know the names of other operators or markets, you will need to determine another way to align this, or possibly use a contains formula.

bgarrett

@JamesB Thank you! Yes, I've done some with cross-sheet references and Index/Match before, but I'm still very new at it.

Market Names (Firm Names) are drop downs on the form, so they are always the same and can be matched. Market Manager (or operator!) isn't a drop down, though, because it might not be the same person filling out the form every single time. There's also a lot of turnover with market leadership, so we'd be updating the drop down over and over all year. Is there something other than the manager/operator name to use?

Here is the source/form sheet for reference:

And here is the helper sheet so far:

On the helper sheet, the Market Date column is pre-filled with dates all the way through the year/season, and I've been trying to put the formula in the "Submitted?" column. I've been trying to make it so that if the Firm Names match on each sheet, and the dates match, then it shows as submitted, but again I'm very new to this.

JamesB

@bgarrett Since you have the firm name and the Market date on both sheets those can be the criterion.

Use the following formula in your Submitted? column. You will need to type it so when you get to the {Firm Name} and {Market Date} columns you can create the sheet references.

=if(And({Firm Name}=[Firm Name]@row,{Market Date}=[Market Date]@row),1,0)

When choosing to reference another sheet when building your formula, click on the column name header and not an individual cell, this will cause the formula to reference the entire column.