I am working with FileMaker 14.v2 (with a server-client environment for 5 users, small business)
To me and my client it makes sense to see the list of selected/filtered and found records not only in detail view, but at the same time present them in a kind of "list view" inside a portal without having to navigate back and forth between a detail and list view. Because then the user looses focus on the context. I strongly avoid opening secondary windows especially on our Windows only platform since the application window then will fall in to pieces of windows (FileMaker has not fixed this since 30 years.)
Either all or filtered sets of records are visible in the list in the portal and the user can either navigate through the list to select the desired record, of move with going next, previous, or searching using any method.
This means updating the portal whenever the user is on a detail record, and also hilite the associated portal row.
(Using ExecuteSQL is fun, but requires FileMaker to be aware of the found set from the SQL query and filter accordingly the found set which adds complexity. Also everything must be performance improved since this must also work on the WAN and LAN from server. Here virtual lists would have to be used, and the selection must also apply to FileMaker selecting. It would just be great if there was a portal which does not depend on a relationship, but on a simply list of text values and kind of "virtual" fields defined outside of table definitions but accessible. It should not be too difficult for FileMaker developers to implement.)
Probably my solution is not a very good enough one, and that is why I am asking. Maybe there are better ways?
Basically what I do:
- Use a summary field "List of" which collects a return-delimited list of id numbers (here project numbers in text form) on the fly.
- Copy this list to a global field which has a relationship with the base table using these ids. So, the list of project ids contained in this global field relates to project ids in the base table and filters those records which are present in the global field.
This list of ids always represents the found set. It seems to be a fast way. When made visible in a portal, the user can navigate through the portal and, using navigation to a related record will arrive at the desired record of the base project table.
Problem 1: The selection of the portal row does not maintain the selected record. There is no visible synchrony between current record and the selected portal row. What I do is copying the project id present in the portal row to a mark field in the base table. This establishes a condition by which conditional formatting of a field or button in the portal row is triggered. At the same time I must ensure that there are no other marks left in the mark field of the base project table before I mark the current record (otherwise there will be multiple visible rows conditionally hilited), and therefore I am first deleting any left ones overwriting them with empty using "Replace Field Contents" script step.
How to find the portal row associated with the current record? The only safe method I found was freezing the window (for speed reasons) and looping through the portal rows comparing the project id of the current record with the project id of the related same record visible in the portal row. Once found, I have to activate the project row selecting a field there and then I get the portal row number of the selected portal row with Get (ActivePortalRowNumber).
Problem 2: The user can find this current record (or records) also using the standard navigation controls FileMaker provides, or use standard searching. So, I must ensure that the portal list will also update in such situations. The one way doing this would be through triggers such as "on record load" using Layout triggers. Problem: Such triggers always fire, and implementing my own controlled find/filter functions will be called also from this trigger which resulted in recursive calls in my case.
So how to make sure that a portal row is hilited synchronizing ALWAYS with the current record? How to make the portal row aware that it is pointing at the current record of the detail view in ANY given circumstances and be hilited?
Problem 3: It is not enough that the portal row it is hilited for the current record. It should also be visible. Hiliting is of no value when it is not seen. The portal row pointing to the current record may sit at the far end of the portal list. The visibility is a problem when moving through records using standard ways. And trying using layout triggers would slow down performance very much. Implementing my own search, it is controlled more or less. If one record is found then only one record is present and it is easy. If multiple records are found I try to hilite the first row in the portal and go to the associated record. Otherwise using my own navigation buttons for moving through found sets allows to at least make the pointing record visible with some scripting.
Has anybody done this using another method? What method would be the best or better?
Here is a picture of what it looks like for a filtered set of records in my test application.