Relationship with spaces in field values

[pjulian]

HI all, hoping somebody can offer some advice on Filemaker Advanced 14

I have a self-join table, so two occurrences of the same table, which have customer names in a field which I need to compare.

The problem I have is that when the customer names have spaces in them the join fails if the source value is just one word of the destination values.

For example if I have a customer name of "Fred" and I want to setup the relationship for that, I get matches on "Fred Flintstone", "Fred Bloggs" and "Fred" when using the GoToRelatedRecord script step.

Normally in a find I simply use the == operator which is all good but as it's a relationship I don't have that option, unfortunately I need to use this method for what I need to do.

Am I missing something obvious here or is there a way around this ?

Any suggestions would be appreciated.

 

Thanks

Paul

[comment]

48 minutes ago, pjulian said:

or example if I have a customer name of "Fred" and I want to setup the relationship for that, I get matches on "Fred Flintstone", "Fred Bloggs" and "Fred" when using the GoToRelatedRecord script step.

Try defining a calculation field (result is Text)=

Substitute ( CustomerName ; " " ; ¶ )

and make that your matchfield instead of CustomerName.

Note that this assumes that the individual name components are separated only by spaces; "Fred" will not match "Fred, Bloggs".

 

48 minutes ago, pjulian said:

Normally in a find I simply use the == operator which is all good

I think you got it backwards: a "==" find is the exact equivalent of the relationship you have now.

Edited February 29, 2016 by comment