ValueList with exact matching on delimited data

[Gyrodude]

I know there must be a simple way to do this, but I'm just not seeing it and I was wondering if anyone could help me.

 

I have a source field that will contain known values in any combination. Example: VANS = "VAN,53VN,VANL,VANV". Then I have a checkbox field that I'm attempting to match against the first, containing all the various types of vans (VanTypes). I need to get an exact match of one of the values without matching partials.

 

For instance, if VANS = "VAN,53VN, 48VN, REFR" and VanTypes has "REFR" checked, then I expect to get a match count. If VanTypes has "VANL" checked, then no match and no partial match to "VAN". The source (VANS) field is imported and can have any number of van types listed, usually from 1 to 4, but always using the "," delimiter if more than one.

 

I've tried different functions, but I either get a partial match (i.e. with PatternCount) or they don't see the delimiter and tries to match the entire string.

 

Thanks,

[comment]

I am afraid your question is not entirely clear. Are these two fields in the same table? Where do the values for the value list come from? And what exactly does "match count" mean in this context?

 

The solution will probably require converting the contents of the source field to a return-separated list (using the Substitute() function), then using FilterValues() to compare the two lists (a checkbox field is a return-separated list).

[milanm]

Could you please explain this a bit more comment, would be of big help to me to. I need to do something similar - to compare one field with a chosen word (ex. "smile") to another field with return-separated list of words ( ex ball¶baloon¶smile¶hair) in a way that it produces the result if any value of the return-separated field matches the chosen value.

I am trying something in the way you are suggesting with FilterValues and Substitute and i think i am getting there, but would appreciate a bit more on this.

[eos]

I am trying something in the way you are suggesting with FilterValues and Substitute and i think i am getting there, but would appreciate a bit more on this.

 

Use FilterValues() – e.g. not IsEmpty ( FilterValues ( "smile" ; "ball¶balloon¶smile¶hair" ) ) will return True; not IsEmpty ( FilterValues ( "smile" ; "ball¶balloon¶smiley¶hair" ) ) will return False.

 

 

If necessary (and suggested by comment), convert your string to a CR-delimited list first (or store it this way in the first place); so in the OPs case: 

not IsEmpty ( FilterValues ( VANTYPE ; Substitute ( VANS  ; "," ; ¶ ) ) )

If you compare two CR-delimited lists, then 

ValueCount ( FilterValues ( VANTYPE ; Substitute ( VANS  ; "," ; ¶ ) ) )

will return what I think the OP means by “match count”, assuming that the field VANTYPE already holds a CR-delimited list; the result can of course also be interpreted as a Boolean value.

[Gyrodude]

Thanks Guys, your comments were a great help! I'm sorry if I was somewhat vague, EOS, your example worked perfectly. I am working with a single table and two fields, trying to compare the contents when one field has comma delimited data and the second filed has CR delimited data.
 
So the "Compare two CR-delimited lists" worked perfectly with no partial matches.

 

Thanks again, and thanks for the quick responses!