Typical Order confirmation implementation

To start with, the schema would look like this (see attached):


  • Order_ID would be the unique identifier for that order (the order number from the client’s system). This would be the primary key for the table, guaranteeing that a single order can only exist once.
  • The table would be joined to the Contacts table through the idContact field if possible. If NOT possible, then this would need to be connected using the Contacts table’s primary key (i.e. Email, Client_ID, etc. – in my exemple above this would be f_NoMembre - a membership number from a demo project).
  • This table would then contain everything that is global to the order:
    • Subtotals, shipping cost, shipping method info, taxes, order total
    • Order status info (something that would let the message indicate the current status of that order, i.e. “invoiced”, “shipped”, stuff like that) – I used an INTEGER here because if the client has a status code in his database it’s probably numerical, but this could be a text value also

Order Items

  • The primary key here is a combination of Order_ID and Item_ID, which guarantees we can’t put the same item twice in the same order (that seems logical anyway).
  • Item_ID is the unique database identifier for the specific item. This should match the client’s products database, so it could be text data if they use SKUs instead of internal identifiers, etc.
  • The table would then contain everything related to each product in the order:
    • Name, Description, Image URL if you want one in the message
    • Pricing and Quantity info (so you could say “ordered 10 of these at 400 RUB each for a subtotal of 4000 RUB) – note that we didn’t add a “subtotal” column as the script can multiply the numbers, but you could add one if you wanted it.

 In itself, it’s pretty simple and straightforward as a database schema.

 We now have 2 things to consider:

  • How we feed data in the tables
  • How we send the message



The message would use a query that matches the schema above, and would inclure a filtering parameter on the Orders table, on the Order_ID field.


OPTION 1 – The “100% Generic way with standard APIs”

The plan would go like this:

 Creating the data

  • Set the Contact properly:
    • Call Contact API to check if contact exists or not, then
      API to use: Contacts.Create(), which returns an error if the contact already exists, or Contacts.GetContactInfo() 
    • Call Contact API to create or update contact as needed
      API to use: Contacts.Create() or  Contacts.Update()
    • Whichever path was taken, save the “idContact” that you obtained
    • Insert the Order:
      • Call CustomTables API to insert record in Orders table, using the “idContact” value retrieved from the previous step
        API to use: CustomTables.Create() 
      • Insert Order Items:
        • Call CustomTables API to insert the records in OrderItems table
          API to use: CustomTables.Create()

Sending the message

  • Use the new SendSingle2() API to send the message to the proper idContact, including the Order_ID parameter to filter the query properly (i.e. to make sure the message includes the right order record)

Overall, this method would require 5 API calls (~2 to create or update the contact, one to insert the Order, one to insert all Items, one to send the message).

 It’s simple enough to do, but it becomes a hassle when you want to deal with errors and exceptions (what happens if you get errors halfway through, etc.)


OPTION 2 – The Custom API

A single API to which you would send everything (Contact, Order, Items, Message ID).

The client would make one call, and all the “business logic” described in the first option would be designed in the service itself.


Please sign in to leave a comment.