Column Creation = 'If contains'

[nickmarlborough]

Hi,

I have a link table where i have 'Name' and the 'Football Team'

Every name has a 'country ID'  and each football team has the 'country ID' mapped within it. However sometimes this is not the case, where you have a 'Name' mapped to the 'Football Team' but not the 'country ID'

I am trying to create a table where i have the following:

Football Team	Mapped Country ID	Name	Country ID	Country ID Mapped
123	777 , 888 , 999	James	777	(IF Mapped country ID contains Country ID then 'Yes' else 'No')
123	777 , 888 , 999	Alex	888	(IF Mapped country ID contains Country ID then 'Yes' else 'No')
123	777 , 888 , 999	Lauren	999	(IF Mapped country ID contains Country ID then 'Yes' else 'No')
123	777 , 888 , 999	Nick	666	(IF Mapped country ID contains Country ID then 'Yes' else 'No')

When i try and do an IF statement, it does not seem to work especially noticable when filtered. (i.e. i will filter on No but Yes will still show - and i can only assume this is because of the link table)

[Anil_Babu_Samineni]

@nickmarlborough Perhaps this?

If(Subfield([Mapped Country ID], ',')=[Country ID], 'Yes', 'No') as [Country ID Mapped]

[BrunPierre]

I'd do something like this:

If(Wildmatch([Mapped Country ID], '*' & [Country ID] & '*'), 'Yes', 'No') as [Country ID Mapped]

[nickmarlborough]

sadly this didnt work. 

It worked to the extent of each field was Y/N but when filtered on, lets say for 'N' it would still show some 'Y' which i do not know why. 

any idea why this might be?

[Anil_Babu_Samineni]

not sure whom you are replying, anyway did you tried mine? If so, can you please attach the test file?