How to do cascading Replace in a set expression

[#1]

I have a set expression that creates a union of two states.  The expression must also cover the situation where the data contains a space, a left parentheses, and a right parenthesis.  I can make the expression work with a space, but am having trouble extending it. 

Below is an example of what is working:

  Replace('(' & Concat({$ + S4} Product, '|') & ')','  ','?')

In this case, I am creating a successful union between the default state, and S4, and inserting a pipe character between the entries.  I am also replacing any spaces that are found with a question mark.  Altogether, this expression takes selections made in one state (S4), and adds them to selections already in place in another state ($).  This works perfectly.

My data also contains a right and left parenthesis.  When I try to extend this expression, I am entering:

Replace(Replace(Replace('(' & Concat({$ + S4} Product, '|') & ')','  ','?'),'(','?'),')','?')

The syntax editor indicates the expression is OK, but it does not work.  Specifically, it no longer moves selections between the states.

As a debugging step, I've tried putting the below example into a text box.  It works perfectly.

Replace(Replace(Replace(Product,' ','?'),'(','?'),')','?')

When I do this, I get the product names, with spaces and parenthesis replaced by question marks.

Anybody have any ideas why the middle example does not work?

gary

[JonnyPoole]

I haven't test it but i noticed you are adding '(' and ')' in the inner string but then trying to replace ')' and '('. 

Would this change work for you ?

change

Replace(Replace(Replace('(' & Concat({$ + S4} Product, '|') & ')','  ','?'),'(','?'),')','?')

to:

'(' & Replace(Replace(Replace(Concat({$ + S4} Product, '|'),'  ','?'),'(','?'),')','?') & ')'

[kristoferahlin]

You are replacing also the left and right parenthesis within the Concat() statement

It might be possible to add $() to first calculate the Concat()-expression, prior to replacing the parenthesis... Not sure about the syntax tho.

Alternatively, replace space and parenthesis in Product prior to concatenating the states. Would that be possible?

[#4]

I tried your change, and it seems to still have the same problem:  if I add a Product with a name like "Orange", the expression works great.  If the name is "Orange Red" the add (union) does not occur, and if the Product has a name like "Orange (all)" it likewise doesn't work.

The full expression this is used in is:

   IF(GetSelectedCount(Product)>0,

          Replace('(' & Concat({$ + S4} Product, '|') & ')','  ','?'),

          Replace('(' & Concat({S4} Product, '|') & ')','  ','?')

     )

Above is the "original" form of the expression, which works OK with a space in the Product name.  This expression does not work when a right or left parenthesis is present, hence my editing to the alternative form that I wrote about, in my first post. 

(Note:  the IF statement covers the first add, when there is nothing to move.  After that, we are moving a new entry into the set that has existing entries.)

gary

[kristoferahlin]

What happens if you try Replace('(' & Concat({$ + S4} Replace(Replace(Product,'(','?'),')','?'), '|') & ')','  ','?')  ?

[#6]

I've tried Kristofer's idea, and still have the problem.  I can add Products having names of all one word.  Products that have a space, and products that have parenthesis, are ignored.

The original expression (my first example) does work with spaces, but does not work with parenthesis.  Both alternatives (Jonathan's and Kristofer's) result in both spaces and parenthesis not working.

Thanks for the suggestions, any more ideas?

gary

[JonnyPoole]

I think there will be a way to fix it. 

Keep in mind that a search string of values , where the values have spaces need single quotes around all values like this (not just at the ends). So i think you need to modify the delimitted for concat() accordingly

('United States'|'Canada'|'United Kingdom')

Not sure about ')' and '(' , but i would suggest testing a sample string directly in the search option of a list box to play with it at first. 

Give it a shot and see how both work out... i'll check in later.

[#8]

I've created an example file for everyone to play with.

This is actually a pretty nice function and, if we can get it working, it might be useful for others.

The idea is that the upper box (Product) contains items that you want to add to the filter.  You can pre select an item, or items, then click the "Add" button.  The selection(s) are added.  Then you can continue by adding more items in the Products box, and press "Add" again.  Items that were selected will then be added to the items selected earlier.  In the full implementation (not this example) I also have a "Remove" button that takes items off the list.

You can see the problem in the example file.  For example:

1. Click "Broccoli", then click "Add".  Broccoli will be added to the filter, and this can be seen in the lower "Product Results" table.
2. Next, click "Collar", then click "Add".  Collar will be added to the results.
3. Next, click "SnowCo (ALL)".  This will NOT be added.

You can do this any way you want, but the product with he parenthesis will not add.  Note that I've got the space condition covered.  This can be demonstrated by clicking "Snow Flake"

gary

[JonnyPoole · Accepted solution]

Here it is with () working too....  not perfect since we are using a wildcard character '?' to replace space, ( and ) which could technically bring back the wrong results.

[#10]

Yes!!  Much dancing around my office ....

Thank you!!

gary