Using case... "contains" rather than "equals"

[DPMacB]

I've run into a bit of an issue, thanks in advance for any assistance.

I have been supplied data from one company that uses their own value list (keywords/terms) for a field (I have the list), but our company uses a different value list (keywords/terms) for the same type of field. The problem is converting one to the other since Case only looks for exact matches... the filed can have more than one item selected from the value list... I need a way to have Case work on a "contains" level rather than equals.

For example, using Flooring for their field:

Case (Flooring="carpet"; "rug";

Flooring="wood"; "hardwood";

Flooring="marble"; "tile")

Their value list:

carpet

wood

marble

Our value list:

rug

hardwood

tile

If Flooring is only one keyword everything is fine, but if Flooring is multiple keywords Case fails. There are about 100 keywords in each list, so making Case statements for every possible combination would realistically be impossible.

Thoughts/Questions?

Thanks

[Vaughan]

Case works on whatever function you give it to evaluate.

You need to read-up about the PatternCount() and Position() functions.

"There are about 100 keywords in each list, so making Case statements for every possible combination would realistically be impossible."

Sometimes you've just gotta do it. Which is why data conversion is usually a separate item in the quote, it can be very dull and time consuming work.

[The Shadow]

You could try something like:

  Substitute( Flooring & ¶;

      [ "carpet¶", "rug¶" ];

      [ "wood¶", "hardwood¶" ];

      [ "marble¶", "tile¶" ]

  )

That assumes these values were entered via check-boxes, so they will all be newline seperated.

Edited August 9, 2007 by Guest

[DPMacB]

Thanks for the replies.

Shadow,

That's exactly what I was looking for. I had tried Substitute but I was missing the ¶... your right, these were originally entered via checkboxes. My only remaining issue is that I'm getting a few duplicates because a few of their keywords are actually covered by only one keyword on ours.

For example: Marble and Slate on their list is just Tile on ours. This leaves me with Tile Tile wherever they had both Marble and Slate selected.

Thanks again Shadow!

[The Shadow]

Hi,

Assuming you have your value list of all items, let's call it vlAllAttribs.

Let( [ allAttribs = ValueListItems( Get(FileName); "vlAllAttribs" );

    listWithDups = Substitute( Flooring & ¶;

  [ "carpet¶", "rug¶" ];

  [ "wood¶", "hardwood¶" ];

  [ "marble¶", "tile¶" ]

     ) ];

  FilterValues( allAttribs, listWithDups )

)

See, the duplicates you have can be used to filter your clean value-list (with no duplicates), resulting in a clean subset.

Hope this helps.

[DPMacB]

WOW, thanks!

This was the final stumbling block in what was a much larger overall project... your solution has prompted me to rethink a couple of other unrealated projects I have going and how I can clean up a few convoluted scripts.

2007 is a transition year for me, I'm trying to get away from the crutch of just exporting data and hammering away on it with BBEdit and grep... and then reimporting it. Staying inside FileMaker for items like this is something I am really working on.

Your assistance was very helpful.