Subtotal field - Unit of Measurement Challenge

[Mahmoud]

Hi,

I'm newbie in filemaker, and I have started to build my first new database, but a new challenge floated on the surface that I believe someone can help.

I have a category field "consignment item" that unit of measurement could be different (Kilogram, ton, animal, ...etc). The problem when I want to group or sort my report based on the "consignment item", the subtotal field count every value in the "amount" field whatever the unit of measurement was. 

Any idea how to fix this problem?

Thank you, and I appreciate your help

Edited October 8, 2020 by Mahmoud

[comment]

If the quantities are of the same kind (e.g. weight) and only the units of measurement are different (e.g. kilogram, pound or ton) then you could define a calculation field that converts the quantities to a common unit.

Otherwise you could sub-group the items by unit of measurement and show only subtotals of the same unit.

 

[Mahmoud]

The problem I want to sort my report by multiple fields (month, country, item,..etc), but the subtotal field doesn't calculate the quantities by item properly (please check the screenshot of my layout which may explain better my problem). I would like to count each item separately that have only subtotals of the same unit.  

 

[comment]

10 minutes ago, Mahmoud said:

I would like to count each item separately that have only subtotals of the same unit.  

That is what I suggested. If you have a sorted group that does not use a single unit then don't show that group's subtotal because it is meaningless.  

 

[Mahmoud]

If you check my layout screenshot, you will notice that the amount field counts the amount values cumulatively, and not separately by each item type. 

[comment]

Yes, I saw that. That's because your summary field is placed in a sub-summary by Item(I think?) part. What I suggested is to place it in a sub-summary by unit part.

It also seems your summary field is defined as a running total, which makes no sense to me.

---

If you want to show separate subtotals for each unit type in a higher sub-summary, consider the attached demo.

 

SummarizeMultipleUnitsR.fmp12

[Mahmoud]

So much helpful. Many thanks

I' ve tried to put subtotals for each unit type as you suggested, and it works, but the problem is I have a long list of unit types that can not be placed all in the layout. Is there any way that those fields show the only unit types which are entered in the browsed records? 

 

Edited October 8, 2020 by Mahmoud

[comment]

If you define a summary field as List of [ Unit ], you can use =

GetValue ( UniqueValues ( GetSummary (  Extend ( sListofUnit ) ; Category ) ) ; Get ( CalculationRepetitionNumber ) )

to calculate the labels for the units used in a category.

 

[Mahmoud]

Good idea, but I'm wondering if I can put only subtotals of every item in a separate layout without all these individual records. 

Appreciating your help and inputs

[comment]

Of course you can: duplicate the layout and delete the body part.

 

[Mahmoud]

Yes. Got it. Many thanks