4 Replies Latest reply on Feb 15, 2010 6:22 AM by comment_1

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

### Title

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

### Post

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 of formula:

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

• ###### 1. Re: Help with calculation: Sum of a found set without performing the find

The easiest - and fastest - way to do this is to find the records you want to include, then show them summarized by location and by status (you can delete the body part of the layout to show only summary values).

Alternatively, you could use a "grid" table of 9 records (3 locations x 3 stages) and summarize over a relationship to the Cabinets table.

chris_vfx_la wrote:

ValueCount ( FilterValues( List (Office Location ); "Los Angeles"); "In Progress"))

I don't think that's a valid expression.

• ###### 2. Re: Help with calculation: Sum of a found set without performing the find

-----------

"The easiest - and fastest - way to do this is to find the records you want to include, then show them summarized by location and by status (you can delete the body part of the layout to show only summary values)."

If I did this, won't the numbers in the other fields on my "subtotals" layout no longer display their accurate information.  The idea is to have one layout with contains fields that displays the results of multiple finds simultaneously.

-------------
"Alternatively, you could use a "grid" table of 9 records (3 locations x 3 stages) and summarize over a relationship to the Cabinets table."

How do I setup a 'gird' table?  I'm presumming that is different than 'viewing' the layout in a table.

Thank you again for your assitance with this problem.  I'm pretty stumpped on this one.

-Chris

P.S.

ValueCount ( FilterValues( List (Office Location ); "Los Angeles"); "In Progress"))  is definitely a valid experession.

Give it a shot (replacing the field names and values with those that match your database).  It's very similiar to the 'countif' funtion in excel.

• ###### 3. Re: Help with calculation: Sum of a found set without performing the find

...actually, I just figured it out using this equation:

If ( Location  = "Los Angeles" ; ( FilterValues( List (Status); "In Progress")))
The value of this should be listed a "text."  The result is a ? it if occurs, and blank if it doesn't

Then, I created another field which adds all the occurences ("?") of this field and that sum is what becomes displayed.

Works like a charm!

-Chris

• ###### 4. Re: Help with calculation: Sum of a found set without performing the find

chris_vfx_la wrote:
ValueCount ( FilterValues( List (Office Location ); "Los Angeles"); "In Progress"))  is definitely a valid experession.

I'm afraid not: to begin with, there are 3 open parentheses and 4 closing ones.

chris_vfx_la wrote:

...actually, I just figured it out using this equation:

If ( Location  = "Los Angeles" ; ( FilterValues( List (Status); "In Progress")))

I don't see how this can work, but if you're happy with it...