Best practice for fixing key field problems?

I am fixing a solution for a client that they had originally built off of the Invoices starter solution. The problem is the InvoiceID in that solution. When they figure out after they created an invoice and add line items that the invoice number is wrong and change it in the invoice layout it breaks the relationship to InvoiceData table and all the line items disappear. They end up with a bunch of orphaned records when they simply enter everything again and if the incorrect entry ever gets used in the future the problem is worse. Why FM decided to allow the direct use and edit of a primary key field like this in a starter solution is confusing, but they do as they wish I guess.

So I need to add a UUID key that users cannot interface with and not break anything. I came up with some ideas.

Change the current InvoiceID field to an auto enter Get(UUID) that would be used for all new records. Change the layout field from InvoiceID to a new field named InvoiceNumber. Does not break anything and new records get UUIDs while old records hold the Invoice numbers as the Key. Not consistent but a working solution.

Create a new field named InvoiceNumber. Create a layout for the UUID migration with InvoiceID, InvoiceNumber and a portal to InvoiceData that has the InvoiceIDMatchField in it. Then have a script loop through every record setting the InvoiceNumber to InvoiceID and then running through the portal rows setting the MatchField to a variable Get(UUID) for the related records, then setting InvoiceID to the same UUID variable. If the key field changes in Invoices first the related records fall out of the portal all at once. If I change the Match field first they drop off one at a time and then changing InvoicesID at the end fixes the relationship again. Seems like the better way to fix this.

2. Populate this field for all existing records using Replace Field Contents, and then set it to auto-populate from then on.

3. Create a new FK field in the Line Items table to take the new UUID key (see below)

4. Use the pre-existing relationship to pass the new UUID across to matching Line Item records. That will work for all records that have not had the InvoiceID interfered with.

5. Find all Line Item records that have no UUID in the new FK field. Fix these up, probably manually if there are not too many to deal with, as figuring out a way to automate the fix-up could take just as long.

6. Once all Line Item FK's are correctly set, repoint the pre-existing relationships to the new UUID fields. The original InvoiceID fields are now redundant as match fields.

Another reason I think your #1 is a good idea is that it leaves the structure the same and doesn't rely on any assumptions that would be required to run #2 scenario. For #1 a DDR can be used to ensure that you change all layout occurrences and it's a complete solution.

But not having seen the solution then I'm also making assumptions so... it's just a suggestion.

Do not give them any meaning (like telephone number, invoice number ...)

Actually, consider not showing them to avoid misunderstandings.

Any field with a meaning will inevitably need a change one day ... and then hell arises.

Any meaning used in a key will probably change over the years ... and then hell arises again.

Consider using one of these two types of keys, or both:

A serial number in a number field starting with one, unmodifiable and alway increasing with one.

UUID in a text field, being set by calculation on every new record.

The UUID has a very important advantage when used with solutions that are not run from a server (mobile Go solutions with the file on your unit, Pro locally on your PC etc.): They will not create duplicate keys.

The current InvoiceID only appears in 5 places on different layouts. Not that bad. That is kind of what I was thinking would be easiest, but there are still all the old invoices that might get edited by accident. I might have to control the data entry somehow so that if there is a current value there cannot any editing, but still allows for entering new Invoice numbers.

Consider not using keys as invoice numbers at all. Clean up, transfer the number to a new field for invoice number ad delete all key fields from the layouts. You will, as everybody else, regret any compromise here.

I know how this should be done. If it just a matter of the best way to actually fix it. That is what I am looking for. I very well know that key fields should not be changed ever and should never be used for any human use. I am a big fan of UUIDs.

What was FM thinking when they started using this poor practice in the starter solution files?

Consider not using keys as invoice numbers at all. Clean up, transfer the number to a new field for invoice number ad delete all key fields from the layouts. You will, as everybody else, regret any compromise here.

I assume that you want a sequential number, moving forward one by one, but that for some reason you sometime will have a record that is not in use .... or?

If not, I can not se how an invoice number could end up being wrong?

Have a settings field centrally placed in your solution called "Invoice_number_next" or whatever. Have a script getting this and adding one to it whenever you want to set an invoice number. This way you do not need to go into the schema to change the next number.

2. Populate this field for all existing records using Replace Field Contents, and then set it to auto-populate from then on.

3. Create a new FK field in the Line Items table to take the new UUID key (see below)

4. Use the pre-existing relationship to pass the new UUID across to matching Line Item records. That will work for all records that have not had the InvoiceID interfered with.

5. Find all Line Item records that have no UUID in the new FK field. Fix these up, probably manually if there are not too many to deal with, as figuring out a way to automate the fix-up could take just as long.

6. Once all Line Item FK's are correctly set, repoint the pre-existing relationships to the new UUID fields. The original InvoiceID fields are now redundant as match fields.

Sounds logical, and here at Codeo Denmark and Codeo Norway we are discussing UUID or UIID + Serial number.

The problem with UUID is that it is completely unreadable, at least if you are not a very special person

The problem with Serial numbers is that they are dangerous if records going into the same entity are created by offline incidents of your solution.

Therefore my personal opinion: If it is a server based 100% online solution I would use serial numbers as keys. And UUID if there are offline elements.

But the decision at Codeo Denmark & Norway is to use UUID for all keys. We have then added an extra key, the serial number, which we are not using for relationships. We are also adding a lot of zz fields for household, some of them establishing where a record is created so that we are not confused if more than one end up having the same serial number.

2. Populate this field for all existing records using Replace Field Contents, and then set it to auto-populate from then on.

3. Create a new FK field in the Line Items table to take the new UUID key (see below)

4. Use the pre-existing relationship to pass the new UUID across to matching Line Item records. That will work for all records that have not had the InvoiceID interfered with.

5. Find all Line Item records that have no UUID in the new FK field. Fix these up, probably manually if there are not too many to deal with, as figuring out a way to automate the fix-up could take just as long.

6. Once all Line Item FK's are correctly set, repoint the pre-existing relationships to the new UUID fields. The original InvoiceID fields are now redundant as match fields.

It seems like I pretty much had the right idea already and there is no real better or easier way to get it done. They at least have the current InvoiceID set to have a unique value so that helps a little. As you might guess this is not the only issue, but it seems to cause the most problems.

FileMaker should really set a good example with better design and not let people get sucked into these kinds of things in their starter solutions.