HELP!! Filter on RecordID with 'reserved chars' in key

itinkiam

My customer has input customers using reserved characters. EX: Customer no. D&B.

I am using a form to show the Record Links and it works for all Customers except for D&B. When I add a record link with the paperclip, The Record Link is Customer: D&B.

When I try accessing the Customer Link through my form, the filter being set is Customer: D&Bank Account. I've tried all sorts of ways to try to force the filter. I've had no luck.

Please tell me there is a solution short of the customer having to rename his customers?

kine

Can you post the way how you are setting the filter?

ajhvdb

The characters & % ? should not be used in keys. The style sheet/export tool won't work with this also.

itinkiam

//===Here's my code snippet====//
MyRecordRef.GETTABLE(Rec);
RecLink.SETFILTER("Record ID",FORMAT(MyRecordRef.RECORDID));
RecLink.SETRANGE(Company,COMPANYNAME);
MyRecordRef.CLOSE;
FORM.RUN(FORM::DocLinks,RecLink);

///=========
IF you do a message(FORMAT(MyRecordRef.RECORDID)) on the Customer table with Customer no. D&B, it returns "Customer: D&B" as expected.

But when you apply the filter, it sets the filter to "Customer: D&Bank Account".

I know customer's shouldn't use reserved fields in key fields. But, try telling a customer that after the system has allowed them to do it over 50 companies, and 8 years of data collection. Renaming each customer takes over 5 hours. Not a possiblilty in this case.

pruday

Try this,

rec.setfilter(field, '%1', 'D&B');

But if you want set the same filter on form, filter as 'D&B'

kine

Exactly as Pruday wrote... in your way, the value is taken as filter - it means that & is as AND. In a Pruday way, the value is taken as one value, and the & is part of the value...

itinkiam

Thanks Guys. I tried your suggestion and got an error when trying to compile.

Type conversion is not possible because 1 of the operators contains an invalid type.

RecordID := Text
//===
I didn't quite understand what Pruday meant by:

But if you want set the same filter on form, filter as 'D&B'

Can I filter on a Form on the call? I've never seen that. I have only seen filtering the records before calling the form. If I can, that might work. Can you show me the code?
//===
If not, any other ideas? I am desperate. ](*,)

kine

The record ID field is not good for filtering. When I am using Record ID, I have another field of type Text (or code) where I am storing the Record ID in text format (using FORMAT to convert it to text). If you do that, you can filter on Record ID.

Quote from on-line help:

RECORDID
This data type contains the table number and the primary key of a table. You can store a RecordID in the database but you cannot set filters on a RecordID.

girish.joshi

Though there are ways (see above) to set the filter, you really want to advise your client to use a different name in the primary key field.

They are going to find unexpected results throughout the standard product otherwise.