I presume that your WHERE part of query is within the IAL - that should be the reason.

When you use “Use data source condition”, the navigation uses conditions set in data source to filter data in form/table you’re sent to. 

You can try doing your data aggregation within IAL and moving WHERE to data source (shouldn’t be an issue if your IAL is set for ‘Live Data’).

If you were to use different Lobby Elements you can also make use of ‘Column values’:

Hi Jack,

I made this setup:

IAL:

select 
a.project_id, 
a.order_no, 
a.state,
a.customer_po_no, 
a.CF$_C_CONTRACT_REF, 
a.REGION_CODE, 
a.DELIVERY_TERMS, 
a.PAY_TERM_ID, 
b.vendor_no,
from 
ifsapp.customer_order_cfv a LEFT OUTER JOIN (select * from ifsapp.customer_order_line where line_item_no <> '-1') b
ON a.order_no = b.order_no

data source:

lobby element: 

But unfortunately it is not working when I click on the lobby element:

I assume the problem is the JOIN in the IAL or did I miss something?

Perhaps you have an idea.

Thank you! 

Best

Mattis

Hi Jack,

I made this setup:

IAL:

select 
a.project_id, 
a.order_no, 
a.state,
a.customer_po_no, 
a.CF$_C_CONTRACT_REF, 
a.REGION_CODE, 
a.DELIVERY_TERMS, 
a.PAY_TERM_ID, 
b.vendor_no,
from 
ifsapp.customer_order_cfv a LEFT OUTER JOIN (select * from ifsapp.customer_order_line where line_item_no <> '-1') b
ON a.order_no = b.order_no

 

data source:

lobby element: 

 

But unfortunately it is not working when I click on the lobby element:

 

I assume the problem is the JOIN in the IAL or did I miss something?

 

Perhaps you have an idea.

 

 

Thank you! 

 

Best

Mattis

Change the target column from distinct(ORDER_NO) to just ORDER_NO, and it should work.