3 Replies Latest reply on Mar 29, 2011 1:45 PM by philmodjunk

    Overview Screen



      Overview Screen


      Hi All, 

      I am just working through a new stage to my database and I've hit up against a brick wall. 

      I am running customers through a 10 step process. I can enter customers in and using a drop down menu I can assign them a stage, and then in turn move them along through it and set conditional formatting to set various colours and indicators. 

      What I would like now is an overview screen to summarises this. So I have 10 columns representing the stages. At the top of the column is the total number of customers in that section. Below is now a list of their names that can be clicked on to go to their entry. If the customer is at that stage for more than a week (for example) their name goes red. 

      I have basically worked out that I need to do this with a Self-Join relationship between the contacts and another contact instance but I don't know what to use as the self join field and what to use in a portal to show this. 

      One other problem I am coming up againt. I want this overview to be fixed. I don't want it to be a record in a table. I needs to sit above that or be record 1 in to a table a the abilty to create new records disabled. 

      Thanks for all your help. 


        • 1. Re: Overview Screen

          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.

          • 2. Re: Overview Screen

            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?



            • 3. Re: Overview Screen

              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.