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

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

Hello Roland

have you tried sequence, works fine for me

Edit by moderator:

More Information on that here:

• https://docs.thinkwisesoftware.com/docs/sf/sequences (Documentation)
• https://learn.microsoft.com/en-us/sql/t-sql/statements/create-sequence-transact-sql (Background info)

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.

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. 

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.