That sounds like a really fun project.
Have you considered doing a performance test between the methodology that you sketched out versus trying to do the same thing via SQL updates with a plugin (e.g. BaseElements plugin)?
I believe that the FileMaker metadata tables should provide you access to the information that you would need regarding field names and types.
At the end of the day, I believe you would still be performing an update on one record/row at a time, so the big questions as I see it are:
- Can you tolerate using a plugin?
- Which incurs more overhead?
- Which is easier to do error detection?
- Which is easier to customize?
One thing that I find attractive about using the meta-data tables is that you should be able to detect an error condition whereby one of your data layouts was not updated after a new field was added to the schema. The flip side to this is that using your layout approach allows you to selectively omit certain fields from the munge process, e.g. a field that validates against a value list of defined strings whereby the munge process would result in invalid data.
I would be curious to find out what you settle on, should you be willing to keep us updated with your progress.
Best and good luck!
You could use a goto field to step through each field in the tab order, check the active field name against your list of fields to handle and do a replace "in place" without specifying a target field.
I had thought about that, and might be able to adjust accordingly. But I'm not confident that:
1) All fields will always be on the layout
2) The tab order will always be correct, and include all fields.
IE, what do I do when there were fields in the list that were not addressed?
In retrospect, I'm not terribly worried about how long it would take to run, it just seemed like a series of replaces is more graceful than going to every record, then every field, and using a set field step.
I thought about doing it "from the outside". but soon figured that there would be issues with certain things. IE how to handle containers, field repetitions, etc.. special filemaker things that sometimes act finicky in ESS.
Since there are a few more design functions in filemaker that are useful in this instance (IE FieldType() ), I think native filemaker is the best way to go.
As noted to Wim below, in retrospect I guess speed isn't necessarily that important to me, so I intend to build out the most robust script I can to handle all "variables" of data that could happen, and test extensively.
In that case you probably should use the FM metatables and build a data dictionary table (even if it just in memory) so that you can keep track of what fields have been set. That way you can also process fields that are NOT on any layout but may contain data.
This makes sense.
The one thing I would mention again, however, are the metadata tables. Wim also mentioned this in his second post.
What I am getting at is that I think the metadata tables would be a more reliable means for you to get your list of field names, versus using FieldNames. If you are using FM12, then no plugin/ESS should be necessary to go this route.
Possibly you could utilize the strengths of each of the methods for getting at your field names:
- You could use layouts to determine the list of fields that will be processed (obtaining the list using FieldNames). This would allow you a simple means for "customizing" which fields get processed, i.e. you just edit the layout.
- You could use information from the metadata tables to consider all fields, and generate a report of, for instance, a list of all non-calculation fields which were not processed, which might make the system a tad more robust.
This sounds like the right path, thanks again Wim.
I did want to make an FM11 compatible version of this, but it's not a make or break requirement.
I'm not finding much on the use of metadata tables, could you point me in the right direction of a whitepaper or functions guide?
Andrew Duncan of Databuzz wrote a nice blog about this topic which is here:
I think it's got everything you need to get up and running with this.
fantastic, thanks for all the help!