4 Replies Latest reply on Mar 20, 2014 1:40 PM by philmodjunk

    Percentage from 2 differently filtered portals

    AlexE

      Title

      Percentage from 2 differently filtered portals

      Post

           Hi, 

           I have 2 filtered portals displayed on the same layout that are working properly. Is it possible to take each resulting number and calculate the percentage? 

           Thanks, 

            

           Alex

        • 1. Re: Percentage from 2 differently filtered portals
          philmodjunk

               I would guess that you have placed a summary field in the two portals to show a value computed from those related records that pass the filter?

               If so, then no, these are "display only" values. There is no way to access the computed values directly.

               You'll need to use some method to access the same set of records that does not use a filtered portal. This can usually be accomplished with either:

               a) ExecuteSQL() (Needs FileMaker 12 or newer)

               b) A relationship that replicates the filter with match fields

               c) a script that finds the records on another layout using find criteria that replicates the filter criteria, set's a variable to the value of a summary field on that layout and then returns to your current layout where the value in the variable can be accessed for what you need to have happen.

          • 2. Re: Percentage from 2 differently filtered portals
            AlexE

                 Yes, these are Summary Fields. 

                 For the ExecuteSQL(), I am in fact using FM13, I've never used ExecuteSQL and really don't know where to start. All the examples I can find seem very complicated. 

                 Basically I have the following Tables

            SKU_LIST: A table with a list of SKU's 1 record per SKU with various warehouse in stock levels as well as product descriptions

                 eg: 

            SKU   Desc   WRHS1 WRHS2

                 1001   Itm1       4            5

                 1002   Itm2       3            6

            SKU_DATA: A table with a different record for each SKU instance (shows the inventory information per SKU per Store) 

                 eg:

            SKU  STORE  MAX  AV

                 1001   ABC       5      3

                 1001   XYZ       3      0

                 1001   KLM       0     0

                 ...

                 My 2 summary fields on the SKU_DATA table are (MAX_COUNT = COUNT of MAX) and  (MAX_AV_COUNT = COUNT of AV) I just need to know how many stores have a MAX above 0 and how many have an AV above 0. 

                  

                 on SKU_LIST layout, I have the following: 

            SKU     WRHS1    WHRS2    MAX_COUNT    MAX_AV_COUNT     IN_STOCK_%

                 1001       4                5                   2                           1                            50%

                 The way I filter the portal for MAX_COUNT is using the following: (SKU LIST::SKU = SKU DATA::SKU) and (SKU DATA::MAX ≠ 0) as I don't want to count the stores that have 0. I do the same for MAX_AV_COUNT but with (SKU LIST::SKU = SKU DATA::SKU) and (SKU DATA::AV ≠ 0). I've tried creating the calculation to get the % on the SKU_DATA table but it's not calculating the filtered results per SKU. 

                 My MAX_COUNT and MAX_AV_COUNT  portals are displaying the right information using the above filters. Just really unsure how to get this IN_STOCK_% number to calculate. 
            • 3. Re: Percentage from 2 differently filtered portals
              AlexE

                   oh, and I need this to repeat for all the SKU's in my SKU_LIST layout

                   eg: 

                   1001

                   1002

                   1003

                   ...

              • 4. Re: Percentage from 2 differently filtered portals
                philmodjunk

                     ExecuteSQL is not for the faint of heart and requires a basic working knowledge of how to create SQL queries.

                     One option is not to use a layout based on SKU_List. From a Layout Based on SKU_Data, you can find all records for a particular SKU or list of SKUs and use a Creating Filemaker Pro summary reports--Tutorial with sorting that re-orders the sorted groups of records by MAX to put all records with a MAX greater than zero at the top of the report. The script can then Omit the records where the MAX is zero. And this can be a report with just one row for each such SKU even though you have multiple records in SKU Data for each SKU.

                     Another option is to create a script run from scripts and script triggers that computes these summary values and updates number fields in SKU List each time you add, edit, or delete a record in SKU_Data.