Section Access Help - Admin not seeing all data

[#1]

Good Afternoon All,

Please could someone help me with the following. I am trying to implement Section Access, here is my code:

Section Access;

LOAD

UPPER(ACCESS) AS ACCESS,

UPPER(HOME) AS NTNAME,

UPPER(DEPARTMENT) AS REGION;

SQL

SELECT

UP.USERTEXT4 AS ACCESS,

UP.USERTEXT3 AS HOME,

U.DEPARTMENT AS DEPARTMENT

FROM sysdba.USERINFO AS U

INNER JOIN sysdba.USERSECURITY AS US

ON U.USERID = US.USERID

INNER JOIN sysdba.USERPROFILE AS UP

ON U.USERID = UP.USERID;

And my Application code is:

Section

Application;

LOAD * INLINE [

REGION, Countryxx

UK, UK

DENMARK, Denmark

SCANDINAVIA, Denmark

SCANDINAVIA, Norway

SCANDINAVIA, Sweden

NORWAY, Norway

SWEDEN, Sweden

GERMANY, Germany

SPAIN, Spain

PORTUGAL, Portugal

IBERIA, Spain

IBERIA, Portugal

FRANCE, France

BELGIUM, Belgium

HOLLAND, Holland

LUXEMBOURG, Luxembourg

BENELUX, Belgium

BENELUX, Holland

BENELUX, Luxembourg

USA, USA

CANADA, Canada

AUSTRALIA, Australia

AUSTRALIA, New Zealand

AUSTRALIA, Singapore

NEW ZEALAND, New Zealand

SINGAPORE, Singapore

];

For any user it's working fine, but I want the admin to see everything despite the region they are assigned to. I am sure I have done this before and got it working, please can someone tell me how to fix this and tell me why it isn't working.

Kind Regards,

Miles

[hic]

There is always a way ...

If you start by defining a symbol '<ALL>', for example through

     MapDepartment:

     Mapping

     Load distinct '<ALL>' as Symbol, Concat(distinct Department,',') as Departments From Departments ;

...then you can use this in your authorization table

     LOAD

       ACCESS,

       USERID,

       PASSWORD,

       Trim(Subfield(Applymap('MapDepartment',DEPARTMENT),',')) as DEPARTMENT

     INLINE [

         ACCESS, USERID, PASSWORD, DEPARTMENT

         ADMIN, ADMIN, ADMIN,<ALL>

         ADMIN,  M09000, M09000,<ALL>

         USER, M09849, M09849, BI

         USER, M01122, M01122, IT];

The Applymap() function will replace '<ALL>' with 'BI,IT,X,Y,Z' or whichever departments you have.

The Subfield() function will create an implicit loop, so that if you have 3 values, you will get three records.

HIC

[#3]

Thanks, we were able to work your logic into our existing work and get it up and running!