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()?

4 comments

Hello Andrew,

 

Thank you for your suggestion. This idea has already been registered and we are planning on implementing it in our future releases.

 

Kind regards,

Anastasiia

Hi Anastasiia, thank you for your prompt response. May I know more detail for what functions or operators will be implemented and when?

Dear Andrew,

 

 

Thank you for your question.

 

 

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. 

 

 

With very best wishes,

Anastasiia

Hi, do you have any update on this topic or a way to use operators in pivot table formula?

Thanks in advance,

Adrien

Show all comments

Hi!

with next formula: [#Leer datos lead.Primer elemento de colección resultante.Punto Información#] != Guid.Empty

I always receive Operator no valid as error. I have reviewed documentation as it is the same syntax.

Any idea of the problem?

Waiting for your help, thanks a lot in advance!

Like 0

Like

4 comments

Hi,

"Punto Información" is a lookup?

Check that, if is not a lookup the formula would like != ""

Regards

juan cruz,

Hi Juan!

Punto información is a lookup :(

Thanks for your help!

sarauzo,

Could you please send an email to support@bpmonline.com? We will be glad to help!

Best regards,

Angela

Angela Reyes,

Thanks Angela, i will resend to support

 

Best!

Show all comments

Hi,

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.

 

 

Like 0

Like

14 comments

Hello, 

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. 

Best regards,

Dennis

Dennis Hudson,

 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.

 

aaykay,

There are 2 options how you can do that: 

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: 

 https://community.bpmonline.com/questions/use-collection-parameter-script-task

https://community.bpmonline.com/questions/use-script-task-update-column-read-data-result-collection

Best regards, 

Dennis 

Dennis Hudson writes:

You can add a boolean column to your object (Something like Processed) and loop through all records, which have processed = false.

 

How do I loop through the records in the business process?

aaykay,

It should look similar to the screenshot below:

http://prntscr.com/nr79cf

Best regards,

Dennis

Dennis Hudson,

 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.

Can you tell what might be going wrong?

aaykay,

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? 

Dennis Hudson,

 Here are the screenshots:

1. Complete Process

2. Date formula for filled

 

3. Date formula if closed:

 

4. Final modification:

 

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. 

Dennis Hudson,

 I omitted conditional flow and now I am getting the following output when I run the process for only status = Closed

The variable "DateDifference" now has value = -737,065

aaykay,

It happens because one of the date values is empty, if you fill in both date/time fields you should get a correct result. 

aaykay writes:

The variable "DateDifference" now has value = -737,065

Dennis Hudson,

Both the fields are filled. Please find the screenshot below.

aaykay,

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.

Best regards,

Dennis  

Dennis Hudson,

 You can find the business process in the screenshots above. Even I think something is not upto the mark with process but only if I knew.

aaykay,

It's hard to determine what's wrong here from screenshots. I think it will be better to contact support. 

Show all comments

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.

Like 0

Like

1 comments

Dear Janine, 

You can perform all standard C# methods in formula (including all string operations). In your case you can use the following:

[#Example String#].Substring([#Example String#].Length - 3) == "-00"

or

[#Read data 1.First item of resulting collection.Full name#].EndsWith("-00")

Best regards,

Dennis

Show all comments

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:

[#MyStringParameter#]==""
 
[#MyStringParameter#]==String.Empty

Is there a function for String length?

Like 0

Like

2 comments

I've used the following: 

string.IsNullOrEmpty([#MyStringParameter#])

However, I believe I've used what you're using as well (but might have wrapped it in parentheses)

Ryan Farley,

String.IsNullOrEmpty([#MyStringParameter#]) works.  Thanks!

Show all comments
Question

Is there an if then else or case structure that may be used in formulas?

Like 0

Like

2 comments

Hello.

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.

https://academy.bpmonline.com/documents/technic-bpms/7-13/conditional-f…

Best regards,

Matt

You can use ?: operator in formulas. For example, a forumla setting some process parameter that stores an email address:

[#IsSupervisor#] == false ? [#Lookup.Email addresses.Andrew Baker (sample) <a.baker@ac.com>#] : [#Lookup.Email addresses.Supervisor <supervisor@mycompany.com>#]

 

Show all comments