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.
Is there an even better way to do this that I am not seeing?