Changing the match field in a relationship (without breaking anything)

[Angus McKinnon]

I’m using some of the current downtime to tidy up some of the murkier corners of my Filemaker solution. One of the problems in my crosshairs is a badly-chosen match field that I want to replace. 

 

The relationship in question is between a catalogue table (a list of all the stock items that we have available) and an Order_items table, which is the line items on customer invoices. 

 

Foolishly, I chose to use a website URL as the match field for the relationship between the two. At the time, all of the items in the catalogue had a matching page on a website, and it was by definition unique. Seemed like a good shortcut at the time, but of course we’ve since needed to add some items to the catalogue which don’t have a website page. Worse, some website URLs have been updated, and we can’t change the URL field in the catalogue table without breaking all of the existing relationships. 

 

I’m planning on changing over to a simple serial number to use for each catalogue item, independent of what’s going on in the URL field. I’m just wanting to double check that I’m not missing anything with the procedure to make this happen. Here’s my plan:

 

1) Create the new Serial field in the catalogue table, and populate it in the existing records with serial numbers (and make sure the auto-entry is set correctly for future additions)

 

2) In the Order_items table, create a matching Serial field, and insert a calculated result into each record, pulling the number through from the catalogue record using the existing relationship

 

3) Change the relationship to be based on the Serial fields rather than the URL

 

 

I’m reasonably certain that this will work properly, but I’m keen to avoid any potential pitfalls. Or is there a better way of doing this?

[comment]

13 minutes ago, Angus McKinnon said:

1) Create the new Serial field in the catalogue table, and populate it in the existing records with serial numbers (and make sure the auto-entry is set correctly for future additions)

 

2) In the Order_items table, create a matching Serial field, and insert a calculated result into each record, pulling the number through from the catalogue record using the existing relationship

 

3) Change the relationship to be based on the Serial fields rather than the URL

Yes, that is the correct procedure. You can use Replace Field Contents on both fields, replacing one with serial numbers (this will also set the next serial number) and the other with calculated result. Make sure to show all records and unsort them beforehand, and that no one can lock any of the target records. And have a backup in case you make a mistake.

--
P.S. Please use the default font when posting.

 

[Angus McKinnon]

That's great - thanks. Good to know I'm not barking up the wrong tree, and I'll definitely have a backup before kicking off the process. 

Apologies for the font - I'd written up the post in Word whilst offline, then copied and pasted over. Slightly ironic since one of my other housekeeping tasks at the moment is adding TextFormatRemove(self) to some of my fields to stop my users from causing similar problems...

[Angus McKinnon]

Just returning to this thread with an update. 

It all seems to have gone swimmingly. There were actually two relationships that needed "fixed" in this way, and everything seems to be working as it should on the new serialised match field. 

Thanks again to comment for his input. Using "Replace Field Contents" saved me some scripting which would have had no long-term benefits.