NAV Query Object : filter

shibily

Hi,

I am using a Query object to create a Web Service (OData) with 2 Data Items Customer and Cust. Ledger Entry. (Cust. Ledger Entry DataItem is linked with Customer DataItem using Customer No. on DataItemLink).

My requirement is to list customer details (from customer dataItem) and the transaction details (from cust. ledger dataitem) and if there is no transaction, just list the customer details from Customer table. I used SQLJoinType as <Left Outer Join> on cust. ledger dataitem.

Problem is, I have a filter set on 'DataItemTableFilter' for Cust. Ledger dataitem (for the field Reason Code), and due to that filter, web service is not listing the customer details if there is no transaction linked to that customer. If I don't have that filter set on Cust. Ledger , then it will show up the customer details.

Is there any work-around so that I can use the filters and at the same time list the customer details if there is no transaction for that customer?

Thanks, in advance.
Shibily

ara3n

You'll need two queries.

ayhan06

Hi,

If you filter Reason Code in dataItemFilter property, joined recordset is filtred. e.g: If Customer has no Cust. Ledger Entry, reason code filed is empty, then NAV applies Reason Code filter to created resultset, so you don't receive Customer.


you need this statement:
select * from Customer
left join cust. ledger entry on customer.no = Cust. ledger entry. Customer No and Reason Code = 'XXX'

but now you have this statement:
select * from Customer
left join cust. ledger entry on customer.no = Cust. ledger entry. Customer No
where Reason Code = 'XXX'


so,
1. you have to create a flow filed in customer table like "Reason Code Filter".
2. in the query object, add the new field as filter.
3. in the DataItemLink property of Cust. LEdger Entry, filter Reson Code of Cust. ledger entry as "Reason code Filter" pf Customer table.
4. you have to filter "Reason Code Filter" field of Customer dataitem when calling query.