4 Replies Latest reply on Dec 10, 2010 1:17 PM by JulianClayton

    equation to place commas in list properly

    JulianClayton

      Title

      equation to place commas in list properly

      Post

      I have a list that appears in a report that shows which of the items 1-42 have a specific entry.  The only way I can find to write the calculation is to either put a comma after or before every number except the first or the last number.  The problem is if the first or last number aren't included in the list I get a comma hanging out there for no reason and it makes the report look sloppy.  Here is a sample of what I currently use where if #1 isn't in the list the entire list is preceded by a comma.

      //Spares calc

      If ( ${1} = "Spare" and panelSize ≥ 1 ; "1" ; "" ) & 
      If ( ${2} = "Spare" and panelSize ≥ 2 ; ", 2" ; "" ) & 
      If ( ${3} = "Spare" and panelSize ≥ 3 ; ", 3" ; "" ) & 
      If ( ${4} = "Spare" and panelSize ≥ 4 ; ", 4" ; "" ) & 
      Does anyone have a better option?

        • 1. Re: equation to place commas in list properly
          philmodjunk

          Subsitute ( List ( Field1 ; Field2 ; field3 ; field4 ) ; ¶ ; ", " )

          Where I have put "field1", "Field2" is where you would insert your If functions.

          I also wouldn't name my fields 1, 2, 3, 4. Makes for confusing syntax--especially as $Name identifies a variable and is easy to confuse with ${1}.

          • 2. Re: equation to place commas in list properly
            JulianClayton

            I apologize but I'm not quite getting this formula correct, and I can't find the instructions to the parameters anywhere in FM's site.  Here's what I changed the formula to:

            List (If ( ${1} = "Spare" and panelSize ≥ 1 ; "1" ; "" ) ; 

            If ( ${2} = "Spare" and panelSize ≥ 2 ; "2" ; "" ) ; 

            If ( ${3} = "Spare" and panelSize ≥ 3 ; "3" ; "" ) ; 

            If ( ${4} = "Spare" and panelSize ≥ 4 ; "4" ; "" ) ; 

            If ( ${5} = "Spare" and panelSize ≥ 5 ; "5" ; "" ) ; 

            If ( ${6} = "Spare" and panelSize ≥ 6 ; "6" ; "" ) ; 

            If ( ${7} = "Spare" and panelSize ≥ 7 ; "7" ; "" ) ; 

            If ( ${8} = "Spare" and panelSize ≥ 8 ; "8" ; "" ) ; 

            If ( ${9} = "Spare" and panelSize ≥ 9 ; "9" ; "" ) ; 

            If ( ${10} = "Spare" and panelSize ≥ 10 ; "10" ; "" ) ; 

            If ( ${11} = "Spare" and panelSize ≥ 11 ; "11" ; "" ) ; 

            If ( ${12} = "Spare" and panelSize ≥ 12 ; "12" ; "" ) ; 

            If ( ${13} = "Spare" and panelSize ≥ 13 ; "13" ; "" ) ; 

            If ( ${14} = "Spare" and panelSize ≥ 14 ; "14" ; "" ) ; 

            If ( ${15} = "Spare" and panelSize ≥ 15 ; "15" ; "" ) ; 

            If ( ${16} = "Spare" and panelSize ≥ 16 ; "16" ; "" ) ; 

            If ( ${17} = "Spare" and panelSize ≥ 17 ; "17" ; "" ) ; 

            If ( ${18} = "Spare" and panelSize ≥ 18 ; "18" ; "" ) ; 

            If ( ${19} = "Spare" and panelSize ≥ 19 ; "19" ; "" ) ; 

            If ( ${20} = "Spare" and panelSize ≥ 20 ; "20" ; "" ) ; 

            If ( ${21} = "Spare" and panelSize ≥ 21 ; "21" ; "" ) ; 

            If ( ${22} = "Spare" and panelSize ≥ 22 ; "22" ; "" ) ; 

            If ( ${23} = "Spare" and panelSize ≥ 23 ; "23" ; "" ) ; 

            If ( ${24} = "Spare" and panelSize ≥ 24 ; "24" ; "" ) ; 

            If ( ${25} = "Spare" and panelSize ≥ 25 ; "25" ; "" ) ; 

            If ( ${26} = "Spare" and panelSize ≥ 26 ; "26" ; "" ) ; 

            If ( ${27} = "Spare" and panelSize ≥ 27 ; "27" ; "" ) ; 

            If ( ${28} = "Spare" and panelSize ≥ 28 ; "28" ; "" ) ; 

            If ( ${29} = "Spare" and panelSize ≥ 29 ; "29" ; "" ) ; 

            If ( ${30} = "Spare" and panelSize ≥ 30 ; "30" ; "" ) ; 

            If ( ${31} = "Spare" and panelSize ≥ 31 ; "31" ; "" ) ; 

            If ( ${32} = "Spare" and panelSize ≥ 32 ; "32" ; "" ) ; 

            If ( ${33} = "Spare" and panelSize ≥ 33 ; "33" ; "" ) ; 

            If ( ${34} = "Spare" and panelSize ≥ 34 ; "34" ; "" ) ; 

            If ( ${35} = "Spare" and panelSize ≥ 35 ; "35" ; "" ) ; 

            If ( ${36} = "Spare" and panelSize ≥ 36 ; "36" ; "" ) ; 

            If ( ${37} = "Spare" and panelSize ≥ 37 ; "37" ; "" ) ; 

            If ( ${38} = "Spare" and panelSize ≥ 38 ; "38" ; "" ) ; 

            If ( ${39} = "Spare" and panelSize ≥ 39 ; "39" ; "" ) ; 

            If ( ${40} = "Spare" and panelSize ≥ 40 ; "40" ; "" ) ; 

            If ( ${41} = "Spare" and panelSize ≥ 40 ; "41" ; "" ) ; 

            If ( ${42} = "Spare" and panelSize ≥ 42 ; "42" ; "" ) ;  ¶ ;  ", " )

            The result returns each entry on it's own line with no comma.  I tried it with and without the  ¶.

            • 3. Re: equation to place commas in list properly
              philmodjunk

              You left out the substitute function that changes all the returns into commas.

              Subsitute ( List ( your stuff here

               

               

                            ) // paren to close list function
                                 ; ¶ ;  ", " )

              Your database would probably be much easier to work with if your Fields 1 - 42 were replaced with a table of up to 42 records in a related table that you access via a portal.

              • 4. Re: equation to place commas in list properly
                JulianClayton

                This worked perfectly, thank you.  I didn't realize "substitute" was a reference to a function.  I just thought that was your instruction on what to swap out.  As far as the other advice I appreciate it.  I just inherited this database and I'm cleaning up the problems as I find them.