Let's set this up with one example and then you can extend that to more fields if needed:
Say you have this relationship:
Layout::PrimaryKey = Portal::ForeignKey
("layout" and "portal" would be table occurrence names in manage | database | Relaitonships.)
You have this global field: gSelectedFruit defined in the layout table. (since it's global, you can define this in any table you want to use for this purpose.)
And you format it with a pop up menu of three fruit types: Apple, Lemon, Peach
In portal setup, you define the following portal filter expression:
IsEmpty ( Layout::gSelectedFruit ) or PatternCount ( Portal::DescriptionField ; Layout::gSelectedFruit )
This will list all related records if gSelectedFruit is empty. If it has a value, only related records where the description field contains the same text as the global field will be shown.
Since the portal will not automatically refresh to show different records when you select a different fruit type here, you have to add a script to update the portal:
Refresh Window [Flush Cached Join Results]
For a pop up menu, the onObjectModify trigger is a good option. For Drop Down Lists where tying in the field is possible, use OnObjectSave.
Here are some other portal expressions you might use here
IsEmpty ( Layout::gSelectedFruit ) or ( Portal::DescriptionField = Layout::gSelectedFruit ) // exact,, non case sensitive match
IsEmpty ( Layout::gSelectedFruit ) or ( left ( Portal::DescriptionField ; Length ( Layout::gSelectedFruit ) ) = Layout::gSelectedFruit ) //Starts with match
IsEmpty ( Layout::gSelectedFruit ) or Exact ( Portal::DescriptionField ; Layout::gSelectedFruit ) //case sensitive exact match
Going to work on this one now. Thanks. This is much more complicated...
So I got everything set up and checked it 4x's. :) But the portal remains blank and no filter action happens. I really think I have checked it that many times.
PK = FK
gGlobalField is drop down and is GLOBAL
Portal filter: IsEmpty ( Layout::gSelectedFruit ) or PatternCount ( Portal::DescriptionField ; Layout::gSelectedFruit )
Is gSelectedFruit of type "number" or "text"? Should be text. And it is a field with global storage enabled in field options correct?
gGlobalFruit is text and is set to Global
Hmm you have a different field name, did you update the portal filter expression to use this name in place of gSelectedFruit? I imaging that you have or you'd get an error message when you try to dismiss the portal filter calculation dialog box.
If you disable the portal filter expression by clearing its checkbox in portal setup... do you see all the related records in your portal?
No I do not see the values.
Then something is wrong with the relationship instead of the portal filter.
You'll need to double check field types and the values stored in the key fields to see why the related records don't appear. Once you have that working, you can re-enable the portal filter and it should now work for you.
Why with my relationship does [=] display nothing and [X] displays all records. I get what [X] does but do not understand why [=] results in nothing displayed. Your instructions say display [=] but no matter what 'I' do, i cannot display any records with [=] relationship.
Layout::Main_ID = Portal::fk_Main_ID
They are both 'number fields' indexing is on. The Main_ID is serial and fk_Main_ID is not.
If you do not see any records then fk_Main_ID is not storing a value that matches Main_ID in at least one record.
Try this test.
Make Main_ID visible on your layout based on "Layout". Make fk_Main_ID visible on your layout that is based on "portal". Do a show all records and switch to table view on both layouts. Inspect the values in these two fields and see what is stored in each. I predict that you won't find matching values in these two fields--probably one of the two fields is empty.
Yes I did that last night. It is blank, why? Do I need create record in relationship set up checked. Please tell me NO! :))
The "Create" option makes it possible to use a portal to add related records and not need to also enter the matching ID number as it would be added for you. This can be very useful, but is not strictly necessary. However you choose to fix it, both fields need matching values before the basic portal set up can work.
OK So now I checked it and I have one record and no others. This is a really big lightbulb moment for me so what is next? This is the lightbulb moment for me on one of the biggest areas of FM. Relationships...
Why is there only one record showing?
How many related records have a Fk value that matches the PK for the current record showing in Layout? I would assume only one record if you have only one showing and you still have the portal filter disabled.