Scripts screwed after changing ODBC source!

[gczychi]

Hi,

after switching from one MySQL data base to another all my scripts are screwed up. What is happening here?

What I did:

• Defined a new ODBC data source with Actual ODBC Pack. Every aspect works perfectly fine.

• Created a MySQLDump of the data source and transferred it to the new server. Both data bases are identical.

• Left all table occurrences in my relationship graph at their place and switched from the old data source to the new data source -- this is what happens:

All layouts work perfectly fine. Not only the layouts that are based on the remote data source but also layouts based on the current file that have related fields on them from the remote data base. Everything works as expected.

But all scripts with field references to the remote database are screwed up! Where once were the correct Set Field statements, for example «Fahrzeuge_U::id_Fahrzeug» is now «Fahrzeuge_U::mo_emissionclasses», a totally arbitrary field reference. I couldn’t check all >1000 occurences in all scripts, but from a first glance only the field references are bad, not the table references. See screen shots.

When I switch back to the old data source (changing the table occurences back to the original table), everything is back to normal. All field references in all scripts are reverted back to its original correct value.

Any suggestions?

Thanks a lot,

Gary

Edited July 27, 2023 by gczychi

[comment]

Hmm...

Are you sure that the fields on the layout show data from the "correct" field?

 

[gczychi]

Hm, now that you mention it, I have not checked it completely. But there are related fields on layouts that do work/are the same.

By asking your question you must have something in mind that causes this…

[comment]

I have a hypothesis that I haven't tested (and at this point, not even sure how to test it).

As you probably know, when you specify a field in a script, Filemaker actually stores the field's ID, not (or at least not only) the field's name. This allows you to rename the field without breaking the script.

I suspect that the two data sources are NOT "identical". They may have the same fields, but Filemaker did not assign them their IDs in the same order. There is not much documentation about how this process works. I suspect that for internal tables the IDs are assigned in field creation order, and I have no clue what happens with external data sources.

The FieldIDs function could ostensibly be used to verify this - but it would be somewhat tricky because of its laconic result.

With all that said, if my hypothesis is correct, you should see the same problem with fields placed on the layout: an instance of the id_Fahrzeug field should be now displaying data from the mo_emissionclasses field.

HTH.

 

[gczychi]

I checked one Layout using the external data source and then switched the data sources. This is what I got:

• FieldIDs are identical

• All related fields on the layout are mixed up

(see pictures)

So, it's not only the scripts but also all related fields on all layouts  :-(

[gczychi]

Interestingly enough, FileMaker’s IDs and field names are also identical on both data sources. I checked this with:

    SELECT FieldID, FieldName
    FROM FileMaker_Fields
    WHERE TableName = 'Fahrzeuge_U'
    ORDER BY FieldID

 

Edited July 28, 2023 by gczychi

[comment]

I am not sure what you mean by "related fields". I am even less sure what your test is supposed to prove.

I would create a layout of the Fahrzeuge_U table, and place only the id_Fahrzeug field on it (while you're connected to the old source). Then switch over to the new source. If the field placed on the layout is now mo_emissionclasses (check this in Layout mode), then my hypothesis is very likely correct.

Unfortunately I have no idea how you can fix this (other than changing each individual reference manually).

 

 

[gczychi]

Yes, you’re right. It is exactly as you predicted.

The reason why I looked up the FieldIDs and FieldNames from FileMaker_Tables was to see if I could set the ID's with ExecuteSQL. I have no idea if this will work or will destroy the whole database…

If FileMaker really uses the FieldID and not the field name at all, it might work.

[gczychi]

I will make a couple of tests and see if it works. Then, I will come back and report.

Thanks so far,

Gary

 

P.S.: What I can say so far, is:

The FieldID <-> FieldName assignments for my external data source are always the same after switching back and forth. I haven't figured out yet FileMaker's scheme how assignment is done.