Solved

After saving always select correct record with incremental id that is not an identity

  • 19 April 2023
  • 7 replies
  • 151 views
R
Rank
Userlevel 4
Badge +13

Let's say we have an order table containing orders numbered 1, 2, 3 etc. This order number is in the column ‘id’ which is the primary key of the table. These numbers are always integers but they are not an identity. When adding an order to the table a bit of logic needs to determine the next available number. In principle this can be done with max(id) + 1. This works fine in the Windows GUI by putting this logic in a default procedure. It stops working however when an order with the same id is added by someone or something else simultaneously.

Say the highest order id is 10. Then I start creating a new order in the GUI which by the aforementioned logic gets order id 11.In the mean time another process inserts order 11 into the table. Then when I click the save button the GUI tries to save the new order with id 11 which is not available anymore and an error message follows.

I've tried to get around this by using an insert handler with the same max(id) + 1 logic. This eliminates the error message but after clicking the save button the GUI assumes the saved order is id 11 instead of 12 and thus selects the wrong record after saving.

The insert handler doesn't seem to tell the GUI the final id is 12 even though it specifically sets the id to 12 in its logic. The default procedure doesn't fire when clicking the save button so it can't set the correct id either.

How can I have the Windows GUI pick up on the correct final order id in such a way that it always selects the correct document id after saving? I know this can be fixed with an identity but if a data model change can be avoided that would have my preference for now.

icon

Best answer by Anne Buit 7 June 2023, 17:11

View original
This topic has been closed for comments

7 replies

Renée Evertzen
Rank
Userlevel 4
Badge +3

Hello Roland,

Theoretically there are a few things you can try:

  • You could write the default in such a way that it recalculates the order number every time you change a field and jump to the next field. If another order has been saved in the meantime, it will recalculate the order number of the current record you're attempting to add, and should up it by 1. As long as there are no other difficult calculations happening, this shouldn't be too impactful on the performance of your screen/application.
  • You could determine your order number in the trigger instead of in the default. The drawback of this solution is that you won't be able to always show the correct order number in the grid/form of the record you're attempting to add.
  • You could maybe create a combination of the two, use the default to calculate it on the fly, since as long as no one or nothing else adds an order, there is no issue. But use the trigger as a fallback by having it determine the definitive order number upon actually saving the record.

In your situation where the order number is a simple integer I would still advise using an identity (just my professional opinion), but there are also situations where the identifying value is not a simple integer and your situation would apply.

Hopefully my suggestions help, let me know if it works out 😃

Kind regards,

Renée Evertzen

eurban
Rank
Userlevel 2
Badge +4

Hello Roland

have you tried sequence, works fine for me

 

Edit by moderator:

More Information on that here:

R
Rank
Userlevel 4
Badge +13

I decided to make the current document number an identity in this table which works fine. There are other tables though where this isn't an option. For example we have invoices with both a journal id and a document id and every new year the document number starts at 1 for every journal. This means there isn't a unique number in a single column. What I’ve tried so far is add an ‘id’ column to the invoice table, make this new column the sole primary key and put all the old primary key columns in a unique index. However the software factory doesn't permit a reference with integrity check from the unique indexed columns to the table with the products on the invoice.

The existing situation:

invoice    invoice_product
-------    ---------------
*year      *year
*journal   *journal
*document  *document
           *product

Proposed change:

invoice    invoice_product
-------    ---------------
*id
[year]     *year
[journal]  *journal
[document] *document
           *product

Primary key columns are prefixed with an asterisk and the columns in brackets are part of a unique index.

I think SQL Server permits key constraints from a unique index to the primary key of a target table. However the software factory doesn't and insists on using the new 'id’ column of the source table.

I can add the same 'id’ column to invoice_product as well but that requires a bigger change in the datamodel which I would like to prevent.

@Renée Evertzen
The issue I think with a default procedure is that it only fires after a field value change. It doesn't fire when saving the record and thus it's never guaranteed that the current number in the form is still available in the table. Even if it did fire upon a save action it's still no guarantee as firing the procedure and inserting the row into the table isn't done atomically. You mention a trigger and perhaps an instead of trigger could be the answer here. I wonder though if that doesn't have the same effect as a handler on the save button where the handler uses the next available document number. The issue with that is when another document is saved in between the gui can actually jump to that in-between document after saving, instead of the document that was actually saved.

@eurban
Thank you for mentioning sequences. I think those are mostly analog to identities except that they aren't stored in the table and can also be used over multiple tables.

R
Rank
Userlevel 4
Badge +13

A foreign key constraint doesn't have to be linked only to a primary key constraint in another table. Foreign keys can also be defined to reference the columns of a UNIQUE constraint in another table.

Source

Is there a method to utilize this in the software factory and deviate from the default of using the primary key?

Mark Jongeling
Rank
Userlevel 7
Badge +21

A foreign key constraint doesn't have to be linked only to a primary key constraint in another table. Foreign keys can also be defined to reference the columns of a UNIQUE constraint in another table.

Source

Is there a method to utilize this in the software factory and deviate from the default of using the primary key?

Hi Roland,

You can freely create references in the Software Factory between tables, including any columns. The situation you mention should work for SQL Server.

By enabling Check integrity, the reference (foreign key) will be rolled out onto the database and enforced. 

 

"Wake me up before you low-code"
R
Rank
Userlevel 4
Badge +13

Hi Mark,

The problem is that when I enable check integrity the task form locks the source columns to the primary key columns of the source table. I don't want that. The source table contains a set of columns which are part of a unique index but that are not the primary key. I would like to use the unique index as the source for the integrity checked reference.

Anne Buit
Rank
Userlevel 7
Badge +5

Hi Roland,

This is currently not possible. The Software Factory does not allow for a different key to be used for a reference with integrity.

Tracing the created record with a logic-created key using a surrogate key known by the UI would probably be preferable over a formal surrogate key via a unique constraint. There are some restrictions imposed on these constraints (for instance, you cannot delete and re-create the unique index while there are foreign key references depending on it).

You can create your own record tracing by adding a column filled with newid(). A process flow can capture this value and use it to find the record and navigate to it.

As you mentioned, creating the primary key via trigger- or insert handler logic will result in the problem where the UI will not be able to trace the created record. The UI (and the insert handler) only has this capability for identities.

Alternatively, you can create a view of this table that marks this column as an identity (even though it isn't). When you use an insert handler for this view, you will be able to manually assign the value of the ‘identity’ column within the insert handler logic to guide the UI to find the created record.

Feel free to create an idea to allow insert handler logic to manually determine the created records’ key values for non-identity columns as well. This would be the most elegant solution in my mind.

Terms & ConditionsCookie settings