in the field calculation of portalfilter_month i have it
portalfilter_month = searchbymonth
Portalfilter_year = searchbyyear
In the FIELD calculation ???? Not sure what you mean by that.
This should be a portal filter expression such as:
PortalTableOccurrence::searchByMonth = LayoutTable::portalfilter_month AND
PortalTableOccurrence::searchByYear = LayoutTable::portalfilter_year
Is that what you have? (I am assuming the the fields that start with "Portalfilter_" are the fields you are using to select a year and month.
Is that what you have?
The main trouble with including user specified values in a portal filter expression is that after selecting a value, the portal doesn't automatically update to filter related records based on the value just changed.
There are 3 solutions to this problem that I know of and can only recommend 2 out of the 3.
1) Try runing a script with the script step: Refresh Window [Flush cached join results} after selecting a different year and/or month. This can be a major performance hit on your system so I don't recommend this option as the final solution. But try it to see if your portal correctly updates. If so, it confirms that your relationship and portal filter expression are correctly set up. Now use one of the following two options so that you don't have to use this script step:
2) Don't use a portal filter, include the fields used in the portal's filter as part of the relationship and then remove the portal filter expression. In your case, you could use your two pairs of fields as match fields in the relationship. This makes for a clean, rapidly updating portal, but not all relationship based "filters" can match values the way you can with a portal filter expression so this doesn't always work and it can add an extra table occurrence to your relationship graph--thereby complicating your design.
3) Include the fields used in the portal filter expression as part of the relationship, but with the X operator instead of one of the other options. Using the cartesian join operator like this, keeps the inclusion of these fields from changing what records are related, but forces the portal to update when you change a value in one of the fields.
So if you were originally filtering a portal with this relationship:
LayoutTable:PrimaryKey = PortalTable::ForeignKey
You'd change it to be:
LayoutTable::PrimaryKey = PortalTable::ForeignKey AND
LayoutTable::FilterMonth X PortalTable::PickAnyField AND
LayoutTable::FilterYear X PortalTable::PickAnyField
This approach may allow you to continue to use one relationship for several purposes where 2) above would force adding a new occurrence of the PortalTable in order to keep that relationship separate from the original.
Presently working on something like this, I was reading this with interest. I must admit that I don't get the idea of your 3rd proposition of using cartesian joins in the relationship. If they do not affect what related records are selected, what would be the use. I'd like my portal to show the restraining effect of specifying a year, for instance ...
I guess there is something I do not understand, so if you're wiling, would you elaborate a bit ?
In my case, I'm using a checkbox set to select "Year", and another one tho select "Month", both coupled with script triggers to update the portal. I chose checkbox sets to be able to do multiple, and/or discontinuous selection. This functions well, although the subsequent calculation of my summary fields in the linked table are pretty time consuming.
Happy New Year to all,
1 of 1 people found this helpful
Yes, the cartesian join matches to all records, but since LayoutTable::PrimaryKey = PortalTable::ForeignKey is also used, you only get related records where the foreign key matches to the primary key. The idea here was that you could then use the same relationship in a search portal with a portal filter and get better portal updates when the criteria referenced in the filter is modified by the user.
But this is an old post. I've learned a few tricks since then and FileMaker has changed as well.
In FileMaker 15, you can give a portal an object name and use Refresh Portal to update what filtered items appear in the portal after changing a value referenced in the filter expression.
If not using 15, I've learned the following details since this thread originated:
If non global fields are referenced in a portal filter expression, you only need commit records to get the portal to update. This does not work with global fields. Since I almost always used global fields, I hadn't observed this distinction. I've learned that if I do the following two things when using a global field in the filter expression, I can get a clean update in pre-15 solutions:
a) Use the global field as a match field in a cartesian join to the portal's table.
b) To refresh the portal, perform this script step: Set Field [LayoutTableOccurrence::Globalfield; LayoutTableOccurrence::GlobalField]
That trick is a bit weird and I've never heard a technical explanation of why it works, but it does.