Apps9 Lobby Count Element navigation to URL help required

Hi @sholmes,

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