Finding Total Counts

[Hemen Babla]

Hello All,

 

My requirement is base on conditions, below is the scenario.

 

 

1) Count total number of Receipt base on The Order Number.

 

2) Count Total Number of Product Qty in the Receipt base on The Ordered Qty. ( Ex : Order Qty is 10, Receipt made 5, Per receipt 2 Qty which come Ordered Qty. )

 

I have tried many was like CASE or IF but cannot get the count/sum, please help me with this.

 

 

I have added 2 img with this note. please let me know to go thru.

 

Hemen Babla.

[Nuos]

I don't know if I follow completely but here are some pointers.  The basic issue I see is that you are trying to add (using SUM or FoundCount) a number of RELATED records.  The Receipt is the "Base" or "Parent".  The product qty should be a related "Child" table.  (Its own table which is related to the Receipts Table on the relationship graph).  If this is how you have it set up (or once you have it set up) you must tell Filemaker to "Look to" all related.  If you tell FileMaker to look at child::product_qty it will return only one record.  To get all records you need to use LIST.  (i.e. List ( child::product_qty ) ).  To get the amount of records in LIST(  ) use ValueCount(  ).  To sum a list of numbers … Well that is more complicated.  Try looking at Custom Functions on the web if you need this.

 

Hope this helps

Marcus

HumaneDevelopment.com

[eos]

I'm not sure I fully understand the relationship between the Orders, LineItems and Receipts tables, but in general …

 

1) Count total number of Receipt base on The Order Number.

2) Count Total Number of Product Qty in the Receipt base on The Ordered Qty. ( Ex : Order Qty is 10, Receipt made 5, Per receipt 2 Qty which come Ordered Qty. )

 

… you should be aware that aggregate functions (Count(), Sum() etc.) can either aggregate fields in the same record, or related records; you cannot use them to aggregate native records (i.e. from the same table as the calculation). Also note that they aggregate fields unconditionally – there is no Count ( Case ( … ) ) 

 

Aggregating native records is what summary fields are for; let them count and sum the fields you want to evaluate, then use them to display the aggregated results for either a found set or (as a related field) a related set of records.

 

In practice: create a summary field “Count of” for your primary key field, and a summary field “Sum of” for your qty field (if I understand this correctly; if not, adapt to your structure).

 

Now you can use those fields either in a report based on the line items table, or put them on your Orders layout to display the summarised values for all related line item records.

 

You could also create calculation fields in the Orders table, using Sum(), Count() etc., but using summary fields is a more versatile method.

 

An even better approach (depending on your display and reporting needs) may be to use normal number fields instead and keep their values up to date by using scripts and triggers when creating, adding and editing line item records.

To get all records you need to use LIST.  (i.e. List ( child::product_qty ) ).  To get the amount of records in LIST(  ) use ValueCount(  ). […] To sum a list of numbers … Well that is more complicated.  Try looking at Custom Functions on the web if you need this.

 

Is this all meant seriously?