AnsweredAssumed Answered

Calc field combining multiple fields based on value

Question asked by MattLeach on Jul 30, 2014
Latest reply on Jul 30, 2014 by keywords

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:

 

Name

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?

 

Thank You

Outcomes