2 Replies Latest reply on Sep 6, 2012 8:47 AM by KhineOo_1

    Conditional Summary Field

    KhineOo_1

      Title

      Conditional Summary Field

      Post

      Hello All,

       

      If I may pose a question to my fellow FMP users Smile

       

      Before I continue, let me give a sample example of what my database consists

       

      Fields:

      Risk Description: Text
      Probability: Number (Ranges from 1 to 5)
      Impact: Number (Ranges from 1 to 5)
      Score: Calculation= Probability * Impact

       

      I would like to create Summary fields to count the risks based on their Probability and Impact and be able to place them in a "heat map" format (example shown below), where there will be 25 Summary fields with the risk counts showing in each box based on their probability and impact ratings.

      As it is right now, I can only think Embarassed of having 25 calculation fields based on the ratings. eg. if (prob=1 and impact = 1; 1;0). And then after, have 25 Summary fields doing a sum on each of the 25 calculation fields. These 25 Summary fields will then be arranged in a 5X5 square format. Is there any shorter way of doing this? Thanks a mil.


       

      Example: If there are 4 risks, one with probability 4 and impact 5, one with probability 5 and impact 4, and two with probability 3 and impact 2, i should get the following display

       

                                       IMPACT

                               1        2        3        4        5

                      1    

                      2     

       Prob.       3                2

                      4                                                1

                      5                                      1

        • 1. Re: Conditional Summary Field
          philmodjunk

          What you are describing is often called a "cross tab" report. Not the easiest thing to set up but it can be done in FileMaker and the small scope of this particular chart makes this one fairly easy to setup. I'm assuming that you have FileMaker 11 or newer for this suggested solution to work:

          I'll name your current table "RiskAssmnt" just to have a name to work with here:

          Define a single summary field as the Total of Score in RiskAssmnt.

          Define a self join relationship.

          In Manage | Database | relationships, make a new table occurrence of RiskAssmnt by clicking it and then clicking the duplicate button (2 green plus signs). You can double click the new occurrence box to get a dialog to appear where you can rename the new occurrence box to be RiskAssmntByProb.

          We have not duplicated a table. Instead, this is a new reference to the same table already present in your database.

          Add it to your relationships like this:

          RiskAssmnt::Probability = RiskAssmntByProb::Probability 

          Create a List View layout based on RiskAssmnt.

          Remove the body layout part and replace it with a sub summary layout part specifying "when sorted by probability".

          Put a portal to RiskAssmntByProb

          Give it this Portal Filter Expression:

          RiskAssmntByProb::Impact = 1

          Select your summary field as the sole field in this portal and specify a one row portal (Initial row: 1, number of rows: 1).

          Use copy and paste or ctrl/option-Drag to make 4 more copies of this one row portal. Double click each copy to open Portal Setup... and change the portal filter expression to:

          RiskAssmntByProb::Impact = 2

          RiskAssmntByProb::Impact = 3

          and so forth.

          When viewing your records on this layout be sure to sort them by Probabiltyor this layout will appear blank.

          • 2. Re: Conditional Summary Field
            KhineOo_1

            Hi Phil,

            Thanks for your speedy reply. I will try this and let you know of the results.