Mahmoud Posted October 8, 2020 Share Posted October 8, 2020 (edited) 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 Link to comment Share on other sites More sharing options...
comment Posted October 8, 2020 Share Posted October 8, 2020 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 comment Share on other sites More sharing options...
Mahmoud Posted October 8, 2020 Author Share Posted October 8, 2020 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. Link to comment Share on other sites More sharing options...
comment Posted October 8, 2020 Share Posted October 8, 2020 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 comment Share on other sites More sharing options...
Mahmoud Posted October 8, 2020 Author Share Posted October 8, 2020 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. Link to comment Share on other sites More sharing options...
comment Posted October 8, 2020 Share Posted October 8, 2020 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 1 Link to comment Share on other sites More sharing options...
Mahmoud Posted October 8, 2020 Author Share Posted October 8, 2020 (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 October 8, 2020 by Mahmoud Link to comment Share on other sites More sharing options...
comment Posted October 8, 2020 Share Posted October 8, 2020 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 comment Share on other sites More sharing options...
Mahmoud Posted October 9, 2020 Author Share Posted October 9, 2020 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 comment Share on other sites More sharing options...
comment Posted October 9, 2020 Share Posted October 9, 2020 Of course you can: duplicate the layout and delete the body part. Link to comment Share on other sites More sharing options...
Mahmoud Posted October 10, 2020 Author Share Posted October 10, 2020 Yes. Got it. Many thanks Link to comment Share on other sites More sharing options...
Recommended Posts
This topic is 1294 days old. Please don't post here. Open a new topic instead.
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 accountSign in
Already have an account? Sign in here.
Sign In Now