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

# equation to place commas in list properly

### 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

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

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

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

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.