Formula with Update Requests

Arnis

Hello all,

Im using Smartsheet to automatize a lead process with around 30 people involved. We get Leads in an external network, I add them to my Smartsheet Sheet and then the one in a contact column gets an automatized notification with the new lead data. After 5 days I need an update with est. values and so on, so I made a column with a formula that gives out a 1 if it is 5 days after the initial notification and 0 if not.  If it is 1, then an automatized update request is sent to the one responsible to give me the values I need for my reportings. 

Now to my setup: I made the update request rule as follows: trigger when a row is updated, then check if the column with the 1 and 0 in the "5 days after initial notification" is 1. Afterwards check if the Columns with the needed Values are empty. Then send the update request to the Person in the contact column Responsible.

Now the problem: as the rule only starts if a row is updated it does it when the "5 days after initial notification" column changes to 1, but also if anyone updates their values in their rows per update requests. So now the Team gets more than one update request for the same row, if somebody updates their rows in the same day. 

Do you have any ideas if it is possible to make a new trigger with the formula: IF there is an update request in the row do not send any other update requests?

I changed the delivery right now to only once per day, had it to imediately before. But dont really know if it will help.

Kind regards

 

Arnis

campbesj

When setting up a rule, you have the option to select "when there are changes in specific columns".  Would this be acceptable, or I may be misunderstanding the issue.

Arnis

Thank you!

I concentrated on the rule itself and totally ignored that part of the rule. A case of - why the easier way if you can do the complicated way

 

Kind regards

 

Arnis

Anthony Lunn

Hey there. Could you share this formula with me? I am doing something similar. 

I am trying to ultimately set up my sheet to automatically send out Update Request 3 days (or whatever I set) within the "Bid Date" loaded into the sheet. It would be ideal if this update request would be emailed to the contact in "Sales Rep" column and copy (cc) me in the emailed Update Request for all of them. I was told this was possible from a conceptual standpoint when updating to Business edition account... 

But it seems like you have a workaround using a formula setting a 0 or 1 trigger that the update request can work off of. Never thought of that simple approach. Thanks in advance!

Arnis

Hello Anthony,

 

here is the formula:

=IF(OR(TODAY() - [Deal accepted]1138 = 5, AND(TODAY() - [Deal accepted]1138 = 4, WEEKDAY(TODAY()) + 1 = 7), AND(TODAY() - [Deal accepted]1138 = 3, WEEKDAY(TODAY()) - 5 = 1)), 1, 0)

 

the formula takes in account also saturdays and sundays as I am always sending the update request 5 days after the accetpted date out. Sometimes those days are saturdays and sundays, so with this Formula on Friday all rows get set to 1 where 5 days are on saturday and sunday.

Afterwards the rule is set to send the update request if the Column with the formula changes to 1.

be careful here as it only happens when you open the sheet and save it again and do not leave the Sheet open when going sleep mode on the pc as it will mistake the formula a bit and send more update requests than needed

I hope you can make smth out of this.

oh and here is my first formula without the saturday sunday part: =IF(TODAY() - [Deal accepted]318 = 5, 1, 0)

Kind regards

Arnis