Jump to content
Sign in to follow this  

Relation to External Data Source Failing

Recommended Posts

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 by GeoSteven

Share this post

Link to post
Share on other sites

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.



  • Thanks 1

Share this post

Link to post
Share on other sites

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 by comment
  • Thanks 1

Share this post

Link to post
Share on other sites

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

  • Thanks 1

Share this post

Link to post
Share on other sites

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>  :wacko:. 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. 

Share this post

Link to post
Share on other sites
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.

  • Thanks 1

Share this post

Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.
Note: Your post will require moderator approval before it will be visible.

Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

Sign in to follow this  

  • Similar Content

    • By KevinP
      What does the superscript letter in the upper left corner of a field mean?
      I'm trying to relate fields in one table to fields in another table . . . 

    • By amerioca
      Hi everybody,
      I recently amended a db from an old Lynda course, controlling shore excursions for river cruise ships. For my limited expertise (I am still a rookie at the very best) the construction is somewhat complex as there is many vessels, different routes, different charterers and consequently different shore excursion programs and prices. In some cases the shore excursion program of different vessels and operators is similar but not the same and so on.
      I have 7 different tables partly related (pls see graph attached):
      For every journey ::customers and ::trip are assigned a tour code and date (“tour code” is not unique as journeys repeat themselves during the season, “Tour Code” in conjunction with “Date” is unique)
      Every tour operator/vessel has many trips on every journey. All table occurences and fields are uniquely defined by unique id.
      Now I want to create an opening layout reflecting all relevant cruise information such as passenger list from ::customer and ::trip (shore excursions) for the specific journey, as well as other information in text fields.
      But I seem to be unable to get the right relationship and layout setups together. I tried all possible relations and layout setup combination but nothing worked so far.  
      On the same layout I was able to either retrieve the passenger list from ::customer for each journey or the offered shore excursion (trip) list from ::trip  but not both together on one layout.
      What is it, I am doing wrong? Any ideas are appreciated

    • By Oyseka
      Hi All, I have a database where the relationship between the Quotes table and the QuoteItems table is set up such that if a record is deleted in the Quotes table, the related records in the QuoteItems table are also deleted. I have now been shown an instance on one Customer where there are four Quotes missing from the Quotes table but all the QuoteItems are still in the QuoteItems table. How can this be done, I am unable to recreate a circumstance where I can delete a quote and leave the quoteitems in place.
      Any help appreciated
    • By H
      i have 3 Tables Property , Units , Tenant.
      Each property has many units , Each unit has one tenant active but may have many inactive tenants from previous years.
      All working fine BUT.....
      i would need to show a table in the property layout of the units and each one only with its active tenant.
      The portal is based on the units table. 
      However the tenant just show up the tenant that was first input to the unit.
      My question is , how could i get that only the active tenant should show to the unit?
      i would really appreciate it someone could guide me on this.
    • By Hproth
      I'm sure there is a simple solution to this problem, but I can't seem to figure it out!
      I have a custom app that allows the user to write letters amongst other things. 
      Each patient is linked to a GP and a Consultant, when writing a letter to a patient there is the option to CC both the linked GP and Consultant. However, occasionally another GP or Consultant might need to be CC'd in the letter (in addition to the ones already linked to the patient). So I would need the address of this additional consultant/GP to also be CC'd in the letter. For the life of me I can't work out a simple way to do this. 
      Does anyone have any suggestions?
      Thanks in advance.
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.