Manipulating a string of comma separated words

[jeskasper]

Hello all,

I have been pondering this scenario for a while, but I inevitably end up with multiple commas or removing data that should not be removed, so I am asking for your help. :

I have a field, Tags, which contains words or sentences that have been added to the record. "Tags" could look like this:

,Red,Apple,Tastes sweet

Now, I have a second field, "Add/Remove", that allows the user to add a tag to the record. Now, if I enter "Round" and presses a button, then the word "Round" is added to the string, along with an leading comma, so the string now reads:

,Red,Apple,Tastes sweet,Round

The script to do this contains a single "Set field" command with this calculation:

Tags & "," & Add_Remove

This works fine. Now comes the challenge. Lets say I want to remove the word Apple? I have done this so far:

If a button is clicked then the script "Remove" is called. It contains a single "Set field" command with this calc:

Substitute ( Tags; "," & Add_Remove ; "" )

This works fine as long as Apple does not exist twice in the string. If the Tags line was:

,Red,Apple,Apples tastes sweet,Round

then the resulting Tags field would read:

,Reds tastes sweet,Round

And that is not really what I want. :

I have tried adding a comma to what is inserted, so there will always be a comma at the end of the word, but then the second word inserted will start with ",," which I do not want. Also I would really like to be able to have a string nicely formatted, so the initial leading comma and an eventual trailing comma in the entire string gets stripped, to read:

Red,Apple,Apples tastes sweet,Round

instead of:

,Red,Apple,Apples tastes sweet,Round,

Hope someone can help. :)

Edited January 30, 2009 by Guest

[comment]

First, I would suggest you use a return-separated list, since this is a format that Filemaker understands and there are several functions to deal specifically with such lists.

To answer your question: to remove the value "Apple" from ",Red,Apple,Apples tastes sweet,Round":

Substitute ( Tags & ",§" ; [ "," & Add_Remove & "," ; "," ] ; [",§" ; "" ] )

[jeskasper]

Thank you comment. How would I go about creating this scenario using a return separated list?

[comment]

About the same way - except it would be more convenient to keep a trailing separator instead of a leading one (if at all).

[jeskasper]

Yeah, but how do I get the formula right then? How do I describe the line break as seperator? n ?

[comment]

¶

[jeskasper]

So would this be right:

Substitute ( Tags ; [ "¶" & Add_Remove & "¶" ; "¶" ] ; ["¶§" ; "" ] )

or am I missing the point?

[comment]

To remove:

Substitute ( "§¶" & Tags ; [ ¶ & Add_Remove & ¶ ; ¶ ] ; [ "§¶" ; "" ] )

or:

FilterValues ( Tags ; Substitute ( ¶ & Tags & ¶ ; ¶ & Add_Remove & ¶ ; ¶ ) )

To add:

Tags & Add_Remove & ¶

[jeskasper]

Thank you very much for all your help.

What does the paragrah sign (§) mean?

[comment]

It's just a marker, assumed not to appear anywhere in your "real' text. I use it to mark the added leading separator, so that it can be removed at the end without affecting the rest of the list.

[jeskasper]

Hmmm....this piece of content:

Anders Sørensen

Gør dette

Hudelihut

Svend

Hudelihut2

Dette er en test

Dette er også en test

Anders Svendsen

Is converted to:

Anders SørensenGør detteHudelihutHudelihut2Dette er en testDette er også en testAnders Svendsen

if I try to remove "Svend" using the formula:

Substitute ( "¶" & test_val::Tags ; [ ¶ & test_val::Insert & ¶ ; ¶ ] ; [ "¶" ; "" ] )

What am I missing?

[comment]

What do you mean "what am I missing"? You are missing the part you have removed. The part that you asked about, and were told that it guards against removing ALL returns in the final substitution.

[jeskasper]

Oh, I am sorry. I must have misunderstood your explanation earlier. I understood your sentence "It's just a marker, assumed not to appear anywhere in your "real' text" as if it was just something you used to point something out in your example, and that it was not supposed to appear in my formular. I am sorry that i misunderstood, but I am not a native of your tongue nor of FileMakers scripting language. If only FileMaker understood the Linux 'sed' command. :

Well, I think I understand now. Thank you for pointing out my mishap so clearly.