Order Summary

[jonbowles84]

Im looking for suggestions on the best way to summarise a list of uniform requests via virtual columns.

I have [Shirt Size], [Pants Size], [Boots Size] in "Staff" table and [Shirts], [Pants], [Boots] in a "Uniform" table, referenced to "Staff Table" by [Full Name].

What would be the best way to summarise the numbers of different size shirts, pants, etc?:

Shirts: 3 x Medium, 1 x XXL, 4 x Small etc...

Thankyou!

[Craig_QREW · Accepted solution]

I would recommend adding a table called total or orders. This would be where we set up the virtual columns. First you will need an ID column, then a article column where you put Shirt Size, Pants Size, Boot Size, lastly you will also need an item column. Fortunately pants shirts and shoes are all measured differently so it could be a column where you have entered the rows Small, Medium, Large, 34x34, 36x34 ... , 6,7,8,9. This would have all options for shirt size, pants size, and shoe size. After words set up your virtual column and make its expression like this: 

IFS(

[article]="Shirt Size", COUNT(SELECT(Staff[Shirt size],[Shirt size]=[_THISROW].[item])),

[article]="Pants Size", COUNT(SELECT(Staff[Pants size],[Shirt size]=[_THISROW].[item]))

[article]="Boots Size", COUNT(SELECT(Staff[Boots size],[Shirt size]=[_THISROW].[item]))

)

This would go through each row looking at what article it represents and what the size is and then count how many times you are going to need one. It does this for all types of clothing and all the sizes you enter. If you have any questions or need any help please let me know, I can record a short video or help in another way if needed. 

Craig

QREW Technologies

IFS() 

[jonbowles84]

Thanks @Craig_QREW ,

Mine is set up slightly different and it was probably a bit vague from my question.
Its a Staff app and each user has their sizes in the "Staff" table which is taken from a list of sizes provided by the shop we will be ordering from.
Then a referenced table is "Uniform" which is a place for staff members to place a request for new items of uniform (which will be required annually). In the "Uniform" table there are [Shirts], [Pants], [Boots] columns for each member to request a number of items.
I then need a VC to summarise the numbers of various sizes of [Shirts], [Pants], [Boots] as they will all be different to present to the shop.
At the moment I have VC's like this:
CONCATENATE([Pants]&" x "&[Full Name].[Pants Size])
I just need a solution to summarise the totals. 🙂 

[Joseph_Seddik]

Hello Jon,

Table Uniforms

uniformID Text - Key column	Item Text - Label column	Type Enum - Text
Shirt-uniqueID1	Shirt basic - S	Shirt
Shirt-uniqueID2	Shirt basic - M	Shirt
Shirt-uniqueID3	Shirt basic - L	Shirt
Shirt-uniqueID4	Shirt special order XS	Shirt
Shirt-uniqueID5	Shirt special order XXL	Shirt
Pant-uniqueID6	Pant regular - M	Pant
Boot-uniqueID7	Boot Industrial type - 44	Boot

Staff Table 

staffID Email - Key column	Name Name - Label column	Other staff related columns
john.doe@company.com	John Doe
jane.doe@company.com	Jane Doe

Orders Table

orderID Text - Key column	Staff Ref  - to Staff Table	Date Date - formula	Other order related columns
orderUniqueID1	jane.doe@company.com
orderUniqueID2	jane.doe@company.com

Orders Details Table

orderDetailsID Text - Key column	orderID Ref - to Ordres Table	uniformID Ref - to Uniform Table	Quantity Number
uniqueID1	orderUniqueID1	Shirt-uniqueID1	3
uniqueID2	orderUniqueID1	Shirt-uniqueID3	1
uniqueID3	orderUniqueID1	Pant-uniqueID6	2
uniqueID4	orderUniqueID1	Boot-uniqueID7	4