I am trying to modify a few calculations which removes duplicates. I have tested probably 20 of them I found on various websites and all fail because my need is probably strange but here it is: Remove matching values in one list from another list. Sounds simple but it is a big deal and very complex from what I have read.

The two closest calculations I have found is substituteValues (by Comment) and omitValues (by Geoff Graham), which are in this file but they both fail according to my strange rules and I need to adjust something. I have been trying since last Thursday and I am at the end of my ropes, having tried everything to modify both of them and even create my own and nothing works. I have learned a lot but not enough yet to make it work.

If there are blank lines, they should be removed. And it should not end with a blank line. And all copies of the full value should be removed but not when part is within another. For example, if value to remove is 'angry' it should not remove angry-face or sangry (okay poor example but you get it, right)? This currently works right in both these final calcs. Part of problem I discovered is that GetValue does not see a trailing blank line as a value (PUZZLING) but leading blank lines and middle blank lines are values.

Can anyone help me come up with a solution or suggest modification? I have enclosed a file to make it easy I hope.

Oh please forget the one called killvalues. I was trying to create one on my own. Replace should work but it does not.

test_cr.zip

See if this helps:

http://fmforums.com/forum/topic/52332-speedy-value-filtering-may-07/#entry245860

Hello Comment,

Interesting thread and it makes me feel less idiotic for not figuring it out. Your test file is much better. Thank you for helping me. I ended up using filterValueList by Kieren because it removes all blanks and removes the trailing return. zapValues doesn't remove blanks and leaves trailing return, subtractValues doesn't remove blanks and only removes 'one' of a value I want to remove and ListDifference includes blanks. These calcs are doing what they are probably intended to do; it just wasn't what I needed.

One thing unexpected that one of my values called Shock! in the original list. filterValueList drops the ! from the result even when it is not in the second list. I can modify my list selections so ! is not allowed but it is very odd. Is ! a special character? It seems to work everywhere else.

I always post when I have given up all hope. I need to start posting a bit sooner to save myself some agony. You knew exactly what to suggest.

Nope, it does not seem to be a reserved word.

Do not use any of the following symbols and words in the field name:

•

,(comma), +, -, *, /, ^, &, =, ≠, >, <, ( ), [ ], { }, ", ; (semicolon), : (colon), :: (relational indicator), $ (variable indicator)

•

AND, OR, NOT, XOR, TRUE, FALSE, or the name of any FileMaker Pro function

Maybe it is a lower ascii character. I will keep searching. Oh dunce that I am. This is data in a field so it being reserved character would not matter in data.

Hi David,

! is a word separator and that CF uses LeftWords(). xWords drops word separators from either side. :-)

Hi LaRetta

Well I'll be smitten! I had to try it, not that I didn't believe you but this is huge surprise. I searched for "word separator" here and get no results. Is it called something else?

You shouldn't be paying much attention to the trailing return: all the native xValues() functions leave a trailing return and it can be removed by the calling calculation. Speed, IMHO, is the more significant factor here, since this task requires rather intensive processing (as discussed in the other thread).