Can you use the create item action to populate next business day?


Badge +8

Looking to update a field, upon completion, to auto populate next business day when the next review is done. Am I overthinking? Cassy Freeman


27 replies

Badge +16

Easiest way to calculate business days has some good stuff on it for you to have a look at.

Just replying to your email too.

Badge +16

What was your approach in the end? X

Badge +8

Well come to find out, they may do reviews on the weekends so I just created calculate date action for current date plus 1 day. I am sure I will need to update that at some point for the weekend and holidays (I saved the workflows you gave me and were referenced in the feed you provided) but I am rolling this out tomorrow to start on Friday.

I have to re-create everything I have done for 4 more sheets. Weekly, Monthly, Quarterly and Annual reviews. Ugh

The auto clearing of those might be a little tricky. I won't be able to use a date to reference, most likely. Daily is easy.

Badge +16

I'm sure you will do it!!

Badge +16

I have been dreaming about this issue last night (I think I may need to take a break from the forum haha).  Anyway I think your problem is easily resolved by adding an additional column to your main list (and all copies of it) called something like "Frequency" and having that as a choice field with Daily/Weekly/Monthly options.  Then in your site workflow you check to see the value of that field and based on that use the calculate date action to choose the next due date:

If daily = current date + 1 day

If weekly = current date + 1 week

If monthly = current date + 1 month

What do you think?

The archive workflows will be unaffected by this.

Badge +8

Haha. That seriously made me laugh out loud! And if my math is correct, you typed this at 5am!! You might get a break from me here soon. Our pilot program is over on June 3rd and they are locking us down until they put it fully in production. Not sure how long that will be.

I already have a frequency column with those exact options. I am super excited to try this now!! if this works, it will be a game changer lol

Badge +8

Hey Cassy

I am not sure how to put an action in an action. I don't want to mess it up. This is my workflow. The calculated date action in there now is just current date plus one. I imagine I am replacing this one.

185305_pastedImage_1.png

Badge +16

Under your create item you need to query the list for the frequency (using the ID in the collection)

The use switch to evaluate that and have calculate date on that

CalcDate.PNG

Badge +8

I love these switch actions. They look so cool and complex! Definitely need to learn to use those more.

Badge +16

Not complex look:

Switch.PNG

They are super powerful though.

Things to know about the switch:

  • Case sensitive.  if your item frequency is "daily" it will not match.
  • If no match, doesn't error.  If the switch doesn't find a match, it just skips past the action (which can cause issues if you are setting a variable)
  • Should really include the other branch for the above reason (I tend to use this to notify myself that the switch did not find a match so that I can revisit the config)
  • Should be used with choice fields where you can control the input (for the case sensitive reason)
Badge +8

Hi Cassy. For this query, do I need to filter to show the items when column frequency is equal to daily, or weekly, or monthly, etc?

186312_pastedImage_0.png

Badge +16

no you don't need to filter- the switch will inspect each item in the collection

Badge +8

Hi Cassy

Can you show me how you have the vTextFrequency variable setup? My switch is working great except for the date calculation. It's adding 1 day to all of my frequencies.

186327_pastedImage_0.png

Badge +8

186328_pastedImage_0.png

186332_pastedImage_1.png

186334_pastedImage_3.png

Badge +16

ok so I quickly knocked this together again:

query list for all items, put ID in collection vCollItems

QueryListCollection.PNG

for each item in vCollItems, put current ID into vIDCurrentItem

ForEach.PNG

Inside for each loop, query list again where ID = vIDCurrentItem and retrieve frequency into vTextFrequency

QueryListItem.PNG

Then keep your switch as it is

Switch.PNG

does that make sense?  you have the right idea but I am not sure you have grasped that your collection should be a collection of IDs only.  that collection has no idea about any of the other properties against the item.  So while we are looping through our collection of IDs only, it is necessary to query the list again for the specific item ID in the loop to get the other properties we need.

Does that make sense?

Workflow.PNG

Badge +8

Hi Cassy. This was very helpful! Thank you for taking the time to do this. You are correct, I was still struggling with understanding the collection ID. I also understand now I need to use a For Each action whenever I query. Makes sense now. I was missing it before and I would not have know to put everything in the For Each condition box. BUT...now my instead of my Due Date advancing one day, it is now advancing to 8/16/16 for all of my frequencies.

186363_pastedImage_1.png

186364_pastedImage_2.png

Badge +8

btw..I set up vTextFrequency as Single Line of Text variable

Badge +8

If I change the lookup in the Calculate Date action to a Workflow variable to Current Date, it goes back to updating the due date 1 day. Like it's not recognizing my switch when the list says Weekly or Monthly. I also have switches for Quarterly and Yearly but no not have items in the list with those results...yet. This shouldn't matter though right? Cassy Freeman

Badge +8

Yep...I replaced my other switch with a notification to me when a switch wasn't recognized and sure enough, I got an email saying no switch was found. I know you said it was super stingy about being exactly as the field in the list. Back to the drawing board to see what I did wrong.

Badge +16

OK good so you know why your date isn't calculating now.  in the email you send yourself, add in the vTextFrequency variable so that you can see what the switch is evaluating - you should then easily be able to identify who no match was found.

Badge +8

My vTextFrequency variable brings back nothing.

My date and time variables (that I use in my update field) brings back:

Annually Due Date: 1/1/1900 12:00 AM
Current Date 6/29/2016 4:20 PM

Monthly Due Date 7/29/2016 12:00 AM (this has the correct date but brings back 7/6/16 into my list)

Quarterly Due Date 1/1/1900 12:00

Real Time 6/30/2016 12:00 AM

Semi-Annually 1/1/1900 12:00 AM

Semi-Monthly 1/1/1900 12:00 AM

Weekly Due Dae 7/6/2016 12:00 AM

vTextFrequency Nothing Returned

Badge +8

And why is it running for every line item and not just the one that I work that is triggering the workflow?

Badge +16

oh my bad - I thought you were still doing a site workflow.  if this is a list workflow and you are looking at the current item then you will just need to configure the switch like this:

Switch.PNG

where the frequency I selected was the item property: frequency

ItemPropertyFrequency.PNG

why is this now a list workflow?

and how are you getting this to kick off every night?

Badge +8

CASSY!!! That worked perfectly! I also used an old blog of yours to change to Workflow Context instead of Workflow Data. I think that helped too.

This is a list workflow to move items and reset them real time. So as people are working them and putting the review staus into Passed, Pending or Failed.

I have attached my workflow. I think you would be proud of it.

Badge +16

yes I love your labelling!

now I know this isn't a site workflow you can remove the following highlighted actions as they are completely unnecessary.

move the switch to above the for each before you delete it (you can left click the switch image on the action and hold down, and drag to design node little square above for each).

deletethese.PNG

  if you don't delete these you are doing massive amounts of unnecessary processing each time.

so should look like this:

Workflow.PNG

Reply