Lobby display object not showing data App9.17

Dear @RogerB 

did you check the permission set?

You must grant the IAL in the permission set.

Do you use IFSINFO to access the IAL? → ifsinfo.your_IAL

Thanks for the reply.
Yes, the IAL is in the IFSINFO schema and is owned by the IFSINFO user.

The user I am testing with, has access to the data normally, just the Lobby element still refuses to give results
If I connect to the database as the test user, I can run SQL to select from the IAL and I get results, just not from the lobby.
 

Do you use any parameters?

Maybe you can post here your datasource with the IAL?

The IAL code is:-

  CREATE OR REPLACE FORCE EDITIONABLE VIEW "IFSINFO"."PD_MY_SUPPLIER_INVOICES" ("INVOICE_NO", "USERID", "COMPANY", "IDENTITY", "PO_REF_NUMBER") AS 
  SELECT invoice_no, userid, company, identity, PO_REF_NUMBER
FROM IFSINFO.PD_MY_SUPPLIER_INVOICES_IAL;

  GRANT SELECT ON "IFSINFO"."PD_MY_SUPPLIER_INVOICES" TO "MCRO01";
  GRANT SELECT ON "IFSINFO"."PD_MY_SUPPLIER_INVOICES" TO "PD-PROC-ORDERS";
  GRANT SELECT ON "IFSINFO"."PD_MY_SUPPLIER_INVOICES" TO "IFSAPP" WITH GRANT OPTION;
  GRANT SELECT ON "IFSINFO"."PD_MY_SUPPLIER_INVOICES" TO "IFSSYS";

As an IAL/view, it works perfectly. Call it in SQL and the test user and I get one line returned

Data Element returns the one line
Display element just shows the cross

Can you spot what I have done wrong or is this just Lobby being a pain?

Thanks

Hi @RogerB 

can you move your cursor over the cross?

It shuld show you the error message.

Also check here if your IAL is granted to your permission set:

I didn’t know about the error message. Thanks for that tip.
 

It used to be in the permission set, but it’s no longer even showing up

I guess I broke than when trying all the variations.
Ok, I deleted the IAL and have recreated and it now shows and is enabled in the main permission set
 

Data Designer still returns lots of data, fairly quickly.
Display element takes a lot longer but still returns the cross with the above timed out error. For a short time it did show column names and no cross, but then it went back to the cross

At this stage it should return ALL the records as I have not filterered to the user yet.

How would I select only records for the logged on user. We were told to use USERID = ‘#PERSONID#’ where USERID is a field in the IAL, but I am beginning to doubt that as well..

How did you create the IAL? In the IFS client or with PL/SQL developer?

Don’t wirthe this “USERID = ‘#PERSONID#’” in the IAL but in your lobby datasource.

Or you can try this as well: ifsapp.fnd_session_api.get_fnd_user()

I have tried both methods but usually it is via IAL create in the client. as that sets up permissions correctly.

The USERID option is in the Data SOurce

I have tried the fnd_session call as well but I still get the cross

If Ilog on to sql developer as the test user and run  select on the IAL 

“from ifsinfo.PD_My_Supplier_Invoices”

I get one line back which is expected

If I Preview the data source withthe same userid mnually added, I get theh same single line

But the display element still only gives me the Cross

I have just tried deleteing the display element and recreating and get the same.

I can creae it, select all the fields from the data source but as soon as I try to preview or view, I get the cross..

I don’t kow why you get the time out error.

You get it directly, right?

Usually you should get it after 60 seconds if it takes more than 60 seconds to load.

Where do you type your where condition if you create an IAL? Can you show it?

No. The timeout error arrives after about 2 minutes..

Is THAT the issue? Is there a max time an element can wait for data before it times out?

The where part is in the Data Element, as shown above?

The code in the IAL is 

  select ii.invoice_no, poh.userid, ii.company, ii.identity, ii.PO_REF_NUMBER
from IFSAPP.incoming_invoice2 ii

left join IFSAPP.purchase_order_hist poh
on poh.order_no = ii.po_ref_number and date_entered > to_date('01/01/2020','DD/MM/YYYY') and message_text = 'Planned'

where   ii.state in ('PrelPosted', 'PaidPrelPosted')

I’ve recreated your IAL on our system.

Changed: where ii.state in to ii.objstate in 

The lobby element works perfectly without any errors.

Glad that it works. 👍🏽

But I don’t understand why you marked your comment as best. 😂

Sorry
I think I clicked the wrong place :-(
I was trying to mark your reply as the best solution because it was the perfect answer.
The community forum does some odd things..

The lobby is now working through to the element and it is nice and fast.

My only issue now is that the click through does not change company. It is stuck on the default one

Do you know if there is a way to click through to another screen and change the copany it uses?