Jump to content
Salesforce and other SMB Solutions are coming soon. ×

Conditionally filtered portals


Recommended Posts

I built a filtering portal in FMP 6 in an application that tracks support calls. The file is called ‘Calls’ and uses a related file called ‘Docs’ (our client base). In ‘Calls’ you put a search criteria in a global field and the portal fed by the relationship of the global field’s value to a calculated field in ‘Docs’ (based on the fields I want searched) fills up accordingly. It works wonderfully. However some users do not like that when they put in a search string it brings up all matches based on all of the fields being calculated at the tail end of the relationship. They want to limit the search to only one field (e.g.: last name)


So I did this: A global field in ‘Calls’ is fed by a value list of all of the searchable field names in ‘Docs’. If the user does not touch the drop down list box, it runs the default filter based on ALL the searchable fields. If the user however chooses one of the fields in the drop down list box, then the value is updated to the ‘Docs’ file. The calculation field in ‘Docs’ which is at the tail end of the relationship has a case function in it based on the user’s choice of search field. Basically if no field is picked to search under, it runs the default calculation, however if a specific field is chosen, then it runs only the part of the default calculation based on this field (e.g.: if last name is chosen then only the left(lastname,1) & “paragraph symbol” & left(lastname,2) & etc… will be run.


When a user does click on a specific field in the drop down list box, the portal remains empty. When they don’t specify a field, it still remains blank. If I remove the Case function, then it runs the default calculation and it fills up like it used to with all fields matching the search string. So I’m back to scratching my head… Am I doing something wrong?

Link to comment
Share on other sites


Yes, you are doing something wrong. crazy.gif


The field in which you are selecting the name of the field to filter on is presumably a global field and is presumably also located in the file that the portal will be viewed in. Either of these facts will resulkt in the calc (in the file where the data resides) being *unstored* if it references the file name field. Since unstored fields cannot be indexed (and relationships depend on the index of the key field in the referenced file) the relationship will fail under these conditions.


To make filtering of the type you have in mind, you will need a different approach.


I suggest that you assemble predetermined key combinations (which include all the combinations you will wish to search on) in the data file. Being predetermined, the structure of these values will not alter as the search criteria change, so there will be no problem storing and indexing them so that they can be used as the basis for the portal relationship.


Once this is in place, you will need to create a matching unstored calc in the file where the portal is displayed, which draws on the values in the two (or more) search criteria fields to produce a composite string which will make an appropriate match with one of the values in the stored key field in the data file.


In case you have no idea what I am talking about smirk.gif, I have created a very quick demo which shows how a filtering system of this type might be constructed in FileMaker 5 and 6 format (see attached). You may still have no idea what I'm talking about, but at least you will be able to see it in action... cool.gif

Link to comment
Share on other sites

Thanks for the information. Your explanation left me a bit puzzled, but the demo definitely showed exactly what you were talking about. Works like a charm. Thanks again!

Link to comment
Share on other sites


This topic is now archived and is closed to further replies.

  • Create New...

Important Information

Terms of Use