I am still trying to get my head around using table relationships that relate to the same table.
Perhaps this tutorial on Table Occurrences can help: Tutorial: What are Table Occurrences?
I was wanting to create a portal on a different tab that would list all of the project leads meeting a certain criteria (such as "Hot Lead" in the field Lead Status).
What version of FileMaker do you have? If you have version 11, a portal filter expression would be simpler to set up instead of an additional relationship. Using what you have already set up, How did you define the field "hot list key"? Is this a field of type calculation or some other type? Stored, unstored or global storage?
I have version 11. I will read about the portal filter. I had never used a portal to show a list of records out of the same file the layout is based upon. The field "hot list key" is setup as a calculation field and the calculation is stored and indexed, not a global field.
And what is its return type? (Should return text and this is controlled by a drop down in the specify calculation dialog.)
Is Lead Status a field of type text?
It's return type is set to text and the Lead Status field is a text type.
So you have this relationship in Manage | Database | Relationships?
ProjectLeads::Hot Lead = ProjectLeads 2::Lead Status
Project leads 2 is created by selecting ProjectLeads and clicking the duplicate button (button with two green plus signs). You can double click the new table occurrence this creates to give it a more descriptive name.
If this is what you have set up, then you can add a layout to ProjectLeads 2 on your ProjectLeads based layout and it should list all records where Lead Status = "Hot Lead".
Using the Portal Filter approach, you can use:
ProjectLeads::anyfield X ProjectLeads 2::anyfield
and then use this portal expression:
Project Leads 2::Lead Status = "Hot Lead"
This eliminates the need to add a special calculation field just to get the portal to work.
If you have more than one status value in lead status, you can even use a reference to a global field in place of "hot lead" and then set up the global field with a value list and a triggered script so that selecting different values in the global field produces a different lists of records in the filtered portal. This can also be set up at the relationship level (that's how we had to do this prior to version 11) instead of using a portal filtering expression.
I had actually set it up backwards from what you described.
ProjectLeads::Lead Status = ProjectLeads 2::Hot List Key
I reversed it and it worked perfect. I see know where my logic was flawed.
Thanks a bunch. I will try the other approach as well to make sure I understand it.
I have a followup on this issue. I am now trying to display a set of records in a new portal that will show a range of values. Given the example before, how would I structure the relationship if I wanted to display all of the records that were equal to "Dead Lead" or "Dead project" or "Lost to Competition".
With a relationship, you can set up the constant calculation as: List ( "Dead Lead" ; "Dead project" ; "Lost to Competition" )
and link that to the lead status field in your relationship. The returns separating each value in the matching field sets up a match that matches to any one of the listed values.
With a portal filter, you just update the portal filter expression:
Project Leads 2::Lead Status = "Dead Lead" or Project Leads 2::Lead Status = "Dead project" or Project Leads 2::Lead Status = "Lost to Competition"
I did use the portal filter method with success, but the sorting in addition to the filtering on the portal seems to slow things down. I only have around 1500 records, but a dialog pops up saying it is sorting with a status bar and it takes 15 secs or so to complete. I worked this out after I made the post last night. I was wanting to try and get it working with the relationship since that will make the reports easier as well.
I will give the first method you listed a shot. Not familiar with the List function, but I will readup. Thanks.