Related File, finding from multiple

[WC Howard]

I've been using a database of cross reference numbers and I've just discovered a problem... (This is with a 5.5/6 database)

I sometimes get a list of requests for our products with someone else's numbers on them (happens a lot). For a short list, we usually just cross the numbers one by one doing a simple find on the manf number's field, but for longer lists I'll turn the spreadsheets into FM files and then relate them to cross to our part numbers.

What I discovered today is that sometimes the manf numbers may contain more than one number for the number we carry. In cases like these, I separate them with a comma and a space. This works fine for doing single item finds, like what we do over the phone or on our webstore. But when I relate the files, only the first number in the field is matched, and the others ignored, and a lot of records are missed.

Is there a function that would allow me to relate these files better together?

[mr_vodka]

It may be advisable to have another file that relates your ProductID to their multiple IDs rather than separating them in one field with a comma and a space.

[comment]

If I understand this correctly, it will work if the values are separated by a carriage return.

[WC Howard]

Thanks! That's the short and quick that will work...

Only problem now is I've got over 500 records with this system of comma - space and that's just in one field... I tried to find & replace with a pilcrow sign (¶, or carriage return symbol), but now I've got a field that has a bunch of pilcrow signs...

Is there a way to do a find and replace with real carriage returns?

[comment]

Replace Field Contents is better for this. If you need to do this repeatedly, make yourself a script with this step, replacing with a calculation:

Substitute ( Importedfield , ", " , "¶" )

EDIT:

As always, a warning when using this step: be careful, as this works on the entire found set, and there's no undo.

Edited January 26, 2007 by Guest

[WC Howard]

But the pilcrow is not what I need, it's the actual carriage return...

Is there a way to do this? Can 8.5 do this?

[comment]

But the pilcrow is not what I need, it's the actual carriage return...

Have you tried it?

[mr_vodka]

"¶" will return a carriage return. To get the literal Pilcrow symbol, you would need to escape it out "¶".

[Lee Smith]

They are using v5, so I don't think that they can escape anything.

I'm not sure what they are having a problem with, comment has suggested the using ¶, maybe they need to capture whatever is actually there, and use that instead as their find string.

HTH

Lee

[mr_vodka]

Lee, Of course. How quickly we forget and get spoiled with the abilities post FM6; the escape character being one of them...

-)

[Bobfmf]

Are there any rules / guidelines / documentation as to Find/Replace vs. the Perform Find/Replace script step, in terms of escaping requirements? What I mean is that in the former, I can paste a pilcrow into a field in Find mode, and it finds pilcrows, whereas in the script step, I must enter:

"¶"

... with the quotes and escape character for it to work.

Bob