Import If/Then Code To Search and Change Account Number

Avatar
Baltazar Rocha

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!

0

Comments

6 comments
Date Votes
  • Avatar
    David Gollom
    Strategic Partner
    Top Contributor
    Celigo University Level 4: Legendary
    Answer Pro
    Great Answer
    Engaged

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

    0
  • Avatar
    Tyler Lamparter
    Principal Product Manager
    Awesome Follow-up
    Engaged
    Top Contributor
    Answer Pro
    Celigo University Level 4: Legendary

    Baltazar Rocha 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}}
    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://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
  • Avatar
    Baltazar Rocha

    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
  • Avatar
    David Gollom
    Strategic Partner
    Top Contributor
    Celigo University Level 4: Legendary
    Answer Pro
    Great Answer
    Engaged

    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
  • Avatar
    Baltazar Rocha

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

    0
  • Avatar
    Kathyana Queeman
    Technical Writer
    Celigo University Level 4: Legendary
    Engaged

    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.

 

Didn't find what you were looking for?

New post