Hi all, I’m trying to set a list/record value on a transaction on before record submit using sql. essentially what i’m trying to do is if the transaction currency matches customfield1 on the list/record’s custom fields then use that records internal id to set the transaction field. I’ve read about sql and the select from where and it seems perfect, but i can’t get it to work and i don’t even know if the sql select is supported with workflows. if there is any other way to go about this then i’m open to suggestions. thank you! Slack Conversation

Shouldn't you just compare the the currency with your customfield1 and set the value as needed. Still don't understand where are you using the SQL?

I’m using SQL in the workflow “set field value”. There are 2 different custom records i am trying to pull from using a CASE WHEN statement. I want to be able to say “if transaction currency matches customfield1 from customrecord1, then get customrecord1 internal id and set the field on the invoice. if transaction currency doesn’t match any customfield1 from any customrecord1, then use defaultcustomfield1 from customrecord2 End”. There is another AND statement that I will need to put in where customfield2 from customrecord1 ALSO needs to match a custom field from the transaction or it should use the default field, but I am just trying to get the first part to work right now of one IF statement. thank you for your response

I have been able to use Case When statements in workflows so this should work. When you are setting the custom field, are you referencing the internal id of the field and the internal id of the value? It may be good to post your formula so we can see it

Hi marcus, It was giving me issues even tho i would be referencing what i thought were the correct values. Suiteflow sometimes confuses me with what variable/field i am actually using so it may have been the issue. I decided to use SuiteScript for this instead since i can tell which values i’m using better. I was able to get the script to work using the Search APIs. thank you for being willing to help!

Better scripted and Suite-flawed 😄