Relationship with a Portal

[Tracy]

OK...I'm having a complete brain freeze on this so let me try to explain (in detail) what I'm looking for. First off, I'm working in FMP11 (I know...don't ask).  What I'm trying to accomplish: To display a single or series of records in a portal in one table based on a text field and a checkbox field on that table. The records are created on another table.

The Setup:

1. I created a single database with two layouts, each having their own table.

2. In one table, I have it labled as "List" where I have roughly 10K+ records.

3. The "List" table has four text field.

3a. Chemical (Standard text field)

3b. Material (Standard text field)

3c. Rating (Standard text field setup as five radio buttons - A / B / C / D / N). This basically tells me how well or poor the Material name is rated for that Chemical name.

3d. Compatibility (Calculated text field based on the Rating Field). If Rating = "A", then display "Excellent", If Rating = "B", then display "Good", If Rating = "B", then display "Fair", If Rating = "D", then display "Poor", If Rating = "N", then display "No Data". I got this to work..haha

3e. The "List" table records are in blocks of 40 for each "Chemical" name. Meaning, every 40 records has the same "Chemical" name but 40 different "Material" names associated with that "Chemical" and plus a different "Rating" option for each material...see below

4. On the second table called "Form", I want to create a portal based on the "List" table.

4a. The "Chemical" name field on this table (not sure if a new "Chemical" text field should be created on this table or use the "Chemical" name field from the "List" table) would like to be a drop down list based on all the chemical names from the "List" table.

4b. The "Rating" field is based on the "Rating" field from the "List" table but displayed as a checkbox set where I can select multiple options. Again, not sure if a new "Rating" text field should be created in the "Form" table or use the "Rating" field from the "List" table.

4c. The portal will diplay "Material" & "Compatibility" fields from the "List" table only and all the potential options based on what I select as a chemical and rating (could be multiple Rating selections).

4d. Finally, If I only selct a chemcial name from the drop down and don't select any rating options, I would for all the rating options to display. Think of it as eliminating records when the rating option(s) are selcted. The more I select, less records display in the portal.

5. What I need you advise with is:

5a. Setting up the "Form" table with proper fields

5b. Setting up the relationship bewteen the "Form" and "List" tables

5c. Setting up the Value Lists for any of the fields.

 

I'm pretty sure this has been done before but I could find any forum topic related to exactly to what I need. Thanks for you help! Let me know if you needs anymore information or clarifications. I'll be monitoring this thread closely because I would to complete this as soon as possible.

 

[comment]

For purposes of my own sanity, let us refer to your "List" table as Materials, and to the other table as Viewer.

Now, if I followed this correctly, once you have selected a Chemical to view in the viewer table, there should be 40 related records in the Materials table - and you want to see either all or some of them, based on your selection of Rating. If so, I would suggest you do it this way:

Define these fields in the Viewer table:

• gChemical - Text, Global
• gRating - Text, Global

Define the relationship as:

Viewer::Chemical = Materials::Chemical

Place a portal to Materials on the layout of Viewer and filter it using this expression:

IsEmpty ( Viewer::gRating ) or not IsEmpty ( FilterValues ( Materials::Rating ; Viewer::gRating ) )

Define a script that does:

Refresh Window [Flush faced coin results]

 

Attach an OnObjectModify script trigger to the gRating field and select this script.

That should be about all. Unfortunately I cannot test this as my v.11 keeps crashing.

Edited November 23, 2021 by comment

[Tracy]

I set it up like you said but not working. I could be doing something wrong. Do I enter A, B, C, D, or N in the gRating field? The portal changes but the "Comaptibility" field from the "Material" table always say "No Date". "No Data" only should

state when an "N" is selected but it say that no matter what letter I type in the gRating field.

[comment]

7 minutes ago, Tracy said:

Do I enter A, B, C, D, or N in the gRating field?

The gRating field should be formatted as checkbox set, using the same value list as your Rating field in Materials. Make sure it is empty before you format it as such.

 

5 minutes ago, Tracy said:

The portal changes but the "Comaptibility" field from the "Material" table always say "No Date".

Are you sure the field is from the same TO you have used for the relationship and the portal? 

 

[Tracy]

I think I'm almost there. The only thing that is hanging me up is the display of the "Compatibility" field from the "Materials" table in the portal. Everything else seems to work as defined by you. Now

the "Compatibility field show "Good" for option of the gRating field.

Correction:

the "Compatibility" field show "Good" for EVERY option of the gRating field.

Found the problem. All fixed and works just like you said! Thank you so much...

[comment]

1. Does the number of records in the portal change when you check different boxes in gRating?

2. For testing purposes, please add the Rating field to the portal. Does it show only and all the values selected in gRating?