Calem Integration 5: Upsert REST API

Upsert REST API will be available in the coming release of Calem R2024a. It is more intuitive than current data update APIs. A user may use the API to add or update a record in Calem, similar to the data upload utility (see this blog).

A record data is provided to add to database.
Calem will perform a database insertion first.
If there are no duplicate keys exist the record is inserted into database.
If duplicate keys are found, the existing record is updated by the record data provided.

1. Fields

Fields are identified by field names, labels, or other means. Data dictionary (Admin | Report | Data Dictionary) is the source of field names and labels.

Calem uses the formats of the field names to determine database values. Here is the summary of the rules.

A lookup field may be a dropdown such as work order status with values such as "wos_new", "wos_completed".
A lookup field of a table references a record of another table. It is the Id of another record.
An Id in Calem is a string of 36 hex characters such as "213c5375-6aaa-a17f-92ed-a7e713fcecc4".

Data base field names. It is the "Field" in data dictionary. For instance, Item table (cm_in) has the following field names: "in_no" – item number "vendor_site_id" – vendor site Id	Raw values must be provided for database field names. For example, "in_no" of "cm_in" (Item table) is the item number, the value is the item number such as "01-01-001". "vendor_site_id" of "cm_in" (Item table) is a lookup field. Lookup fields have a suffix of "_id". The value is the Id of a vendor site such as "213c5375-6aaa-a17f-92ed-a7e713fcecc4".
Field labels. It is the "label" of a field. For instance, For instance, Item table (cm_in) has the following field labels: "Item #" for "in_no" "Vendor site" for "vendor_site_id"	The field names and values must be same as an export of a report in Calem. • The field names are field labels. • The values for non-lookup fields are database values; • For dropdown lookups, the values can the English labels, or database values. For instance, the database value "wos_new" (work order status) is transformed to "New" (the English label) in an excel export. "wos_new" is also acceptable. • For table lookups, the value must be the primary key corresponding to the lookup Id. For instance, the value for "Vendor site" (the label for "vendor_site_id" field) must be "Acme" (the vendor site (primary key) for record Id "213c5375-6aaa-a17f-92ed-a7e713fcecc4".
A join field name. It is "lookup field name"+"_". For instance, "vendor_site_id_" is the equivalent of "Vendor site".	For lookup fields – handled just as field labels.
Datetime field type based on data dictionary. For instance, inventory transaction datetime.	The value needs to be your local time. The format may be one of the two below. • 19-character format of "yyyy-mm-dd M:i:s" such as "2024-04-20 14:30:00". • Local date time format such as "4/20/24 2:30 pm" for US locale.
Date field type based on data dictionary. For instance, asset purchase date.	The value needs to be your local time. The format may be one of the two below. • 10-character format of "yyyy-mm-dd" such as "2024-04-20". • Local date format such as "4/20/24" for US locale.

2. Data Payload

The "POST" operation is used for upsert: An insert is attempted. If a key violation is incurred from insertion, an update is attempted.

Content type is JSON: application/json
"Id" field must be included for a record when key fields are to be updated.
The data in the POST body is JSON object of the following format. It includes "table" (for table to update) and "data" (with fields).

If a record is inserted the record Id is returned to the client. The object Id may be sent to Calem for data update later.

3. Service Address

The service address uses virtual table "cm_vt_ld_so" with your Calem service. For instance,

https://acme.calemeam.com/api/insert/cm_vt_id_so

Additional Resources