Identity Columns
Less than to read
Most financial systems assign unique identity codes for each record in a consistent manner. Some systems use identity code strings, others use numbers, guids, or dates. Sage Network API standardizes identity codes for all records within a group because some companies use more than one financial system.
Let’s explain how identity columns work.
Who assigns the code?
Identity codes fall into two categories: Assigned by Sage Network and Assigned by Financial System. Here’s how to use them:
| Type of Identity | Data Type | Usage |
|---|---|---|
| Assigned by Sage Network | One field, whose name ends with “ID”. For example, the primary key for the Company table is CompanyId.Always a GUID. |
Uniquely identify a record within the Sage Network. |
| Assigned by a Financial System | Two fields: ErpKey and AppEnrollmentIdErpKey is a primary key assigned by an enterprise resource planning system. The format of the ErpKey field depends on the financial system; can be strings, integers, guids, dates, or a combination.The AppEnrollmentId is a GUID assigned by Sage Network that is unique to each connector installed by the customer. |
Match a record with its representation in the originating financial system. Only unique when combined with the AppEnrollmentId of the originating financial system. |
Real World Example
Alice Smith, an employee for ABC Plastics, sets up a Sage Network account. She connects her Sage Network account to the online accounting system Xero. Within Sage Network, Xero is known as an “Application”, and her connection to Xero is called an “App Enrollment”. The app enrollment is assigned a GUID, 1ae22575-79b1-4bbb-84db-aee7ad7fce89.
When Sage Network loads invoice #2021-12-03-001 from Alice’s copy of Xero, the value 2021-12-03-001 is called the ErpKey. That is the unique ID of the invoice as it is known to Xero. Other ERP systems may also have invoices with this same ErpKey, but they will be on a different app enrollment.
When Sage Network saves this invoice to the Sage Network data system, we assign it an InvoiceID value which is a GUID that is unique to this invoice. This new GUID, 9b564d52-b0f9-4b29-b41d-dfde207e2a71, permanently identifies this invoice within Sage Network.
Unique ID and ErpKey
Sage Network refers to these records as “Unique ID” and ErpKey. For example, the Unique ID of the CompanyModel record is called CompanyId. The original code that identified this company within the source financial system is called ErpKey.
The term ErpKey was chosen because it was brief, it represented an “Enterprise Resource Planning” system, and because it slightly silly when you say it. Never let it be said that engineers don’t have a sense of humor.
When you are working with a record, you should always identify it by its Sage Network Unique ID value. This is because some customers use multiple financial systems, and when a customer uses more than one financial system, they can have conflicting ErpKeys. For example, the financial system Xero numbers all records sequentially starting with 1; this means that it is extremely likely that multiple financial systems will have objects with the same identity code.
How can I make ErpKey unique?
Each record that was imported from a financial system has both an AppEnrollmentId, which represents the connector for the financial system that originated the data, and an ErpKey which represents its primary key in that system.
If you query for records whose ErpKey matches a specific value, you may get duplicates. You will never see duplicates if you query by AppEnrollmentId and ErpKey.