Personally Ron, and this may not work for you, I just work on a copy of the live database then once i've got the change working and tested, I just do exactly the same to the actual live database.
Working on a live database is dangerous. While I do make small changes to a live database regularly. I am careful to avoid making any changes to table/field definitions on the live database. When you dismiss Manage | Database and it applies the changes you made, the records in your tables are briefly locked against edits and this can keep scripts from correctly modifying data if they are being run by another user the same time you are clicking the OK button.
To get an empty copy of your development file, just do another save a copy as... operation and select the clone option to get an empty database.
In general, many developers use one or both of the following strategies for updating a database:
Write an import records script that systematiclly imports all records from all tables in the old file into matching tables and fields in the new file. Include code that uses Set Next Serial Value to update any and all serial number fields. With larger databases, you often have to set this to run overnight so that you can install the new file in the morning. I've even had to do the imports in stages in order to make a smooth transition.
Split your file in two: File 1 is an interface file that contains no data, just the scripts, layouts, value lists etc that make up the part the user sees. File 2 is the data file with all the data source tables. Since many updates only affect the interface file, you can update that file simply by replacing it with the new file without having to import any data at all.
Thanks. I would definitely like to migrate to a data seperation solution at some point. But for now I will have to stick with option one until I can dedicate the time to deconstruct and seperate the data from the UI.
Could you provide an example of the script I will need to import all records. I understand that it will have to loop though all the tables but will I need a special layout for each table to do this? Do all the fields in a table have to be on a layout to import them?
Unlike manual imports, you don't need any specific layouts for doing the imports.
You do need a layout of some kind for each table where you need to update a serial number setting so that you can sort your records by serial number value and then go to the first or last record to determine the current maximum serial number value (You could also use a "maximum" summary field for this) and then update your serial numbers. These layouts can be completely blank as long as the table occurrence specified in "show records from" in Layout setup... refers to the correct data-source table.
I am trying to figure out how to write the script to do this but I just can't wrap my head around it. Could you please provide an example of a script to import and update one tabe? By the way, my ID fields use a text prefix in the serial if that makes a difference.
See this recent thread for examples: Problem with auto-enter serials when importing into (empty) clone
I haven't tried this with leading text defined in the next serial value setting--I prefer to load such text in a separate field and concatentate the two values when needed for display purposes--so you'll have to test that part and see if set next serial value will work for you.
I found in another post that there is a SerialIncrement ( text; incrementBy ) function that works on a serial number with a text prefix. But I have no idea how to use it in a script.
Serial increment allows you to change the value of a number that is part of a text string.
I'd just use either:
textfield & SerialNumberField in a calculation field
On a layout. (these are called merge fields in filemaker)
Please note that you may be able to use set next serial value with no trouble at all. I'm just warning you to check and be sure.
That will work.
It's also possible to use a container field and Insert file to ask the user to locate the import file. The script inserts a reference to the selected file in a container and then extracts the file path from it to refer to in Import Records steps. This allows the script to work no matter where the import file is located.
Continuing on here. I have written the script to import each of the tables in turn. But as it goes from table to table it is asking me for a login to the source file each time it imports a table. Is there a way to avoid this?
That's an annoying "feature" of the current versions of filemaker. Even if you've defined the same account name and password in both files, using Import Records on an unopened file triggers the request for a password. Since performing an Import Records step on an unopened file automatically imports the entire table, this is a simple way to get all the records but now you can't set the script to run overnight or while you take a break as you'd have to sit there and log in as it switches from table to table.
If you import records from a file you have already opened, filemaker will import from the table's current found set and this could fail to import all the records in the table unless you take steps to get all records into your found set first.
Write a script in the source file that loops through all your layouts and does a show all records on each so that all tables you'll import from have a found set of all records. Now perform this script from your updater first, then import the records. If the account name and password you have used to open your current file matches that of the source file, everything runs smoothly without any requests for a password as the perform script step opens the file in the background and does so with the current file's account name and password.
That is annoying :(
A couple of points. If you Unsort a table, the last record should have the last serial ID. If it does not, it means you've done some odd importing before. So, you should test first, but there should not be a need to Sort an entire table, nor use Max().
There is however a small weakness in using the method of getting the "next serial id" from the last record. Imagine this scenario. What was the last record in the table was deleted just before the import. It had child records in another table. [x] Delete related records was not checked in the relationship. The child records are therefore orphans, with the ID of the missing "last" record.
If you reset the serial ID to that of the current last record, then the next created record will have the serial ID matching those orphaned child records, so they will appear connected.
The chances are small, but it is possible. There are 2 options to prevent this happening.
1. Always check [x] Delete related records in the main parent->child relationship. Or,
2. Don't use the "last record" method. In FileMaker 5.5 a special function was added, in the Design functions:
It requires that the other file be open. You must hard-code the name of the other file* (which generally you already needed to rename beforehand, in order to set up the Import steps). You no longer need to hard-code the field name however. You can use the new (10) function, GetFieldName (which basically returns the name of a FileMaker field as text; it does not break if you rename the actual field later).
I use this right after the Import. I also have a subscript, which runs after all the imports, which uses it again, as above, but also for the current file (which can use: Get (FileName) for its name). It sets a Variable for each "next serial id", then compares them. If this 2nd subscript says they match, I know it all went OK. The subscript is a modified copy of the Imports script. Since it's only setting variables it is quite fast, a couple seconds for 50 tables.
*Actually, you could call a script (of several) in the "_old" file to go to the table and get its next serial id, in which case you could use: Get (FileName). But I'm OK with hard-coding the old file's name. Which reminds me; test open that file before going far, to catch a wrong name.
[P.S. On a Mac the Keychain can store the access account & password for the "_old" file, to use when setting up the Import steps. You can then delete it.]