Checking for a week number in a workflow expression

[Guido1982]

I have the following setup:

• SalesOrders have two fields:
  • A dropdown with numbers 1 through 52 for the week no's.
    
  • A dropdown with the coming 10 years (so 2018, 2019 and so on).
    

Now I'd like to setup a workflow that runs once a week. The purpose of this workflow is to:

• First check if the selected year is equal to the current one.
  
• Then check if the week no. is lower then the current week no.
  

If those conditions are met, an e-mail should be sent out. Can I do this through the workflow expression engine and if so, does anyone have some pointers on the syntax?

[joebordes]

This condition should work:

week_field smaller than (workflow expression) format_date(get_date('today'),'W')

[Guido1982]

Thanks! So basically format_date(get_date('today'),'W') outputs the current week no.?

[joebordes]

correct!

you can use any valid PHP date modifier

[Guido1982]

Would this also work in reports by the way?

EDIT:

I have a small problem in the week comparison field. It doesn't show me the 'smaller than' option in the dropdown. So now I have something like 'productionweek is if so_productionweek < format_date(get_date('today'),'W') end'. Don't know if that will work.

SECOND EDIT:

Changed that to:

if so_productionweek < format_date(get_date('today'),'W') true else false end. Hopefully the workflow expression now returns true or false, making the condition functional.

THIRD EDIT:

OK, my second edit broke the application, so no dice there.

[joebordes]

What type is the so_productionweek field?

You can use the evalwf.php script to evaluate the workflow conditions against different records and see if it will work or not.

[Guido1982]

UI type 15, picklist.

Thanks! Will check this.

[Guido1982]

I have the weirdest thing here:

I created some workflow expressions, none of them worked. I created this one just for testing:

Code:

if salesorder_no == 'ORD1800484' then 'A' else 'B' end

Keep in mind, the salesorder no. is correct for the SO I'm testing. So I would expect the expression to evaluate to 'A'. But when I use 'evalwf.php' on it, I get this query:

Code:

SELECT vtiger_salesorder.salesorderid FROM vtiger_salesorder INNER JOIN vtiger_crmentity ON vtiger_salesorder.salesorderid = vtiger_crmentity.crmid WHERE vtiger_crmentity.deleted=0 AND ( (( vtiger_salesorder.so_productionyear = '2018') and ( vtiger_salesorder.so_productionyear = 'B') )) AND vtiger_salesorder.salesorderid > 0

The second to last where clause should check for 'A', not 'B'. It's like every expression I write results in the 'else' clause. What am I doing wrong?

---

Hmm, it looks like the workflow is working when I test it. The eval script doesn't do a live check maybe?

---

By the way, I used a little trick to check the week, since 'smaller then' was not available:

I used the field 'so_productionyear' for comparison to check against (using 'is'). Then I used:

Code:

if so_productionweek < format_date(get_date('today'),'W') then so_productionyear else '1000' end

So basically, when then productionweek is in the past, I made productionyear check if it was itself, otherwise I returned something that will never be true ('1000').

[joebordes]

If you get the SQL I understand that you are creating a scheduled workflow. Scheduled workflows do not support expressions because they would need to be translated to SQL which is VERY complex. In scheduled workflows only basic conditional operations that can be directly translated to SQL are supported.

I would suggest you create a numeric custom field and fill it in with the value in the picklist with an update field workflow and then create the scheduled workflow based on the numeric field.

[Guido1982]

Ah, I didn't know that. Could I hide the field that the first workflow task auto-fills? Or are hidden fields excluded from workflow comparison?