How you document what stage they are in for each customer record? One field with 10 different values or do you have 10 different fields?
Do you have FileMaker 11 or an older version?
You can create a new table for the overview layout or you can use custom menus or security settings to prohibit the creation of records on the overview layout. If you use a separate table for that layout, you can just set up a script to delete all records in that table everytime the file is opened or closed just to keep an accidentally created records from accumulating. (The extra records will be harmless to your database.)
Either way, you can set up this relationship:
CustomerOverview::AnyField X Customers::AnyField
If you go witha self join CustomerOverview becomes a Table Occurrence of Customers.
You can then set up filtered portals for each colum to list the customers in that stage.
The exact filter expression depends on how you set up your customer table to monitor stages.
If you have one field that stores 1, 2, 3... to identify the stage, a filter expression like this:
Customers::Stage = 1 will only display customers in stage 1.
Now add more portals with filter expressions for each of the other stages.
If you do not have FileMaker 11, this can still be done, but you need a relationship for each stage instead of portal filters.
Hi Phil, I have FMP 11 and I have managed to get this all working. I am using calculations to work out the stages and then using the portal filters to search by that stage number.
One final thing on this. Is there a quick way to calculate the total number of people in each step and display that in the overview?
Due to the portal filtering, you have to get a bit sneaky to do this.
Define a "count of" summary field in the customers table. "Count" any field that you know is never empty such as a primary key field.
Now make an extra copy of each filtered portal (so it keeps the same filter expression), but limit it to one portal row and only put this summary field in that row. You can make the portal borders invisible so that this looks like an regular field and you can position it underneath or just above each portal to show the total counts in each.