How to: Automatic Reminder Emails Based on 2 Factors

1

Hello,

I’m trying to figure out how to create automatic reminder emails based on 2 concurrent factors: a dropdown list and a date.

I don’t know how to insert a code, but I can put in a screenshot (below). Essentially, I have a list of todos, and some of them are follow ups. I have so many that I need them emailed to me as a sort of (mental) filter. I want the item emailed to me if the row meets 2 criteria: status=scheduled and date=today. How do I do that? Seems like it’d be simple but I just can’t get it to work. Below is the formula I’m using . . .

57%20AM
Screen Shot 2019-10-31 at 11.43.57 AM.png1990×1008 87.7 KB

33%20AM
Screen Shot 2019-10-31 at 11.45.33 AM.png1188×1028 68.6 KB

2

Hi Ben, the correct syntax there is And(test1, test2), or alternatively test1 and test2 (without parentheses). So in your case just remove the parentheses and it works.

Having said that, given your use case you actually need to use OR instead of AND.

1 Like
3

I actually use the date function for both DUE and DONE (on a certain date). And one of my “status” options is “complete”. I wanted to make sure that in order to only get the right items that both of these criteria met. Would I still use “and” or does “or” cut it? I guess I’m not understanding the difference between the two, or just didn’t explain my use case scenario very well.

4

Reading through and noticed you wrote equals in your description and your formula shows not equals.

Status=Scheduled AND Date=Today()

This should work.

I think in this case you do need AND instead of OR.
OR would mean that only one of these criteria needs to be met, so all tasks that are not today would be included and all tasks that are not Scheduled would also be included.

5

@BenLee It’s in the “disable if” - you want it to be disabled if the criteria is not met so the ‘not equals’ is correct.
And it should be OR - you want it to be disabled if either of the criteria is not met, or in other words - you want to enable it only if both criteria are met

6

2 Things:

  1. I changed to “or” and the button became grayed out (non-clickable). It’s clickable with “and” when I change it back.
  2. I think I’m doing this all wrong :frowning: . . .

I was trying to get an AUTOMATIC email, but what it looks like I’ve done (notwithstanding #1 above) is created a button which is only available when x and y conditions are met. I should probably be doing this with the gmail automation pack, correct?

7

@Asaf_Dafna, you’re totally right. I missed that this was in the Disable part of the button. Using OR is the way to go as well as !=.

Thank you for the correction!

8

@Benn_Bennett, are you able to share a doc, even if it’s a sample doc that you recreate and share with “view and play”.

10
11

It looks to be working properly if I use this for the formula…

thisRow.[Task Status] != "Scheduled" OR thisRow.[Due/Done Date]!= Today()

12

Does this send an automatic email on the date or does the button have to be pressed? I’m aiming for the former. Sorry for my ignorance, I’m just starting to use the gmail functionality.

13

You need to set an automation to push all buttons in this table every day in the desired time (it will only push the buttons that are not disabled)

14

That makes sense, perfect!

15

Ok, last question. How do I format the “content” of the email to include the text of more than one column? I currently have (that works):

thisRow.[Task].Filter(thisRow)

I’ve tried:

thisRow.[Task].Filter(thisRow) AND thisRow.[Student].Filter(thisRow)

but that gives me an email that says “true”.

16

I think that something like this :

Contatenate(ThisRow.Task," - ",ThisRow.Student)

might help :wink: .

Depending of what you had in mind for the text of the content :slight_smile:, how it’s supposed to look like :wink:

17

Thanks! This works on my test doc but not on my actual document (which I can’t share for privacy reasons). The “student” column/portion of the formula on my original document is the problem, and I suspect it’s because it’s being called from another sheet. Is there any way around that?

18

Could you try this :slight_smile: ?

Contatenate(ThisRow.Task," - ",ThisRow.Student.Student)

In that part ThisRow.Student.Student the second .Student should get the name of the student from your list/lookup field (from its origin point) :slight_smile:

Or maybe this :

Contatenate(ThisRow.Task," - ",ToText(ThisRow.Student))

But there might be other ways to do this too :slight_smile:

19

@Benn_Bennett, I was looking at this again and one of the easiest ways to include everything in an email is just emailing a particular section. For the email button, you can set the content to be a section.

If you create a new section, add a view of your table, then you can filter that view for the Date being equal to Today and the Status being equal to Scheduled.

Then you only need to send one email and it will have anything listed in the table. If you have 5 items, you’ll see the table of 5 items.