Conditional Updates to Master Reference

1

Which approach would you suggest for this issue to control for duplicate Sales Order Numbers?

I receive an Order file (Excel) several times a day. It will contain a number (5 - 25) orders plus details. As a flat file, the order number will be repeated for each product ordered on that order.

I want to process the order ONLY IF the order number DOES NOT exist in my MASTER ORDER FILE.

If I do process the Order, I then want to update a “master” file history table with the Order number (and some other data) to prevent the possibility of reprocessing the same number.

I’ve been reading through the Conditional Workflow and Derived Table information, but I’m not yet clear on how to structure this. Is the Sandbox required?

Thank you –

2

The Sandbox is not required. Here is an example that updates master table with new a ID only when it doesn’t exist in the master table:

Append not existing.zip (1.3 KB)

image
image.png801×693 18.8 KB

It works as follows:

  1. Load the master table with a list of IDs.
  2. Generate a random ID in the range from 1 to 10.
  3. Check if the new ID already exists in the master table. If it does NOT then append it to the master table and save it.
  4. If the new ID does exist then fail with message “ID already exists”

To use the example, unzip it into a folder, open in EasyMorph, then press “Reload and Run” in the Project menu (or Ctrl + F5) several times to run the project for multiple randomly generated IDs. Each time you will see that the master table is ether updated with a new record, or the project fails with message “ID already exists”.