Getsummary problem

[reynir]

Hi

I am trying to use the getsummary function to use a summary in a calculation. The database is managing products produced on board a freezing vessel.

I have two tables:

ProducedProducts with:

id

fk_trip_id

no_of_produced_cases

weight_of_case

total_weight = no_of_procuced_cases * weight_of_case

fk_process_method

fk_type_of_productd

and

Yield with:

id

fk_trip_id

yield_of_products

fk_process_method

fk_type_of_product

Then I have a report with a subsummary showing records from ProducedProducts

Subsummary by fk_type_of_product

Then I have Total of no_of_produced_cases, Total of total_weight and Average of yield_of_product

What I am trying to do is to get the weight of the fish that the vessel got on board by dividing the total of total_weight by the average yield_of_product.

I.e. if the vessel produced 100 kg of a product with a 0.54 yield then the raw material was 100/0.54 or 185.19 kg.

In the report the Total of no_of_produced_cases and total of total_weight shows but the Average of yield_of_products shows blank.

On filemaker formum : http://help.filemaker.com/app/answers/detail/a_id/3614/~/the-getsummary-function-can't-reference-related-fields

it says:

The workaround for the Break Field reference is to use a Calculation Field in the Master file that simply equals the Break Field in the Related file. In the case of the Summary field reference you will need to create a Calculation Field in the Master file that equals the Field in the Related File you want to summarize and then create a Summary Field in the Master file that Summarizes the new Calculation Field.

Another option is to use a Sub-summary report. Sub-summary Parts can Sort on Related Fields.

Can anybody point me to the right direction on this workaround?

Best regards

Reynir.

[comment]

I don't understand your structure. I would have expected the yield to be a field in a table of Products, so that if a vessel produced 100 kg of product ABC, then the raw material would be calculated as =

GetSummary ( sTotalWeight ; ProductID ) / Products::Yield

when the ProducedProducts are sorted by ProductID.

[reynir]

The problem is following:

On a freezing vessel they catch fish in a trawl. The fish comes on board and is not weighed. Then the fish goes into the production line. Then the crew takes sample of 10 fish for each product. This yield is stored in the Yield table. If I could weight the raw material before the production it would be correct to have it in the products table. To have the total catch of raw material I need to divide the producedproducts by the yield to get it.

Consultant, what about the workaround from Filemaker, any comments on that?

Regards,

Reynir.

[comment]

what about the workaround from Filemaker

I don't think the problem here is the same as the one described there.

Then the crew takes sample of 10 fish for each product. This yield is stored in the Yield table.

Does this mean there are 10 related records in the Yield table for each product/trip combination? How then is the yield determined, is it an average or what?

[reynir]

About the yield. Yes there can be many related records for each product/trip combination. The yield for the trip/product is an Average. But the total weight is a Total.

So the raw material is calculated as: total weight / average yield.

Regards,

Reynir.

[comment]

So the raw material is calculated as: total weight / average yield.

So why not do exactly that, i.e. =

GetSummary ( sTotalWeight ; ProductID ) / Average ( Yields::Yield )

Alternatively, you could calculate the net weight of each record in ProducedProducts as =

Weight / Average ( Yields::Yield )

and use a summary to total the result. This has the advantage of working also when the sort order is different.

[reynir]

Ok, the calculation works but does not show the right result. The reason is that I need to filter out the data from the product table from each trip. i.e. 20 cases of G-92 (Product code) with sale weight 26 kg. Total product weight = 2080 kg. Then I need to filter out of the yield table the yield tests made for G-92, i.e. 71,88 %. In your case the average (Yields::Yield) result takes the yield of all yield tests in the table and is with a yield 0,54 which is to low. Hope you understand what I am trying to explain.

Regards

Reynir.

[reynir]

Hi Comment.

I did read a lot of articles on the forums and I managed to fix this problem this way:

In the product table I placed a portal based on fk_trip_id & fk_process_method relationship between the prodcedproducts table and the yield table. ProducedProducts::fk_trip_id&fk_process_method = Yield::fk_trip_id&fk_process_method. Then I added an new field in the producedproducts table showing the average yield of the process_method. Then I can calculate the raw material weight of the produced products: ProducedProducts::total_weight / ProducedProducts::average_yield.

Then I use the total of the raw material weight in the report.

Again,

Thank you for your time and efforts.

Regards,

Reynir

[comment]

I am afraid I have lost you at some point. I thought the relationship to the Yields table was based on matching the ProductID as well as the TripID?