Apps9 Lobby Count Element navigation to URL help required
Hello all you very kind and helpful folks.
I’m needing help with navigating from the lobby count element to a page view. Below is the Data Source and as you can see I can’t use the Condition section as navigation parameters so I’ve added the MCH_CODE to the Order by to use, but it doesn’t appear to be working.
The Element designer as you can see has been set up to navigate to the Equipment Object Navigator page and use the data source Order By. Now the element when in a lobby page will navigate happily to the page but doesn’t bring any data back - any ideas please?
The page Equipment Object Navigator fetches data from the view “&AO.EQUIPMENT_OBJECT_UIV”.(For the parent section) and not from the work_order view.
I think that causes the navigation to not work.
Best Regards, Bhagya
Hello @Bhagya Wickramasinghe
The Equipment_object_uiv has the MCH_CODE as a field so why won’t it look at this? Sarah
@Bhagya Wickramasinghe to add I have a list element where the link work, the element uses the same data source but within the design of the list element I can choose the column value unlike in the count element - unsure why there is this difference
This is a limitation in the Counter object since you do not have the data in the counter.
In your example the SQL statement is like this:
SELECT count(wo_no)
FROM ….
And then you do not have mch_code in the counter object. In the list element you have the values in the list that’s why you can use it there.
So to solve your problem you can create a data source for view EQUIPMENT_OBJECT_UIV and then have a sub-select in the WHERE statement like this:
(mch_code, contract) IN (SELECT mch_code, mch_code_contract FROM &AO.work_order WHERE org_code = ‘TR’ ...etc.)
Then you check “Use data source condition” in the navigation in the counter and it should work.
Thanks @Tomas Ruderfelt - however the Equipment_Object_UIV doesn’t have the WO_NO field which is what I need to count up
Sorry I was not showing the whole solution.
Add this as a column and summarize in the counter:
(SELECT count(wo_no) FROM &AO.work_order WHERE mch_code = EQUIPMENT_OBJECT_UIV.mch_code AND mch_code_contract = EQUIPMENT_OBJECT_UIV.contract AND org_code = ‘TR’ ...etc.)
Hi @Tomas Ruderfelt , my SQL skills are very minimal, really not there so sorry for being thick but the way the data source designer works to me is that is only allows you to choose columns from the view you select or am I incorrect there? I’m in Apps9 update 17
You can add empty columns with the “Add” button. Then you can write a SQL statement in the Column filed. Like in the picture below.
When I do this I test this out in my SQL tool with a query like this to see that the subselect in the SELECT part is working. OBS! the subselect must be on one line to copy and paste it into the column:
SELECT (SELECT count(wo_no) FROM IFSAPP.work_order WHERE mch_code = EQUIPMENT_OBJECT_UIV.mch_code AND mch_code_contract = EQUIPMENT_OBJECT_UIV.contract AND org_code = ‘TR’ ...etc.) FROM IFSAPP.EQUIPMENT_OBJECT_UIV
@Tomas Ruderfelt - said I wasn’t good at sql, never realised that that was possible - thank you so much
