Import If/Then Code To Search and Change Account Number

Integrator.io
, ,
1

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!

2

This sounds like a Static Lookup mapping to me. Have you tried that?? There's training on it in the university.

3

@baltazarrocha there are several ways you could do this.

  1. 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}}

    5301043caa20740200000001-3f682a858e0b45548161acc32f5d939e.png
    5301043caa20740200000001-3f682a858e0b45548161acc32f5d939e.png2281×821 46.4 KB

  2. 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://connective.celigo.com/t/querying-netsuite-records-in-custom-form-builder-settings/566
    https://docs.celigo.com/hc/en-us/articles/360059205112-Common-form-fields#type_staticMap
4

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!

5

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.

6

Thanks David. Getting into trouble is a bit of a stretch, but ill look into the training. :)

7

Hi @baltazarrocha,

You can enable Developer mode and use the developer tools with administrator access. Let us know if you have any further questions!