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?

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.

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

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?

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, 200718 yr by Guest

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?

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

Have you tried it?

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

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

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

-)

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