I have a scheduled workflow that will be kicking off once a month. I need to know what the Calendar quarter is at the start of the workflow (standard Jan-Feb-Mar = Q1 etc.) and store it in a workflow variable. Any ideas? Am I limited to the list of inline functions provided in the expression builder or can I put any javascript there?
Solved! Go to Solution.
You have a couple options.
1. You could follow the following thread to be able use ceiling and calculate a quarter CEILING(fn-FormatDate(Current Date, "MM")/3). They don't give me access to the Query XML action, so I couldn't try it out.
2. You could use a couple of regular expressions. Make some parallel actions (optional). Add the four regular expressions, one for each quarter. I used the input text of the variable Month, but you could just put the following expression in there and save a step: fn-FormatDate(Current Date, "MM").
3. You could use a switch. Create two variables, one for month and one for quarter. Both single line of text.
Use the Set variable action to set month equal to fn-FormatDate(Current Date, "MM").
Create a switch and create a branch of 01-12 (the leading zero is important). Then in each branch set quarter equal to whatever you'd like.
4. You could use the string builder. It would take a bunch of nested replace functions, but it is doable.
fn-Replace(fn-Replace(fn-Replace(fn-Replace(fn-Replace(fn-Replace(fn-Replace(fn-Replace(fn-Replace(fn-Replace(fn-Replace(fn-Replace(fn-FormatDate(Current Date, "MM"),"01","Q1"),"02","Q1"),"03","Q1"),"041","Q2"),"05","Q2"),"06","Q2"),"07","Q3"),"08","Q3"),"09","Q3"),"10","Q4"),"11","Q4"),"12","Q4")
I ran into this same issue. I was not overly fond of the four solutions presented, so I kept looking for alternatives, and believe I have a fifth solution.
You can utilize a Query Excel Services block. I created a "WorkflowHelper.xlsx" in my Shared Documents. I put the fancy math into the Excel spreadsheet, and then just query the spreadsheet. The query block allows you to update cell values before retrieving the results.
I don't need to run this very often, so I don't see it becoming a resource issue. However, I don't know what the impact of this method would be if the workflow were to run often.