After almost a year of being on hiatus from FileMaker work, i'm back and it and i must say i've gotten a bit rusty so forgive me if this seems like a no brainer.
Im working on a FileMaker 11 database that was pretty poorly laid out based on data structure and have been tasked with creating a layout to output labels. Seems easy enough but not quite so.
There is a single table that contains a field for a name, then there are several fields for products and sizes: TeeS, TeeM, TeeL, ShortS, ShortM, etc....
The label needs to consist of the name and what items have been ordered, omitting items that have a value of 0 in the following format:
Tees: 1S, 5L
Shorts: 5M, 5L
My thought would be to create 2 calc fiels, one to combine all of the Tee quantities and sizes and one to combine all of the Short quantities and sizes, then add to the label layout as a merge field.
so for example calculation would be Table::TeeS & "S, " & Table:TeeM & "M, " etc...
Problem is if the value is 0 i would have
Tees: 1S, 0M, 5L, etc..
How would i omit the 0 value fields as well as the label? So if size M is 0, it doesnt need to show at all, just 1S, 5L, etc..
This would be easily accomplished had there been a customer table and orders table to where the data was separate (i've tried to talk into a change however they want it to stay the same) but since it is all in one table im a tad stumped.
Any recommendations on the best way to accomplish this in the current form?