Record Variable filtering

sarkie83 · 2007-08-15

This is part of the code i've added to the the Sales Line table - OnModify
It wasn't working as I hoped, so I put it in a report, to test the output but the COUNT value comes out at several thousand.

       SalesHeader.RESET;
       SalesHeader.SETCURRENTKEY("Document Type","No.");
       SalesHeader.SETFILTER("No.","Document No.");   
       SalesHeader.SETRANGE("Holding Order",FALSE);
       SalesHeader.SETFILTER("Hold Code", '<> HELD');

       IF SalesHeader.COUNT = 1 THEN
       ....(actions)...

The code part in bold is where I need clarification..
SalesHeader.SETFILTER("No.","Document No.");

Is this selecting the No. field in SalesHeader and filtering where it = the Document No. in the current Sale Line?

btw SalesHeader is a record variable

Thanks,
Kieran

DenSter · 2007-08-15

Your approach is a bit off, in my opinion.

The primary key of the Sales Header table is "Document Type,No.". The primary key of the Sales Line table is "Document Type,Document No.,Line No.". These two tables are related where the Document Type fields are equal, and the "No." field in the Sales Header table is equal to the "Document No." field in the Sales Line table. Essentially this means that for each combination of "Document Type" and "Document No." (which uniquely identifies a Sales Header record), you can have an unlimited number of Sales Lines, and they are identified by the value of the "Line No." field.

So when you set a filter on the Sales Header on both the Document Type and the Document Number fields, you are uniquely identifying the Sales Header record. A better way to do this would be:

SalesHeader.GET(SalesLine."Document Type",SalesLine."Document No.");

Then, instead of doing a COUNT, you can evaluate field values in the Sales Header record, something like this:

IF (SalesHeader."Holding Order" = FALSE) AND
  (SalesHeader."Hold Code" <> 'HELD') THEN BEGIN
  //Do your stuff here
END;

sarkie83 · 2007-09-11

Sorry - forgot to reply to this, but thank you, that enabled me to do what I needed

DenSter · 2007-09-11

Oh don't worry about it, you're so welcome. Glad you could make it work, and thanks for following up

Record Variable filtering

Answers

Categories