Help with calculation: Sum of a found set without performing the find

February 15, 201015 yr

Newbies

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

February 15, 201015 yr

What you need is to create a series of self relationships that cover the possibities. eg:

Chicago, Unfinished

Chicago, Delivered

etc.

Then use a calculated field for each relationship to do a count through the relation.

Easiest way is probably to have a series of indexed calculated fields, one for each location and one for each status eg:

c_Chicago="Chicago"

c_Unfinished="Unfinished"

Then use these as the "Left Side"keys of multi predicate relationships to the actual records. Doing this you chould be able to view overall status from any record in the table for all possibilities using calculated aggregate fields.

HTH

Dave