Sunday, April 21, 2019

Use Workflow With Formula to Extract the Entity ID From the Payee Field on a Vendor Payment Form

A workflow with a formula can be used to extract Entity ID from the Payee field on a Vendor Payment form.
It means to take the Payee field and trim off everything to the right of the Entity ID.

For example: 'A14 Clowns & Jokers Ltd.' would become 'A14'

    Intent: Take the payee ID and place it into the Memo field by workflow.
  1. go to Setup > Customization > Workflows > New
  2. Record Type: Transaction, Sub Types: Vendor Payment
  3. check On Create and On Update
  4. click Save
  5. create new state
  6. create workflow action Set Field Value
  7. set trigger on Before Record Submit or Entry
  8. in section Parameters select Memo in Field
  9. in section Value select Formula and enter "SUBSTR({entity}, 1, (INSTR({entity}, ' ',1)-1))"
  10. click Save Now after each Vendor Payment save changes the value in Memo field to payee's ID.

Here is an explanation of the formula used above.

INSTR({entity}, ' ',1) – returns the position of the first space (second parameter – ' ') inside the {entity} field (e.g. 4)
– searching begins from first character (third parameter - number 1) 

SUBSTR({entity}, 1, 2) – it takes a substring containing 2 characters (third parameter – number 2) from {entity} field,
substring begins from first character (second parameter – number 1)

The formula is recommended to get substring from {entity} a starting from first character to the first space.
There should be SUBSTR({entity}, 1, 2) and INSTR({entity}, ' ',1) instead of number 2 because this parameter could vary.
Substring must be WITHOUT the ending space thus 1 character must be subtracted.

No comments:

Post a Comment