Jump to content

Subtotal field - Unit of Measurement Challenge


Recommended Posts

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 by Mahmoud
Link to post
Share on other sites

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.

 

Link to post
Share on other sites

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.  

 

image.thumb.png.f8d567c838633913ccaa4d692bca8ba1.png

Link to post
Share on other sites
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.  

 

Link to post
Share on other sites

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

  • Like 1
Link to post
Share on other sites
Posted (edited)

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 by Mahmoud
Link to post
Share on other sites

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.

 

Link to post
Share on other sites

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

Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.