Form filtering one field against another?

How can you display all records in a form based on the Sales Line table that is filtered where SalesLine.Quantity <> SalesLine.QuantityShipped?

How about in the OnOpenForm trigger: SalesLine.SETFILTER(Quantity,’<>%1’,SalesLine.QuantityShipped) Since SalesLine will be Rec on the form, you leave out the “SaleLine” Table Name Steve

No Steve that does not work, that will just filter the quantity that does not equal the quantity shipped on the current record. If you do not have a current record that would be quantity <>0. Unfortunately Navision does not do such a thing, but Navision does have an “outstanding quantity” field on the sales line, which is what I think you are trying to find. So SalesLine.SETFILTER(“outstanding quantity”,’>0’); Paul Baxter

In that case, you could use “the goode olde mark loop”:

// maybe some filters here ?
IF SalesLine.FIND('-') THEN
    IF (SalesLine.Quantity <> SalesLine.QuantityShipped) THEN
  UNTIL SalesLine.NEXT  = 0;
// now set the TABLEVIEW of your SalesLine-Form to SalesLine, and RUN it

Regards, Jörg Joerg A. Stryk Apollo-Optik, IT/ERP Edited by - stryk on 2002 Jun 14 15:15:11

Not in this case as “outstanding quantity”:=Quantity- “Quantity Shipped”; So its just a case of SalesLine.SETFILTER(“outstanding quantity”,’>0’); Which looks a lot easier than going through the effort of marking records to me. Paul Baxter

Thanks for the great ideas. Unfortunately I can’t use the outstanding field because I must also only include orders that have at least something shipped (ie, one of the SalesLine records from a DocNo must have a QuantityShipped > 0). I thought about marking records, but was wondering if there was an easier way. It’s looking to me the way to do this is to use a temp table. Agreed? If so, how would this be done?

You could use SalesLine.RESET; SalesLine.SETFILTER(“outstanding quantity”,’>0’); SalesLine.SETFILTER(“Quantity Shipped”,’>0’); Which navision can do. Paul Baxter

That will excluded records that have QuantityShipped = 0. I need to see these for DocNo’s that have other lines with QuantityShipped > 0. I’ve decided to use a temp table to allow complete control over what gets displayed. Thx!

Yes I agree that the only way to do that is with temp tables. One piece of advice about temp tables is to do a RESET and DELETEALL before you use them as they are not always as empty as they should be. Paul Baxter

Thanks for your help! One Paul to another… :slight_smile: