Associating subfield values to a linked table

[#1]

I have one data source of users and their assigned roles in our financial system. If a user has more than 1 role, all roles (up to, say, 4 roles) appear in the “Role” field and are separate by semicolon.

I’ve performed a subfield function to separate multiple roles into separate fields.

I have another data source of the roles and their applicable permissions so that I can view the permissions a user has. The key between the 2 data sets is the Role title.

With the subfield function, though, I lose the ability to view the permissions of a 2^nd, 3^rd or 4^th assigned role when I click on it. How can I associate the role subfields to the permissions table?

[swuehl]

Sorry, I don't really get what you want to achieve.

When you select / click a role, the permissions shown are limited to that role. This is QV standard behaviour.

If you select a user, you should see all roles and their permissions.

[#3]

I understand that. The issue I have is that if a user has multiple roles, even after I subfield out the roles I cannot view the associated permissions.

Source data looks like this:

User	Roles
John	Administrator
Jane	Administrator; Controller; VP

When I run subfield, I get new fields. Note the roles for Jane:

User	Role1	Role2	Role3
John	Administrator
Jane	Administrator	Controller	VP

If I click the role that John has, I can view those permissions. If, however, I select the Controller role it does not display the permissions (in fact, it grays them all out).

So, how do I view the permissions of the roles in the subfields?

[swuehl]

Ah, you created additional fields like

LOAD

       subfield(Roles,';',1) as Role1,

       subfield(Roles,';',2) as Role2,

       subfield(Roles,';',3) as Role3,

...

Try creating your subfields like

LOAD

       subfield(Roles,';') as Role

        ....

This creates multiple records per User / Role.

[#5]

When I do that I get an error that says field names must be unique. Here is what I put in the script:

Subfield(Roles, '; ',1) as Role,

Subfield(Roles, '; ',2) as Role,

Subfield(Roles, '; ',3) as Role

I also tried

Subfield(Roles, '; ') as Role,

Subfield(Roles, '; ') as Role,

Subfield(Roles, '; ') as Role

and got the same error

[Gysbert_Wassenaar · Accepted solution]

Just once is enough. The subfield function will split the values and create a records for each value. What you get is:

User, Role

John, Administrator

Jane, Administrator

Jane, Controller

Jane, Vp