Using a Cartesian relation results in an unexpected issue


the question is not clear, I know, but I couldn't come with something better.

The situation started with another question, see:

Error 407 I can't explain - Questions - - Independent FileMaker Forum. Help, Discussions & Answers for Developers and Users

Please have a look at the relationships screen shot in the first post. TO VISI_Contenus - the TO the Layout is based on - is related via a Cartesian relation to TO Fiches_NIVEAUX_V4. The reason is that no relation exists between these two TOs, and I need to display fields from TOs related to Fiches_NIVEAUX_V4. Now I realize that this creates - or seems to - a Layout with two contexts, which is very weird.

In the beginning a Find in a script is performed on fields from TO VISI_Contenus, which returns a single record. All of the purple TOs are from the previous version of the Layout which was based on TO Fiches_NIVEAUX_V4. Don't ask, it's complicated and this the way I went to achieve the goal.

The issue happens when I do a second Find on fields in TO Fiches_NIVEAUX_V4. I know that looks weird, it's from the old Layout that was based on that TO. When the second Find is performed, the Layout switches to a different record from TO VISI_Contenus, in fact the first one that was set when the Layout was loaded , via a script on OnLayoutEnter. This is where things are broken.

I don't see how I can do without a second find. I am painted in the corner !

Thanks for all the comments.

What happens when you change the relationship to a regular Un - Un?

I missed the above. Without a valid relation, a search in field of a related TO cannot be executed. The cartesian relationship always gives all records of the related TO. By definition, you cannot ‘find’ a subset, thus the error thrown.

There's no error thrown now, as in the previous question. What is wrong is the record that get's changed from VISI_Contenus.

Let's look at what you need to do. Why do you need to display these fields? What are you trying to achieve?

Using a cartesian join implies that you want to display data from every record of Fiches_NIVEAUX_V4 on the layout. Is that correct?


You have a found set (of one record) and now you want to modify the found set. You want to change the record set based on a searching data in a different table using a different layout, correct?

Then use a layout that does not have a script trigger on it.

This app was developed by someone else, I was commissioned to modify it. It was created with an older version than V 19, where the only two ways to have buttons where the old buttons, text only and Grouped buttons. The actual app in used is full of creative ways to achieve the goals, things not needed anymore.

The original Layout was based on TO Fiches_NIVEAUX, the same table as Fiches_NIVEAUX_V4 . This layout has among other things a Tab control with 3 Tabs. Consider the TOs which are purple. All those not based on table Fiches_NIVEAUX_V4 are used to display data in one of of the Tabs. The design of this part (purple TOs) is in my opinion a piece of TRAP. I planned to redo everything but I lacked time, just figuring out how it works. So part of the new Layout uses Button bars - instead of separate buttons, - and now the labels and active buttons are set by calculations. Much simpler and easy to implement. That works wonderfully.

Then comes one of the Tabs, the one I could not figure out how to redesign (the purple TOs). I thought I may easily reuse the old TOs, thats where the Cartesian relation comes in. I was wrong.

The table called Fiches_NIVEAUX should have a single record. It is used to set values used by other TOs. Thing is it has more than one record because the code uses Set Fields without selecting THE record before. Try to figure out which record is used for the Set fields... On top of that, once the fields are set a Commit is done. In the new system, I do not want users to be able to modify anything, either create, edit or delete records. The data on the tables is set prior to the app being copied to the iPads.

In the revamped app, all settings are saved in Global variables, and the values in the variables are used to do finds.

Unless there are other ways around my issues, I only see two:

  • I add fields to table Fiches_NIVEAUX to be able to create a real relationship with VISI_Contenus

  • Or I get rid of the Tab that does not work and replace it with a Card window

NOTE: I dont use a Tab control but a Slide control, on top of which I use a Button bar to switch between slides :wink:. This way can easily have my Tab label changing from French to English !

In summary, the old system worked :trophy:
and the new system does not work :skunk:

Can you update the user interface without messing with the internals?

[explanation, because that question doesn't seem to help]

Changing the relationship structure can be done very easily but relationships are the materials that bind all of your information and your application together. It is the equivalent of stripping out the steel in a tall building.

Don't be surprised when things come crashing down on your head when you implement a change in the relationship structure.

If you want to implement change in such a fundamental way, do it in parallel with the original. Don't break or remove the original, work around it, and when your new system is working and everyone is confident it is working, phase out the old system.

Please excuse any misinterpretation of your needs. I’m not in front of a computer to look at the example but what I interpret from the text -on a high level- is you have multiple lists to display concurrently and their found set can change.

Assuming this is some facsimile to the requirement, here are a couple suggestions.

If your tables have 3 additional fields - a stored calc recordid, an unstored calc recordID, and a get (summary) list from the unstored record ID, then the result of any FIND will create a list of RecordIDs. Writing this to a global match field in the layout context matched against the STORED RecordID, will produce the related records in a portal of same.

A few key features come to play:

  1. the unstored RecordID field does not require FM to traverse through all the found set. The get summary list is near instantaneous after the Find.
  2. FM uniquely supports multi line predicates (not to be confused with multi-field predicates) so every record ID in the global match field would be related. The reason for the seemingly redundant STORED RecordID is a relationship cannot be made against an unstored field. (Magic key technique excepted)
  3. you can do that FIND in any layout and grab the summary list field content, say in a card window or off screen window, write the global list and have your found set displayed.

Another less elegant but equally effective approach would be to open multiple windows precisely positioned relative to the underlying context. Then their data is not dependent on any other ….. based on your statement things are not related.

When I get in front of a computer again I’ll look at the example and see if my shot in the dark was even close to hitting the need :slight_smile:

1 Like