Filter on table where x <> y

babstx

Is there a way to use a filter in Navision for the following scenario:

Customer Table
Where Sell-to Customer No. <> Bill-to Customer No.

Thanks!! :P

DaveT

Hi,

Not possible - but you could create a flowfield e.g. in Sales Header - "Bill-to Same" with the calcformula
Exist("Sales Header" WHERE (Document Type=FIELD(Document Type),Sell-to Customer No.=FIELD(Bill-to Customer No.),No.=FIELD(No.)))
and then filter on this field

DaveT

Hi,

Just noticed the table - will work on customer table too. You could easly do this is a report e.g.

if "no." = "bill-to customer no." then
   currreport.skip;

kine

But both versions will means that DB server will read all entries in the table and filter them after that on client. It will not read filtered dataset...

garak

If you need this often, you can also do following.

Create a field (Bool) like CustNoIsDiff. in the Customer Table.
Now, if you modify in the Customer table the Field "bill-to customer no." you set the new field to true if

"CustNoIsDiff." := ("No." <> "bill-to customer no.") and ("bill-to customer no." <> '')

For all existing Customers, you must create a batch to fill the new field.

Now you can use the field as Filter in your reports.

Regards

DaveT

kine wrote:

But both versions will means that DB server will read all entries in the table and filter them after that on client. It will not read filtered dataset...

Good point, didn't think of this angle - was thinking of a solution without a dev. licence (still needs table designer)