Platform App - Lookup Application

[Sterio, Louis]

Submitter: Tierney Santoro, The Menta Education Group

Business Need: : As a multi-location, single entity, we needed a way to record a customer’s purchase order number for each of our own locations based on the dimensions ‘Location’ and ‘Class’. The solution we arrived at was to create an intermediary lookup table that housed custom relationships between the customer, location, and class dimensions. The lookup table was then used to populate the PO number on order entry transactions using an API trigger call. While we are using the lookup tables for this specific use case, this same logic could be implemented for any number of use cases that require multi-criteria lookups.

Pre-Requisite: Ensure that Platform Services are enabled for your account.

Tool Limitations:

-

FAQ's:

-

Objects and Fields:

• Object: Order Numbers (Lookup Table)
• Relationships:
  •  Many-to-One Customer
  • Many-to-One Class
  • Many-to-One Location
  • One-to-Many Order Entry Transaction
• Fields:
  • Purchase Order Number (Returned Value, Text)
  • Concatenated ID (Record Name):
    • Notes: We use a concatenation of “Location ID” + “Class Name” + “Customer ID.” For example: “LOCATION1-TUITION-CUSTOMER1.”
    • This allows us to utilize information from the order entry transaction to find the appropriate record. This could be pre-populated using an ‘Update Field’trigger after the record is created. • Related Relationship Fields which are auto-created

Usage Steps:

1. If you do not have an application you will be adding this to, create a custom application.

2. Create the lookup object with associated relationships and fields as described above.

3. Add the object to the associate application menu so that users can enter new lookup values.

4. Update permissions to access the lookup object.

5. Open the object definition for the Order Entry Transaction.

6. Create two triggers: one to attach the related lookup record, and the second to update the returned value field. They should be listed in this order so the execute in correct sequence.

• Attach the related lookup record:
  • Trigger Type: Attach related record
  • Trigger: After Create
  • Relationship: Lookup Table
  • Relationship Formula: this returns the same value that was used for the ‘Record Name’ for the lookup table. (See explanation above.)
  • return "{!SODOCUMENT.INV_LOCATION!}- {!SODOCUMENT.TUITION_TYPE!}-{!SODOCUMENT.CUSTVENDID!}"
•  Return lookup value:
  • Trigger Type: Intacct API
  • Trigger: After Create
  • Document Template: (Create new, see step 6 below.)
  • Trigger condition formula: {!R_Menta_PO_SES_Tuition.id!} > 0;
    • (This is the name of our relationship field on our Order Entry Transaction. Note, if no relationship exists Intacct returns 0.)

• Create a platform document template with the following API call:
  • Template Type: Generic
• Code: {!R_Menta_PO_SES_Tuition.menta_po_number!}

<update_sotransaction key="{!SODOCUMENT.DOCID!}">
<customerponumber>{!R_Menta_PO_SES_Tuition.menta_po_number!}
 </customerponumber>
</update_sotransaction>

• (Updates the current order entry transactions purchase order field with the related lookup’s purchase order number.)

 

Lookup Application.pdf Lookup Application.docx