Hi @Charles

if I understood you correctly you need a parameter “COMPANY”. And another parameter should show you all connected sites automatically, right?

Hello @Link 

I give you an exemple to be clear.

We have 2 companies with multiples sites associated :

• C1
  • S1
  • S2
  • S3
  • S4
• C2
  • S5
  • S6
  • S7
  • S8

In the lobby, the goal is to filter with all sites from C1. At the moment, our customer has to :

• Filter with the first site, write the result and so on with the other sites
• With another SQL formula, he can write in his filter “S1,S2,S3,S4” and it will work but it is not a solution because they do not want to write all sites each time they come the lobby

So the idea is to have like you show a filter “Company” but in the SQL condition, it will find all sites associated to this company to make the filter. Because the field “Company” is most of the time not available in the logical unit from the lobby element.

For instance :

I want to see my customer orders from all sites of the company C1.

The idea is to write “C1” in the “Company” filter and it will search all customers orders from sites S1,S2,S3 and S4.

Hi @Charles 

OK, got it.

Basicly it is possible. But in some views or tables you haven’t the column Company or Site (also Contract). In this case maybe you can use APIs if exist or create an IAL.

This means you need the columns Company and Contract in the view which you need for your lobby data source. And you need this structure for all lobby elements. 

Hi,

It is very possible as long as the view/table has the column Site (contract), like Link mentions.

Example CUSTOMER_ORDER_LINE

The condition in the datasource would be:
CONTRACT IN (SELECT CONTRACT FROM &AO.SITE WHERE COMPANY = $COMPANY$)

BR
Johan

I agree with @JohanLindstrom .

In additional you can use the API IFSAPP.COMPANY_SITE_API.Get_Company (CONTRACT) to fetch the company of a site if you haven’t the column company but the site.

Thank you very muck both of you !

I just added ‘’ around $COMPANY$ and it works great.

After reading your answers, I have two questions :

1.  Where do you find the “&AO.SITE”  in your request ?

If I look on the system info on the site menu, I find this :

It will help me for other request.

2. Our customer still want to be able to filter by site if necessary.

For the moment, the condition is : 

and contract like '$site$'

It works well, you can write 1 site or “%” to obtain all sites connected to the user.

But another request from them is to be able to filter with multiple selected sites…

I found on the forum this topic :

AND CONTRACT IN ( select regexp_substr('$SITE$','t^,]+', 1, level) from dual     connect by regexp_substr('$SITE$', 'b^,]+', 1, level) is not null )

And the solution works well if you write multiple site separated with “,” but if you try “%” it does not work.

In conclusion the optimal solution for my customer would be :

• You have two filters : Site and Company
• If a company is provided (no site), we filter like your answers
• If one or several sites are provided (separated with “,”), we filter with them
• If “%” is provided for the site, we filter every site connected to the user

If I re-use my last exemple,

We have 2 companies with multiples sites associated :

• C1
  • S1
  • S2
  • S3
  • S4
• C2
  • S5
  • S6
  • S7
  • S8

This one might me a little bit more complicated :/

Hi @Charles 

filter multiple sites in the sql code is possible: example where contract in (‘S1’, ‘S2’) ….

But it isn’t possible with parameters in the lobby page.

I think I was not clear because it is possible to filter with multiples site in the lobby page.

With the formula given by Ashok in this topic, it works :

AND CONTRACT IN ( select regexp_substr('$SITE$',',^,]+', 1, level) from dual
    connect by regexp_substr('$SITE$', ''^,]+', 1, level) is not null )

My concern is to combine the filters with the conditions I wrote in my previous reply.

 

1.  Where do you find the “&AO.SITE”  in your request ?

If I look on the system info on the site menu, I find this :

It will help me for other request.

 

You can find it in Sites Analysis (Application Base Setup - Enterprise - Site > Sites Analysis

I don’t have much experience with regexp but try the following:

CONTRACT IN (SELECT CONTRACT FROM &AO.SITE WHERE COMPANY LIKE NVL('$COMPANY$','%') AND (CONTRACT LIKE NVL('$SITE$','%') OR CONTRACT IN ( select regexp_substr('$SITE$','_^,]+', 1, level) from dual
    connect by regexp_substr('$SITE$', 'p^,]+', 1, level) is not null )))
 

It should allow you to select multiple sites.

Or leave site and/or company empty which would select all sites/companies that the user is connected to

BR

Johan

@JohanLindstrom 

According to my firt tests, it seems to work like I wanted.

Thank you again 

@Charles happy to help