What version of FileMaker are you using?
What purpose is served by a relationship for your reports? (There may be an alternative here.)
Do You need to see all these different portals at once or would it work for you to have one portal where you can see different categories and totals by selecting a category (and possibly other data) in some value list formatted fields?
Hi I'm using FMPRO11
The purpose of the relationship and showing the information in categories is because as a personal taste i prefer to see the information by browsing the records instead as a result of search and sort.
I would prefer to have one portal and beeing able to filter them by selecting the category, for example a value list with CAT1, CAT2, CAT3
thanks for your interest
I asked about which version of FileMaker and about a value list controlled portal because there's a feature new to FileMaker 11 that makes this easier to set up than in older versions. (Where you can still do it, BTW, but takes a slightly different approach.)
Let's call your tables and the basic portal relationship by these names:
MainTO::PrimaryKey = PortalTO::ForeignKey
Define the following fields in Main. In multi-user systems, global storage is a good field option for each of these so that different users can interact with this portal at the same time without interfering with each other: (starting the field names with g is a naming convention for identifying global fields.)
gDate1, gDate2 (date) (Note: date field in your portal table must be of type date for this to work.)
gCat1, gCat2, gCat3 (Text)
On a layout based on Main, place a portal to PortalTO on it. In portal setup... click the portal filter option and enter this expression:
If ( Not IsEmpty ( gDate1 ) and Not IsEmpty ( gDate2 ) ; ( MainTO::gDate1 < PortalTo::Date ) And (MainTO::gDate2 > PortalTO::Date ) ; True ) AND
If ( Not IsEmpty ( gCat1 ) ; Main::gCat1 = PortalTo::Cat1 ; True ) AND
If ( Not IsEmpty ( gCat2 ) ; Main::gCat2 = PortalTo::Cat2 ; True ) AND
If ( Not IsEmpty ( gCat3 ) ; Main::gCat3 = PortalTo::Cat3 ; True )
This gives you an "all in one" portal. The logic of this expression is set up so that if any element ( a date range or one of the categories ) is left empty, that part of the expression then does not filter out any records. If all the fields are left blank, you see all the related records. If you want to use a separate portal for each category, you can simplify the above expression in each portal to only use the date fields and just one category field.
Place the gDate1 and gDate2 fields on this layout and format them with drop down calendars if you want.
Place gCat1, gCat2, and gCat3 on the layout and format them with value lists that list their respective catgory values.
Now write a single step script: Refresh Window [ Flush Cached Join results ]
On each of these fields, set a script trigger to run this single step script to refresh the portal any time you enter a value in one of these filter control fields. For fields where the user can type in data, (Date fields or drop down lists), OnObjectExit works well.
For fields where data can only be entered with a single mouse click (Checkboxes, radio buttons, pop up menus), OnObjectModify is a better choice.
Note: Many other filter expressions can be used. If you need a "portal total", you can define a summary field in the related portal table and then place it inside a 1 row portal of its own with the same exact filter expression in order to display a portal total that will update with each filtering option.