Now the functions in pivot table formula are only date/time related. Can you add some more operators like "IF(condition, result when true, result when false)", and PartQuarter()?
Unfortunately, there is no information on exact functions or operators will be added nor ETA we can provide you with.
The possibility of adding this functionality is being reviewed by our R&D team and they planning to enrich the existing functionality in future releases.
I am trying to build a process that will subtract 2 dates and record the difference in an integer field for all the records in the CRM. But for some reason, I can not get the result as required.
Formula value uses standard C# syntax. In your case subtraction of 2 datetime fields gives you a TimeSpan, you can get the number of days in the TimeSpan by using Days property (TotalHours if you want to calculate difference in hours).
So to get the difference between 2 date fields you can do the following formula:
([#Date 1#] - [#Date 2#]).Days
You can assign this value to a business process parameter and them with modify data update the needed field with this parameter.
Thanks for the reply. I am trying to build a business process to run every morning and that process should read the two dates and update field with the difference in those dates in days.
Can you guide with how to build such a process?
Currently it's as below and is really working. Just can't figure out how to read all the records and then update the field based on the current values of the record.
1) You can add a boolean column to your object (Something like Processed) and loop through all records, which have processed = false. After that you can read the first record in the selection with the mentioned filter, process date columns and after that set the Processed to true. You would need to loop until there is a record that has processed = false (count > 0).
After all the mentioned operations you would need to set Processed of all records to false (one Modify data element).
2) You can read all records as a collection, pass it to script task and operate with them in a script task. Here is some documentation on using collection:
When I run the process and put a constant number such as 07 or 50 or any other value, the business process updates the value in the specified field. But when I try to compute the value using the dates, it doesn't work. I am using the same formula as you mentioned earlier in the first comment (date2 - date1).Days.
Could you please specify what do you mean by 'doesn't work'? It gives you an error or the value is incorrect? In first case please give me an error message, in the second case - what dates do you input and value do you get when using the formula? Also, could you please send a screenshot of your formula and a part of process flow in which you use it?
I am neither getting an error nor any other response. The integer field that I am trying to update shows value = 0, which I guess is the default value of field of type integer.
Probably you are calculating those fields using the dates of one particular record that has one of the dates not filled in, and not for each record separately. Please write to the support@bpmonline.com for more detailed analysis. I've double checked the formula itself and it works fine, probably the issue is in the process flow.
How do I parse a String using a formula or condition? For example, if I want to do a comparison of the last three characters of a String, could I use [#Example String#].Right(3)=="-00"? Is there a length function? Please provide the available functions.
How do I test if a String Parameter in a process has not been filled in using a Formula? For example, the following are not working because they do not evaluate to true when the parameter is not filled in:
Implementation of such logic would require a script task. Alternatively, you can use conditional flows in order to avoid script tasks but still use the if condition equivalent.
