How can I create a new primary key in a FileMaker table and then modify an existing relationship to be based on the newly created key?

I have quite the request. A developer created our database that has multiple Layouts with a relationship tree that is very messy. Had another developer come in, take a look at it, he said that it would be too time consuming and painful to deconstruct our system, consider creating a script for now so that you can work around it until it can be fixed. Here's the dilemma: I've got information on one page that is supposed to reference information on a number of different pages utilizing tabs and portals. However, all the information is linked to a username. This username is not static/serialized or what have you. Therefore, if the issue is not caught right away a great deal of informati开发者_开发百科on can be linked to this person's 'page'. If the name is incorrect and someone tries to alter the username even slightly, it breaks that relationship and starts a new one. The information does not disappear, but it is basically sitting in limbo somewhere until you change the name back to the original. I've actually tried exporting the information, changing the information that doesn't match, i.e. changing a name from Jon.Smith to John.Smith, and then importing the information to a new 'page' for that person with unsuccessful results. Which brings me to my question, is a script going to be able to fix this problem? Likewise, are there any suggestions to how to create this script? I apologize, but I have very little experience with DataBase management at all, and am not sure why this project fell upon me. Any help would be GREATLY appreciated.

Well, as a general answer, just about anything that a user can do in FileMaker (and by user, I mean non-developer, so activities such as defining the database structure, writing scripts, etc., are excluded) can be scripted. So if you're able to "solve" the problem by resetting a field's value, for example, then, yes, a script can do that.

Regarding your specific trouble, a script probably doesn't need to export the records, but could reset the values for the field within FileMaker. This could be done either by looping through the records that need to be changed or by using the Replace Field Contents script step. So a script could do something like this:

Go to Layout[MyLayout] // Go to a layout that is attached to the table you need to search.
Perform Find[Restore] // Restore a find that will show the records you need to change.
Replace Field Contents[Table::Field; "New Value"]

Offering more advice than this would require actually seeing the database and understanding more about your specific needs.

wchsTech4, you are in luck because your problem is easy to fix. You don't even need a script.

BACK UP YOUR FILE(S) FIRST. Then:

1) Create two new fields:

• id (person table)
  • Options for id: Auto-enter serial number
• person_id (the table related to person)

2) Generate serial numbers for the new id field in the person table.

• Navigate to the person layout.
• 'Records' > 'Show all records'
• Click in the id field and select 'Records' > 'Replace field contents.'
• Choose 'Replace with serial numbers' and accept the defaults. Be sure to check the box to update your next serial number.

3) Place the serial numbers you just generated in the related table.

• Navigate to the related layout.
• 'Records' > 'Show all records'
• Click in the person_id field and select 'Records' > 'Replace field contents.'
• Choose 'Specify' next to 'Replace with calculated results.'
• In the drop down on the top left, select the related person table.
• Double click the id field (your calculation should be person::id, with person replaced with the name of your table).
• Click 'OK' and then 'Replace.'

4) Change the relationship.

• Select 'File' > 'Manage' > 'Database.'
• Navigate to the relationship tab.
• Change the relationship from being based on name to id.

5) Test everything. Should work.

There is a chance that your related records are being created in such a manner that you also need to modify a script, website, or something else not mentioned here to have your fix work going forward. That is important to investigate.