Percentage from 2 differently filtered portals
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?
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.
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
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)
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.
oh, and I need this to repeat for all the SKU's in my SKU_LIST layout
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.
Retrieving data ...