6 Replies Latest reply on Jul 30, 2014 2:07 PM by keywords

    Calc field combining multiple fields based on value

    MattLeach

      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

        • 1. Re: Calc field combining multiple fields based on value
          ninja

          Howdy,

           

          You could simply use an If or Case statement (repeated over and over) within the calc...something like:

           

          CalcField =

           

          "Name" & Carriage Return &

           

          If (TeeS ; TeeS & "S" ; "") & If (TeeM ; ", " & TeeM & "M"  ; "" ) & If (TeeL ; ", " & TeeL & "L" ; "" ) ......

           

          and assemble your list checking each value and including it only if the value exists.  Think through how you want the punctuation as well, and include it in the calc to make a well organized list.

          1 of 1 people found this helpful
          • 2. Re: Calc field combining multiple fields based on value
            MattLeach

            I was actually just working through it and found a solution using the Let function which would basically do the same thing without multiple if statements and was able to produce the result as well:

             

            Let (

            [

            tym = Case ( IsEmpty ( TeeYM ); "" ; TeeYM & "YM");

            tyl = Case ( IsEmpty ( TeeYL ); "" ; TeeYL & "YL");

            sl = Case ( IsEmpty ( TeeS ); "" ; TeeS & "YS") ] ;

             

            tym & " " & tyl & " " & sl

             

            )

             

            Thanks for the follow up and answer, pretty sure i was overthinking it as its been a while since ive had to use calcs lol.

            • 3. Re: Calc field combining multiple fields based on value
              keywords

              It sounds like the single table structure might be making this more complicated than it needs to be. Your probably should fix the data structure and your immediate labels problem would be resolved in the process. My assumption would be that you need to separate Orders and Products into separate tables and have an Order Items join table between them. Then your label, perhaps a calc in the order table, would only contain product details relevant to each order.

              • 4. Re: Calc field combining multiple fields based on value
                erolst

                Or start with a list, then turn that into a string; you only need to define the desired punctuation in a single spot, and also let FM take care of proper concatenating.

                 

                This …

                If (TeeS ; TeeS & "S" ; "") & If (TeeM ; ", " & TeeM & "M"  ; "" ) & If (TeeL ; ", " & TeeL & "L" ; "" )

                 

                … would produce a string that starts with ", 3M …" if you had 3 TeeM, but no TeeS.

                 

                The following only adds punctuation for existing lines.

                 

                Let (

                  myList = List (

                     Case ( TeeS ; TeeS & "S" ) ;

                    Case ( TeeM ; TeeM & "M" ) ;

                    Case ( TeeL ;  TeeL & "L" ) // etc …

                     ) ;

                  Substitute ( myList ; ¶ ; ", " )

                )

                 

                Note that you don't need to provide an empty default value for Case() and If(); also, you can always use Case() and simply forget that If() exists …

                1 of 1 people found this helpful
                • 5. Re: Calc field combining multiple fields based on value
                  MattLeach

                  I would love to but unfortunately the file owner is reluctant to let me set it up properly.

                  • 6. Re: Calc field combining multiple fields based on value
                    keywords

                    Ugh! You can either (1) live with it, or (2) try to convince them otherwise, then, if necessary, revert to (1). Still, other posts look like they are getting you moving. All the best.