NAV Query Object : filter

shibily
shibily Member Posts: 89
edited 2015-06-27 in NAV Three Tier
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

Comments

  • ara3n
    ara3n Member Posts: 9,258
    You'll need two queries.
    Ahmed Rashed Amini
    Independent Consultant/Developer


    blog: https://dynamicsuser.net/nav/b/ara3n
  • ayhan06
    ayhan06 Member Posts: 210
    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.

Categories