1 of 1 people found this helpful
where a location name that contains my Candidate ID
A relationship left to right matches against literal string values
so A to A, B to B, AB to AB
A to AB is not a match
you essentially have multiple values within the right side field
try substituting the spaces with pilcrows, to make the right side field a multiline ; the brackets also need to be removed
eg Substitute( location_id ; [" " ; "<pilcrow character>" ] ; ["(" ; "" ] ; [")" ; "" ] ; )
// pilcrow is a carriage return
this will return WAIRAKEI STORAGE (WKT-021-008-A) as:
which can then be used as a right side multi-line key
the right side field always needs to be indexed (except for cartesians)
Thanks for this.
I tried to use the substitute, however had too many variables and unknowns still.
I have crudely solved my problem with a script that takes each location then performs a find and if there is a reuslt it copies the location ID back to my original table.
It's a bit rough however the power of 'find' has solved the tricky part for me - I think....
Thanks for your reply - I'm sure I'll use substitute somewhere, now I know how it works; it will be useful in tidying some data.