How do I do an if else in formula mode? I want to say something like.. If the value of field is x return "xxx" if value of field is y return "yyy"
for example:
if(_('data.quickbooks.lookup_customer1.CurrencyRef.value')="CAD":"GST") but how do I specify what I want the field to be if the if is false?
Best Answer
A
Adam Goh
said
almost 7 years ago
Hi Peter,
The best way to do this is not to use IF conditions, but rather using a lookup table. By matching the Department, value to a certain value, you can say ID field should take on this specific value.
Here's a brief example :
What this means is that Closed will take the value of false if the Status pill has the value of new, pending, or solved. If the Status field is closed, then Closed field will take the value of true.
You can do a similar one with the Department data pill in the ID field. You can read more about lookup table and more on our reference guide here.
1 person has this question
Adam Goh
said
almost 7 years ago
Hi Ullaco!
Yes you can definitely do that. Here's an example of it:
? follows with the value if it is true while : follows with the value if it is false.
P
Peter Kohler
said
almost 7 years ago
I need something a little more complex, more like an If Else If statement.
I have an input field on my trigger for 'Department' which I then need translated to an ID to send on to Salesforce. How do I build a formula to waterfall down the list of 7-8 items?
if (Department <> null) {
if (Department == "A") {
ID = "123" }
else if (Department == 'B') {
ID = "456"}
else if (Department == 'C') {
ID = "789"}
and so on......
)
});
1 person likes this
Answer
Adam Goh
said
almost 7 years ago
Hi Peter,
The best way to do this is not to use IF conditions, but rather using a lookup table. By matching the Department, value to a certain value, you can say ID field should take on this specific value.
Here's a brief example :
What this means is that Closed will take the value of false if the Status pill has the value of new, pending, or solved. If the Status field is closed, then Closed field will take the value of true.
You can do a similar one with the Department data pill in the ID field. You can read more about lookup table and more on our reference guide here.
S
Saul Macht
said
over 5 years ago
Hi,
Thanks for the Look Up table formula! However, I have a more complex use case. How can I use two conditions in the formula? For example, If I want differing inputs based on different combinations of Status and Account?
I need a much more complex CASE or IF Else statement.
Basically I search for a record, If the record doesn't and some other condition is true then I search for it using another method, but if that condition is not true I try yet another method, if neither of those give me the record I then search using another method but only if another condition is true, etc.
The number of nested and repeated IF statements and searches I am having to in causing a lot of confusion, and then I have to figure out which one succeeded and to use in another step.
I could do this pretty easily with a nested IF Else statement, although even then it's complex.
M
Mark
said
over 5 years ago
A lookup table is no use as I am dynamically looking up things.
Patricia Guevarra
said
over 5 years ago
Hi Mark,
Just to clarify, when you say you would like to search using a different method, do you mean that you want to search using a different parameter based on certain conditions? As a simple example, you want to search based on Name if condition A is true, or Email if condition B is true?
M
Mark
said
over 5 years ago
Yes. For example I get a contact from Infusionsoft, if the email exists I lookup there contact in Xero based on email, if that doesn't return a contact and there is a company name for the contact in Infusionsoft I look up a contract in Xero with that company name, if that doesn't return anything or there is no company name then I check for a linked company in Infusionsoft and search based on that etc
So I'm first checking if the search parameter exists before I search for a contract. If the parameter direct exist or the search returned no result I try the next.
I have to be careful not to use a parameter it result that doesn't exist or an item from a step that never ran.
Maybe there is a much better way to do this in Workato?
Patricia Guevarra
said
over 5 years ago
In the case that the pill is empty or is coming from a step that wasn't executed, you don't need a formula. For example, if you fill in both Contact Name and Contact email, and the pill mapped into Contact Name is empty, then it will only search by the Contact Email you provided. In the case where nothing is returned, there is no formula for this and it would be necessary to use conditional actions.
Ullaco
If Else in formula mode
How do I do an if else in formula mode? I want to say something like.. If the value of field is x return "xxx" if value of field is y return "yyy"
for example:
if(_('data.quickbooks.lookup_customer1.CurrencyRef.value')="CAD":"GST") but how do I specify what I want the field to be if the if is false?
Hi Peter,
The best way to do this is not to use IF conditions, but rather using a lookup table. By matching the Department, value to a certain value, you can say ID field should take on this specific value.
Here's a brief example :
What this means is that Closed will take the value of false if the Status pill has the value of new, pending, or solved. If the Status field is closed, then Closed field will take the value of true.
You can do a similar one with the Department data pill in the ID field. You can read more about lookup table and more on our reference guide here.
1 person has this question
Adam Goh
Hi Ullaco!
Yes you can definitely do that. Here's an example of it:
? follows with the value if it is true while : follows with the value if it is false.Peter Kohler
I need something a little more complex, more like an If Else If statement.
I have an input field on my trigger for 'Department' which I then need translated to an ID to send on to Salesforce. How do I build a formula to waterfall down the list of 7-8 items?
if (Department <> null) {
if (Department == "A") {
ID = "123" }
else if (Department == 'B') {
ID = "456"}
else if (Department == 'C') {
ID = "789"}
and so on......
)
});
1 person likes this
Adam Goh
Hi Peter,
The best way to do this is not to use IF conditions, but rather using a lookup table. By matching the Department, value to a certain value, you can say ID field should take on this specific value.
Here's a brief example :
What this means is that Closed will take the value of false if the Status pill has the value of new, pending, or solved. If the Status field is closed, then Closed field will take the value of true.
You can do a similar one with the Department data pill in the ID field. You can read more about lookup table and more on our reference guide here.
Saul Macht
Hi,
Thanks for the Look Up table formula! However, I have a more complex use case. How can I use two conditions in the formula? For example, If I want differing inputs based on different combinations of Status and Account?
E.g:
If Status is A and Account is D, then Value X
If Status is B and Account is E, then Value is Y
And so on and so forth.
Patricia Guevarra
Hi Saul,
For this, it would be better to use our Look up Table Feature instead of using a formula. You can see more information about this feature here: https://support.workato.com/solution/articles/1000244426-all-you-need-to-know-about-lookup-tables
Mark
I need a much more complex CASE or IF Else statement.
Basically I search for a record, If the record doesn't and some other condition is true then I search for it using another method, but if that condition is not true I try yet another method, if neither of those give me the record I then search using another method but only if another condition is true, etc.
The number of nested and repeated IF statements and searches I am having to in causing a lot of confusion, and then I have to figure out which one succeeded and to use in another step.
I could do this pretty easily with a nested IF Else statement, although even then it's complex.
Mark
A lookup table is no use as I am dynamically looking up things.
Patricia Guevarra
Hi Mark,
Just to clarify, when you say you would like to search using a different method, do you mean that you want to search using a different parameter based on certain conditions? As a simple example, you want to search based on Name if condition A is true, or Email if condition B is true?
Mark
Patricia Guevarra
In the case that the pill is empty or is coming from a step that wasn't executed, you don't need a formula. For example, if you fill in both Contact Name and Contact email, and the pill mapped into Contact Name is empty, then it will only search by the Contact Email you provided. In the case where nothing is returned, there is no formula for this and it would be necessary to use conditional actions.