Calem Blogs

Blogs of CalemEAM
Font size: + –
Subscribe to this blog post Unsubscribe
Print

Calem Integration 5: Upsert REST API

Feature
4156 Hits

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 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".
A join field name. It is "lookup field name"+"_". 

For instance, "vendor_site_id_" is the equivalent of field label "Vendor site".


 For lookup fields it is cleaner and intuitive to use this format. Alternatively, one can use direct Id field (with Id values), or labels below.
Field labels. It is the "label" of a field in data dictionary. 

Calem recommends the use of field names in interface specification since field labels are subject to changes in future releases.

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".
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). 
  •  The data is encoded with field and lookup names:
{
	"table": "cm_in_site",
	"data": {
		"site_id_": "Site-001",
		"in_id_": "IN-001",
		"description": "In-001-Desc",
		"status_id": "ins_active",
		"note": "In-001-Spec",
		"uom_id_": "U001",
		"order_type_id": "in_order_minmax",
		"min_level": "5",
		"max_level": "10"
	}
}
  • The data is encoded with field labels for lookup names:
{
	"table": "cm_in_site",
	"data": {
		"Site": "Site-001",
		"Item": "IN-001",
		"Description": "In-001-Desc",
		"Status": "ins_active",
		"Specification": "In-001-Spec",
		"Stock Unit": "U001",
		"Order Type": "in_order_minmax",
		"Min": "5",
		"Max": "10"
	}
}
  • 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.
{
    "status": 0,
    "data": {
        "id": "013c5375-6aaa-a17f-92ed-a7e713fcecc4",
        "asset_no": "A084346",
        "note": "Asset Note",
        "etc": "etc"
    }
}

 3. Service Address

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

 Additional Resources

Tweet
0
Tags:
Integration
Scrollbars and Mouse Wheeling
Simplifying Inventory Transfer and Disposal

About the author

Clay Li

Clay Li

Customer Success Team
Author's recent posts
More posts from author

By accepting you will be accessing a service provided by a third-party external to https://calemeam.com/