Unlike other database systems where you can define a SQL query as the record source, FileMaker layouts are based on a specific occurrence of a table (Table occurrences are the boxes found in Manage | Database | Relationships). When you set up a layout to Deals, you create a direct connection to the Deals table. It's like a Select * query with no Where and no sorted by clause. It doesn't "go through" any relationship. You can perform find, do show all records, show omitted only, etc and you are working with all the records in the table. The relationships you define to the layout's table occurrence control how you can access data in any other tables--not Deals.
If you based your layout on Globals, a portal to Deals could be created to list all records from Deals with status = 3. The Globals to Deals relationship then works to control what records appear in the portal.
If you want to base your layout on Deals, you'll need to use scripts to restrict the records shown to the specified status.
A script can perform a find or use Go To Related Records to pull up the desired found set. Script triggers can catch the transition from find back to browse mode to kick in a script that constrains the found set to only records with status = 3. An if you have FileMaker advanced, you can use custom menu sets to either disable the show all, show omitted options or replace them with scripts that also filter out all but status = 3.
Many layouts are based on a Companies table, which has a CompanyTypeID [1 = Business, 2= Hotel, 3 = Restaurant, etc.].
Have built a layout for Hotels, where the script does a Perform Find which filters the layout for CompanyTypeID = 2. But if I try to find a hotel that starts with the same few letters as a business or restaurant, those records that are not *only* hotels [companyTypeID "2"] also appear.
How can I completely exclude anything other than the preferred CompanyTypeID??
Thought the relationship method using a global field might work, but it doesn't.
Need to find a rock-solid method of filtering layouts.
Any assistance will certainly be sincerely appreciated.
1 of 1 people found this helpful
To quote from my last post:
Script triggers can catch the transition from find back to browse mode to kick in a script that constrains the found set to only records with status = 3.
In layout setup, click the script triggers tab and select "OnModeExit" and specify the "Find" mode option.
Have it perform this script:
Enter Find Mode
Set field [YourTable::CompanyTypeID ; 2]
Set Error Capture [on]
Constrain Found Set
WIth this trigger in place, any find that you perform will automatically constrain down to just records of type 2.
It's something of a Kludge, but I also worked out a different approach that can be used if you prefer:
Define a table, HotelTypes, and link it to your current table like this:
YourTable::PrimaryKey = HotelTypes::ForeignKey
Enable "allow creation" for HotelTypes in this relationship. Enable the delete option for it as well.
Use the OnObjectSave trigger on YourTable::CompanyTypeID to perform this script:
If [YourTable::CompanyTypeID = 2 ]
Set Field [HotelTypes::ForeignKey ; YourTable::PrimaryKey ]
Else If [Not IsEmpty ( HotelTypes::ForeignKey) // company type used to be 2 and has been changed.]
Go To Related Record [Show only related records; From table: HotelTypes; Using layout: "HotelTypes" (HotelTypes)]
Delete Record [no dialog]
Go to layout [original layout]
Since this table only contains records for companies of type 2, you can now base your layout on HotelTypes, but include all needed fields from YourTable as this is a one to one relationship, but you can only see and access type 2 records.
Will try both and see what works most efficiently.
On the second choice's script, a little uncertain of why "DeleteRecord" is there??
It's there to maintain data integrity. Say you create a new record and mistakenly identify it as type 2. Then you discover your mistake and correct the error by changing the type to a different value. If the script does not delete the related record from HotelTypes, you will still see this record listed on the HotelTypes layout even though it is no longer a type 2 company.
Looking at your second choice ...
I have a table already, named Hotel Details. It works in conjunction with the Companies table, like a sub-form in Access.
It's related to Companies via CompanyID in both tables, and has only records relative to hotels.
Any way I could use that as the "filter" ?
*** The overview is the the Companies table handles generic info, ie name, company type, address, etc, where the Detail tables - relevant to each company type - handle specifics. Ie Hotel Details will have rate & type of room fields, where Restaurant Details will have a field outlining type of cuisine.
Yes, provided that it's a one to one relationship.
Ok, have the second choice working with my Hotel Details table.
Just added the CompanyTypeID to it, and made it a calculated field, value = 2.
One thing though -- when I go to Find in Company field, it's still showing entries that are not hotels.
Any ideas on how to alleviate that?
On what layout are you performing the find? If the layout is based on Hotel details, this should not be possible.
The layout is based on HotelDetails, but the Company field ... which is not a field in HotelDetails table, it's in Companies ... is the field I use to find hotel names.
Have a button running a script that's simply...
Enter Find Mode [Pause]
Show All Records
The Companies field has Auto-Complete enabled, which is where I'm seeing records other than hotels.
Wondering if there's any way to filter for CompanyTypeID = 2 within that script that will still work, considering the Show All Records.
One addendum to the above ... the non-hotel records show in Auto-Complete, but if I choose one the message says "Record Cannot Be Found".
Ok. You have a value list defined for that field (or just an auto-complete setting) and that pulls values from the main table and is not in any way affected by the current layout. Once you have selected a company and perform the find you are telling Filemaker, find all Hotel Details records that link to the specified company. Since there are no Hotel Detail records that link to that company, you get a records not found result.
You need a conditional value list on that field that only lists companies that are linked to a record in the Hotel Details table.
Forum Tutorial: Custom Value List?
Knowledgebase article: http://help.filemaker.com/app/answers/detail/a_id/5833/kw/conditional%20value%20list
Hierarchical Conditional Value lists: Conditional Value List Question
Feel free to post follow up questions here if those links aren't enough to get your value lists working correctly.
Will review all the material you sent.
Am not getting anywhere with conditional value lists.
The script trigger indeed does "filter" everything but CompanyTypeID 2, it's only when I use the "Find" script [or just plain click on Find] that the Companies field shows all records in the auto-complete. I believe this makes sense, as the Find is looking at the entire field in Companies table. Or table occurrence??
Is there any way to "filter" the table occurrence of Companies itself, so that the Companies field in that table occurrence already excludes anything but CompanyTypeID 2 from ever showing up in it?
And if so, would that work for the Find problem??