Hello,
I am trying to obtain the sum of specific records without performing a find for those records.
My database tracks the progress of cabinets being produced in three different facilities.
One cabinet = 1 record.
I have a field called : "WOOD SHOP LOCATION" with a drop down of three options: Los Angeles, Chicago, New York
I have another field called: "STATUS" with a drop down of three options: Not Started, In progress, Delivered
My goal is to have ONE layout (a breakdown) that contains fields that calculates the following running totals:
LOS ANGELES:
Not Started: [__X__] - the number that appears in this field is the sum of the found set of (Wood Shop Location: "Los Angeles") and (Status: "Not Started")
In Progress: [__X__] - the number that appears in this field is the sum of the found set of (Wood Shop Location: "Los Angeles") and (Status: "In Progress")
Delivered [__X__] - the number that appears in this field is the sum of the found set of (Wood Shop Location: "Los Angeles") and (Status: "In Progress")
The same type of fields would appear for CHICAGO and NEW YORK.
I know that I can do an OVERALL calculation (of all records) of "Not Started," "Work in Progress" and "Delivered" by using this type the formula below, however, what is the formula for calculating the sum of a specific found set (i.e. without performing the actual find.
Formula to find sums of ALL records in the database:
ValueCount ( FilterValues( List (Office Location ); "Los Angeles"); "In Progress"))
- the result of this is a '1' if the combination appears, '0' if it doesn't. Then, I create another field that SUMs these totals.
Please let me know if you have any ideas. Any thoughts are greatly appreciated.
-Chris
