Relation to External Data Source Failing

[GeoSteven]

All files/tables in this discussion are fmp12 using FM 17 and both files are opened when initiating the app. "logging" is opened as the primary file and "claims" is opened as an external data source.

I have a table called "newclaim" in "logging" which contains mailing address information including a USPS zip code. I have a table called "lu_ZipCode" in "claims". There is a relationship set between "newclaim" and "zipcode" on a field called "COID" (company ID). This field is automatically populated in both tables with "DCSI". This is an old method (used in Fm6) of establishing a simple relationship so that global fields can be used to pass parameters into scripts for zip code lookup and verification. 

In the parent script (in "logging") I set the related field "lu_ZipCode::_gZipCode" with (for ex) "12345" with the intent of calling a subscript in "claims" then using "12345" to do the lookup. However when the subscript is called the "_gZipCode" field is empty. As a workaround I called the subscript passing the "12345" as a parameter which of course works as one may expect. After setting the parameter to a local variable in the subscript I'm able to do the lookup then populate the global fields "lu_ZipCode::_gCity" and "lu_ZipCode::_gState" while still in the "claims" file. Yep, sure enough when I go back to the parent file the global fields I just populated are empty. 

It's worth pointing out here that prior to yesterday "zipcode" was a separate file and the methods described above worked like a charm. This effort is being put forth to consolidate this old FM6 solution with 40+ files into just a few files. I imported "zipcode" into the "claims" file then copied and modified the scripts anticipating that this would be a quick little project to get rid of one more file.

I know there are better methods (ie JSON) to get this done and I will probably go that route. At the same time I'm left scratching my head on why this tried and true method doesn't work. Why would global fields I can populate from one side of the relationship not be populated when viewed from the other side? 

Edited July 12, 2019 by GeoSteven
clarification

[Wim Decorte]

I'm not quite sure I follow the part where you try to establish temporary relationships.

Some thoughts:

- passing information between scripts: using script parameters is a good way to do that, that's what it is there for.  But setting global fields should work too

- you do not need a relationship to set global fields in a different table; if that is what you are doing then you can scrap the whole mechanism.  You can set a global field anywhere at any time, without a relationship.

 

 

[comment]

You are asking us to debug two scripts we cannot see. The best you're going to get is a guess. Why don't you use the debugger along with the Data Viewer to locate the exact point of failure?

In general, I would say that if a script sets a global field to a value and the global field is still empty, then the most likely causes are :

• the value is empty;
• you're setting the wrong field;
• you're reading the wrong field;
• the user setting the field is not the user reading the field.

Since you say you have moved the target table from an external file, the "wrong field" theory (as in "wrong TO") seems the most likely.

 

 

Edited July 12, 2019 by comment

[bcooney]

And the old "prefixing with a g < > global field" - check the storage setting!

[GeoSteven]

bcooney - thank you for the advice. When I imported the zipcode file the fields that were globals in the old table did not import as globals but rather as text fields. <facepalm>  . Wish I'd seen your response sooner but in the end I wound up re-writing the script so that passing parameters became mute. As mentioned this was old code from FM6 and I was trying to make a quick change. We all know what a quick change can turn into. 

Wim Decorte - setting a global field in another table from anywhere can be done as long as the tables are in the same file but unless I'm mistaken this doesn't apply if the global field is in a table referenced in an external data source. Is that correct? 

comment - I was using debugger and data viewer. When I was in the parent script and set the value the field and value was showing in data viewer as expected. When the sub-script was called then the same fields in data viewer showed 'unrelated table'. 

The parent script was run from a file called 'logging'. From that perspective the table I moved (zipcode) was moved from one external data source (zipcode::zipcode) into another external data source (claims::zipcode). The TO chart in logging was changed to reference the newly moved table. In theory the script that worked with the old configuration (zipcode::zipcode) should have never seen the difference.

Of course, in theory, I should have also looked closer at the imported table to be sure those fields were actually globals. ;)

Thank you all for your responses. 

[Wim Decorte]

1 hour ago, GeoSteven said:

Wim Decorte - setting a global field in another table from anywhere can be done as long as the tables are in the same file but unless I'm mistaken this doesn't apply if the global field is in a table referenced in an external data source. Is that correct? 

No, that is not correct.  You can set a global in a table in another file, as long as you have  TO to that table in the file that runs the script.