Import If/Then Code To Search and Change Account Number
Hello,
We have a somewhat complicated scenario involving AR files from our Corporate office. In short, they send us AR files which dummy account numbers (we've asked repeatedly to change his process, but probably wont happen!). Then our accounting department has to manually change the dummy account numbers to the correct ones depending on if there is a specific PO number in the AR file.
Essentially what we are looking to do is whenever we receive an AR file, search the beginning of the "Purchase_Order_Number" field in the file, if it starts with 1 of 5 different identifiers, change the existing account number to a different one.
Mapping would be like this-
PO field starts with:
DEN --> change account number to 10276
KCY --> change account number to 10278
MIN --> change account number to 38675
SLC --> change account number to 10274
STL --> change account number to 10279
I presume this would take some custom code and we are light on that skillset, so any help is greatly appreciated!
THanks!
-
This sounds like a Static Lookup mapping to me. Have you tried that?? There's training on it in the university.
0 -
Baltazar Rocha there are several ways you could do this.
- Use handlebar expressions on the mapping. You would have to keep adding to this handlebar expression as new things come up. This entire expression could be come quite large, but it would work. Here is an expression that works with what you've specified:
{{#compare (regexSearch Purchase_Order_Number "^DEN") ">" "-1"}}10276{{else}}{{#compare (regexSearch Purchase_Order_Number "^KCY") ">" "-1"}}10278{{else}}{{#compare (regexSearch Purchase_Order_Number "^MIN") ">" "-1"}}38675{{else}}{{#compare (regexSearch Purchase_Order_Number "^SLC") ">" "-1"}}10274{{else}}{{#compare (regexSearch Purchase_Order_Number "^STL") ">" "-1"}}10279{{else}}{{Real_Account_Number}}{{/compare}}{{/compare}}{{/compare}}{{/compare}}{{/compare}}
- You could use scripts as well that utilize form settings. This would be a bit more complex to initially setup, but would be better in the long run. Here are a few articles on how it works.
https://docs.celigo.com/hc/en-us/community/posts/11906392792347-Querying-NetSuite-Records-in-Custom-Form-Builder-Settings
https://docs.celigo.com/hc/en-us/articles/360059205112-Common-form-fields#type_staticMap
0 - Use handlebar expressions on the mapping. You would have to keep adding to this handlebar expression as new things come up. This entire expression could be come quite large, but it would work. Here is an expression that works with what you've specified:
-
David,
I havent looked on the university for static lookup training, but will do!
Tyler,
I appreciate the detailed response. This might be a silly question, but where do i find the developer section to enter the handlebar expressions?
Thank you both. This is new territory for me!
0 -
Hi,
spend a few hours on the training and learn to use the tool. You're just going to get yourself in trouble with this approach trying to use advanced techniques.
0 -
Thanks David. Getting into trouble is a bit of a stretch, but ill look into the training. :)
0 -
Hi Baltazar Rocha,
You can enable Developer mode and use the developer tools with administrator access. Let us know if you have any further questions!
0
Please sign in to leave a comment.
Comments
6 comments