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!
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
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 |
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. 