Identity Columns

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. Lockstep 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 Lockstep and Assigned by Financial System. Here's how to use them:

Type of IdentityData TypeUsage
Assigned by LockstepOne 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 Lockstep platform.
Assigned by a Financial SystemTwo fields: ErpKey and AppEnrollmentId

ErpKey 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 Lockstep 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 Lockstep Platform account. She connects her Lockstep Platform account to the online accounting system Xero. Within Lockstep, 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 Lockstep 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 Lockstep saves this invoice to the Lockstep platform 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 Lockstep.

Unique ID and ErpKey

Lockstep 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 Lockstep 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.

845

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.