I have found one way that seems to sort of work for my first question, but I have a follow-up. In the same database as before, I need to filter a report with a portal by date. I need to be able to pick from a Value List of dates, then have the portal display only records in which a field matches the date picked from the list. I tried using a Global field, but couldn't find out how to set a Global field based on my selection from the Values List. Thoughts?
I tried using a Global field, but couldn't find out how to set a Global field based on my selection from the Values List.
The field formatted with the value list should be your global field. Then selecting a value in it can cause your portal to update automatically with the selected value. The global field should be included as a match field in the portal's underlying relationship.
I thought that a Global field could only contain one value. This Value List will include every date that any person took a trip. Maybe I'm missing something. Is it possible to upload my sample database so that you can see what I'm talking about? Thanks for any help you can be. I'm trying, but I'm just so used to how things are done in Access.
It depends on what you mean by "value".
A global field will store the same data no matter what record or layout is current. The field itself will store what ever data you enter by selecting a value or values from the value list and that should be exactly what you need here.
Once you have a value in a global field, that value can be the match field that used in a portal's underlying relationship to control what records appear in the portal or it can be used in a scripted find to pull up or modify a found set.
I think I'm confused by your comment "The field formatted with the value list should be your global field." I have a global field called "Departure Date". In my Trips table I have a field called "Leave Date" (which is really the same idea, but with a different name). On my report/layout which is going to display the trip details for a particular Departure/Leave date, I have created a pop up menu using the Field/Control tool. In the Inspector (under the Data tab) what should I pick as the "Display Data From" box and what should I pick from the "Values from" box? Also, I created a Value List from the "Leave Date" field in the Trips table, which is what I had picked for the "Values from" box. These are only enabled in Find mode. I then set up the portal to filter by Leave Date. When in the layout, the find pop up lists the dates as needed and correctly finds a date, but the portal continues to list all leave dates, not just the one I selected in the find. That's why I thought I could pick a date from the pop up menu and have it write that selection to the Global field and then filter the portal by the global field, but that hasn't seemed to work. Has this helped or confused things even more?
It reveals more things to straighten out.
When you place a portal on a layout, the layout references data via the Tutorial: What are Table Occurrences? name selected in Layout setup | Show Records From. The Portal references data via the table occurrence name selected in Portal Setup | Show Related Records from.
When you enter find mode, specify search criteria and perform the find, the find is searching the layout's table for records. Any records in the layout's table that match the specified criteria are displayed as a found set for that layout (and all other layouts that specify the same table occurrence name.) Once that found set is produced, FileMaker returns to Browse Mode and any records shown in the portal will be shown as specified for the relationship between the layout's selected table occurrence and the portal's selected table occurrence. The find criteria specified for the find has no direct effect on what records appear in the portal.
To selectively control what records appear in a portal must be done via other means that do not use find mode at all.
If you are on a layout for Travelers and you want to see records from Trips that fall on a specific date, you can set up this relationship:
Travelers::__pkTravelerID = Trips|Date::_fkTravelerID AND
Travelers::SelectedDate = Trips|Date::Date
By setting up SelectedDate with a value list of dates, you can select a date and see only the trips records for that traveler that have that date in the Trips|Date::Date field in a portal that refers to Trips|Date in the Show Related Records From drop down in portal setup.
I am using Trips|Date as the name of a new table occurrence of trips on the assumption that you already have a relationship between traveler and trips that matches records by traveler ID. Using a new occurrence allows you to keep the existing relationship in use unchanged in other parts of your database file.
In this example, SelectedDate could be a global field and often, it is a good idea to set it up that way, but it doesn't have to be in order for this to work.
For an explanation of the notation that I am using, see the first post of: Common Forum Relationship and Field Notations Explained
On the other hand, you can use TravelerID and a specified date to perform a scripted find on a layout based on the Trips table instead of the Traveler table to pull up the same records.
This seems very workable and within my grasp of FM. I will work on it and get back to you. Much thanks!
OK. I think I"m getting there, but the filter doesn't seem to be working in the portal. In Relationships I set up a 2nd occurrence of both the Travelers and the Trips tables. I then created a relationship between both Traveler ID fields and also the Trip:Departure Date and Global Departure date fields. On the layout the pop up menu now does list the available dates and does actually assign the chosen date to the Global field. The portal selects from the Trips2 table and filters on: Trips:Departure Date = Global: Departure Date. However, when I pick a date it doesn't actually invoke the filter correctly. If I remove the filter then all records are shown, which is what I would expect, but when I check the filter as above, it doesn't seem to work. What am I missing? Once I get this working I can use it several different ways throughout the database. Thanks for your patience!
In Relationships I set up a 2nd occurrence of both the Travelers and the Trips tables.
I only suggested making a new occurrence of Trips. If you make a new occurrence of Travelers, your layout must be based on the new occurrence of Travelers instead of the original occurrence of travelers or your portal to the new occurrence of Trips isn't likely to work as expected.
And I haven't specified using any portal filter as this method filters the records at the relationship level--which eliminates the need for a portal filter.
I think I have tried everything you said, but it still won't filter my portal. I am trying to upload a snippet of my relationships graph and my problem layout.
Here is the problematic layout.
See how it still displays all the trips. I just can't figure out what I'm doing wrong.
The relationship between Global Values and Trips 2 has no affect on what records appear in the portal. Only the relationship between Travelers 2 and Trips 2 controls what appears there.
Define the global departure date field in Travelers. Drag from Travelers 2::Departure Date to Trips 2::Leave Date so that your relationship now uses 2 pairs of match fields linking Travelers 2 to Trips 2.
Make sure that Travelers 2 is selected in Layout setup | Show Records From.
Make sure that Trips 2 is selected in Portal Setup | Show Related Records From.
Wow. We are really close. This doesn't seem intuitive to me, but it works. Now, just one more problem. As you can see it is now only showing the trips for the selected date, but it is still showing the other person, though his trip list is empty. I don't even want to see the other person if that person has no trips on that date.