Select your user interface: Web Shell Smart Client

"Primary Record ID" Batch Form Field Metadata

One key difference between a batch that adds records and an upsert batch that adds and edits records is the additional metadata that is required to distinguish between a batch row that adds a row to the production table and a batch row that updates an existing row. Let's look at the FormMetaData tag within the data form that adds a row into the USR_BATCHFOODITEMUPSERT table. Here we see a form field with a FieldID="PRIMARYRECORDID" attribute. This PRIMARYRECORDID form field contains a SearchList child tag that allows users to search for food items. After a food item is selected, the primary record ID value of the food item is tracked to the batch row within the PRIMARYRECORDID column/form field.

Figure: A form field is used to designate rows that are updated versus inserted

Now, there is nothing magical about the name of the FieldID value "PRIMARYRECORDID." It has no special meaning other than this is the FieldID that is referenced within the EditLoadField attribute within the CommitRowEditDataFormTemplate tag within the BatchTypeSpec element. The PRIMARYRECORDID column for a batch row may contain the unique identifier of the USR_FOODITEM production table. When the PRIMARYRECORDID form field is populated within the batch staging table, it prompts the Infinity platform to update the row referenced by the PRIMARYRECORDID, rather than add, the row within the production table at commit.

Figure: The EditLoadField attribute

To illustrate the point, let's add some rows into a new batch that is based upon the Food Item Upsert Batch. Rows will be added to the USR_BATCHFOODITEMUPSERT batch staging table. After we save the batch, we will look within the USR_BATCHFOODITEMUPSERT table. First, we will add three rows to the batch.

Figure: Add and update rows within the same batch

The "Food Item" caption above describes the PRIMARYRECORDID column. After we save this batch, we run a query to investigate the contents of the USR_BATCHFOODITEMUPSERT batch staging table. Note the PRIMARYRECORDID column within the query results below. At commit, any row that has a value of NULL for the PRIMARYRECORDID is added to the production table while a row that does contain this value is updated.

Figure: PrimaryRecordID column contains a value for the Almonds row. This row will be updated at commit.

With the rows saved to our USR_BATCHFOODITEMUPSERT batch staging table, we will attempt to commit the rows to the database.

Figure: Commit the upsert batch

Let's review our food items within the production table, where we can see that two were added and one was updated … all from the same batch!

Figure: Two rows added, one row updated