List function and pilcrows

[Rainy Pass]

Can FM convert a return delimited list into values separated by pilcrows? Using the list function I obtained a field with a list of values:

BLB

BLG

BLK...

I want to use this field/list in the a Filter function. Can I use FM to convert this list to BLB¶BLG¶BLK so it can be used in a Filter calculation?

[LaurenKuhlman]

It is separated by pilcrows actually. You just don't see them.

BLB

BLG

BLK = BLB¶BLG¶BLK

A note about the list function. There is no pilcrow on the end. I sometimes use the list function to build lists of keys and such and when you remove a value make sure to remove the value and its pilcrow. In this way it is important to throw one on the end of a list function.

List ( Field ) & ¶

Hope this helps.

And another note... You may be looking for FilterValues instead of Filter. I am not sure of your application but I use this a lot with List ( ).

Edited June 24, 2009 by Guest

[Rainy Pass]

I must be missing something.

Typing this list into a FilterValues function does not seem to work.

BLB

BLG

BLK

However this does

BLB¶BLG¶BLK

They don't work the same. If the values are not on one "line" in the calculation, the function does not work.

When testavailSizes contains

32

34

36

38

40

42

44

This calc returns only "40", not 30...40.

FilterValues ( "

30

32¶

34¶

36¶

38¶40¶SML¶MED¶LRG¶XLG" ;testavailSizes)

Ultimately, I want to use a field value for "textToFilter"

[mr_vodka]

Indeed you are.

If you are explicitly referring to it in the calculation itself, it will strip it of the returns upon evaluation.

Therefore, if you put a carriage return in the middle of text it wont work. You have to use the pilcrow.

However, if you are referring to a field or result of a function such as List (), it will work.

Therefore, your calc could simply be:

FilterValues ( textToFilter; testavailSizes )

where both fields are lists.

[LaurenKuhlman]

Ah. Yes you are correct. If this was displayed a calculation result:

31

32

34 it would be equal to 31¶32¶34 "behind the scenes"

Inside a calculation dialog, FileMaker doesn't recognize line breaks the same way it recognizes a pilcrow.

I do not understand what it is your trying to do however. Can you explain and then I can suggest some code?

[comment]

There is a difference between a carriage return typed into a field, and one typed into a calculation formula. A carriage return entered into a text constant of a formula is interpreted as a space*.

Example:

Let ( [

values = "a

b"

] ;

values = "a b"

)

returns TRUE.

---

(*) On a Mac, at least - YMMV.

Edited June 24, 2009 by Guest

[mr_vodka]

True on Windows too.

[Rainy Pass]

This is what I am trying to accomplish. If this is not over simplified...

Table A contains fields: Style, Color, Size.

There are many records for one Style, each with different color/size combinations.

Table B contains fields: Style, Description.

Each record has one style(unique), one description.

I want to show all the colors for each style (presumably in Table . The colors need to be sorted and not repeat.

Any help is greatly appreciated.

[LaurenKuhlman]

Draw a relationship between TableA::Style and Table B::Style Then, create a calc field in Table B with this calculation:

List ( TableA::Color )

In the attached sample file I replace the pilcrows with commas. Not sure how you want it displayed. If you want the available colors to be a value list, let me know and I can show you how to do that too.

I am not sure how you want these sorted but the sorting should be applied to the relationship between the two table occurrences.

ListFunction.fp7.zip

Edited June 24, 2009 by Guest

[comment]

If you do not want colors to repeat, use a value list - see this recent thread:

http://www.fmforums.com/forum/showtopic.php?tid/209744/

Or, preferably, use a third table for colors, and take the list from there.

[Rainy Pass]

Thanks. But,it falls apart when multiple sizes on one color are included. File attached.

ListFunction2.fp7.zip

[Rainy Pass]

Using ValueListItems did the trick.

Thanks to everyone for the help!