Collapsing list

[Bridge]

I have recently started using functions. I was wondering if someone can point me in the right direction for a calculation I would like to use. I am wanting to take a data in a field that is separated by commas and added dashes to collapse the data.

This is what the data currently looks like:

210,211,213,214,376,377,400,501,502,503

I would like the calculation to produce:

210-214,376-377,400,501-503

Since sometimes the data separated by commas can be 50plus numbers in consecutive order, than a jump in the data, I am looking for a way to condense the data.

Since I have been having a lot of success figuring the functions, I think I have drawn a complete blank on this one. I am thinking, should I explore "range" functions?

Best,

brigitte

[comment]

This requires a recursive calculation:

http://www.briandunning.com/cf/734

[Bridge]

Hi,

Thank you for the link to the Brian Dunning site. I found some very useful calculations / topics. But I did try the CollapseList and I am most definitely not getting it. Could it be because all of my numbers are in one field, not a value list? I also found another calculation on the same site called Pagenumbers_condensed_with_dashes. I played around with this one too, but I didn't understand in the calculation "flag". So I was not able to get through that one too.

Still working on it - Bridge

[comment]

Could it be because all of my numbers are in one field

That shouldn't matter - as long as they are return-separated. If you start with a comma-separated list, you must use Substitute() before calling the custom function.

[Bridge]

Hi,

The field with all the numbers is return separated (between each number). I tried it again and only got the very 1st number to appear. But it is certainly since I removed the

"CollapseList ( nextList )" at the end of the function, since my filemaker 8 said it was an unknown function and I was just wanting to see if I could exit the calculation window. Am I in too old a version of FMP?

Still a snail - Bridge

[comment]

Do you know how to install a custom function?

http://www.filemaker.com/help/html/fmpa_custom.23.5.html#1027698

[Bridge]

Hi,

Thank you for pointing me to the direction of custom functions (totally new to me). There are steps (lack of understanding) that I am missing. I am going to post what I have done, even though I suspect it will be totally laughable to those whose understand FMP.

I created the custom function named "CollapseList" and added the function parameter called NextList. I lift the calculation box empty. And said "OK" to close the window. Then I went to my define database to a my "collapse numbers" field that I created and pasted the custom function in there and replaced the "listofvalues" to my return separated field of numbers called "Sum numbers". At the end of the calculation, I replaced the "CollapseList (NextList)" to "CollapseList (sum numbers)".

What appears in my field "Collapse Numbers" is only the very 1st number in the return separated list.

Totally confused.

Bridge

[comment]

Go to the custom function's definition and change the parameter's name to "listOfValues". Then copy the function's formula from the web page (note the 'Select All' button) and paste it into the formula box.

Then change your calculation field to =

CollapseList (sum numbers)

[Bridge]

Hi,

Wow. Thank you. It totally works now. You've just given me a window into a new area of Filemaker that I am most definitely going to explore more.

Thank you!!!

Bridge