Reading table based on part value of field

I have a table with primary key RFQNo (code 20). Possible values, for example, of RFQNo are: RFQ0100 RFQ0100-001 RFQ0100-002 RFQ0100-003 RFQ0101-001 RFQ0101-002 and so on. When I’m in the RFQ card, looking at RFQNo RFQ0100-001, if I click on a button called Revise, the program is supposed to create a new RFQ record with the primary key value of RFQ0100-004, that is it has to find the last record in the table starting with RFQ0100 and add 1 to the revision no portion(-001,-002,-003, etc.) of the RFQNo. We cannot have the Revision no. portion as a separate field because of other implications to the existing database design. Is there a way to SETRANGE or SETFILTER on part value of a field and do FIND? The code that I have is (“No.” in the code is RFQNo): Position := STRPOS(“No.”, ‘-’); IF Position <> 0 THEN RFQStr := COPYSTR(“No.”, 1, Position - 1) ELSE RFQStr := “No.”; HCMJobRec.RESET; HCMJobRec.SETFILTER(“No.”, ‘=%1*’, RFQStr); IF HCMJobRec.FIND(’+’) THEN BEGIN <…logic to increment number…> END; The above logic always looks for the exact value in the string RFQStr. But I want it to locate RFQ0100-003 as the last record that begins with RFQ0100 and fetch it. Is there a way to doo this? Thanks. Pari Somasundaram

Hi Pari, without testing it:


HCMJobRec.SETFILTER("No.", '=%1*', RFQStr); 

What happens if you remove the ‘=’? Andre

quote:


Originally posted by Andre DDB
Hi Pari, without testing it:


HCMJobRec.SETFILTER("No.", '=%1*', RFQStr); 

What happens if you remove the ‘=’? Andre


It does not work even after removing the ‘=’. Thanks for your suggestion. I found a work around that is a bit tedious to code but works. Pari.

Hi Pari, I had a similar problem today. Our ‘Document No.’ in ‘G/L Entry’ has different values. I want to show only lines with ‘ALL*’ in a form. The following code works:


Name	DataType	Subtype	Length
!G/L	Record	G/L Entry	
!EntryForm	Form	General Ledger Entries	
tmpTXT	Text		30

"!G/L".SETCURRENTKEY("!G/L"."G/L Account No.");
"!G/L".SETRANGE("!G/L"."G/L Account No.",'0103010');

__tmpTXT:='ALL' + '*';__

"!G/L".SETFILTER("!G/L"."Document No.",**tmpTXT**);
IF "!G/L".FIND('-') THEN;

"!EntryForm".SETTABLEVIEW("!G/L");
"!EntryForm".RUN;

I guess the filter should work for your problem too [8D]. New I’ve just tested it with tmpTXT:=‘ALL’; “!G/L”.SETFILTER("!G/L".“Document No.”,’%1*’,tmpTXT); It doesn’t work! Replace ‘%1*’ with your variable. Add the ‘*’ to your variable before setting the filter. /New bye Andre

quote:


Originally posted by Andre DDB
Hi Pari, I had a similar problem today. Our ‘Document No.’ in ‘G/L Entry’ has different values. I want to show only lines with ‘ALL*’ in a form. The following code works:


Name	DataType	Subtype	Length
!G/L	Record	G/L Entry	
!EntryForm	Form	General Ledger Entries	
tmpTXT	Text		30

"!G/L".SETCURRENTKEY("!G/L"."G/L Account No.");
"!G/L".SETRANGE("!G/L"."G/L Account No.",'0103010');

__tmpTXT:='ALL' + '*';__

"!G/L".SETFILTER("!G/L"."Document No.",**tmpTXT**);
IF "!G/L".FIND('-') THEN;

"!EntryForm".SETTABLEVIEW("!G/L");
"!EntryForm".RUN;

I guess the filter should work for your problem too [8D]. New I’ve just tested it with tmpTXT:=‘ALL’; “!G/L”.SETFILTER("!G/L".“Document No.”,’%1*’,tmpTXT); It doesn’t work! Replace ‘%1*’ with your variable. Add the ‘*’ to your variable before setting the filter. /New bye Andre


Thanks Andre. I wish I had thought of that! I could have saved a lot of time! Pari Somasundaram.