1

Loop through Records

I am creating a user access module.

  1. System User
  2. User Group
  3. User Permissions

None of these records are related. My goal is to use Dynamic Choice fields. So a user can be a part of multiple User Groups, and each user groups can have various User Permissions. 

 A user is assigned to multiple user groups, and user groups have multiple user permissions.

 

I am trying to return a list of the user permissions that is tied to all user groups. I have

let userGroups := this.'Permission Groups';
select 'User Permissions' where Id = userGroups

But it's not working. I would imagine it has to loop through each user group that is assigned to the user.

 

Please help.

13 replies

null
    • Fred
    • 5 mths ago
    • Reported - view

    to answer your question, I first need to tell you how multiple choice (regular or dynamic) fields work. Once you select a second choice, then Ninox will return an array of the choices. You can see this if you create a new formula field then add this code:

    numbers(dynamic multichoice field)

    You will see the array shrink/grow as you deselect/select choices.

    Which means when you use the equal sign you are asking Ninox to match all of the choices. Which means your

    where Id = userGroups

    will only be equal when you select only 1 choice.

    One way to solve this is to use the contains() command to tell Ninox to look for the record Id in the userGroups array variable. It would look something like:

    where contains(userGroups,Id)

    Some thoughts:

    1) I don't think you need Permissions to be a separate table. It can just be a multichoice field in userGroup. I don't think your list of permissions will change very much. But if you want to leave it as a table then maybe you can create another table called groupPermission and make a child of User Groups and add a link to User Permissions. You now have created a N:N table between User Groups and User Permissions. So you can link as many groups to as many permissions as you want.

    2) Maybe consider creating a new table called Membership and make it a child of userGroup and add a link to SystemUser. Like above, you can link as many users to as many groups as you want.

      • stephenjtaft
      • 5 mths ago
      • Reported - view

       thanks for your reply. 

       

      In response to #1. I want to shy away from a multichoice field, just to have the large freedom to add multiple granular permissions. 

      With the information you have provided, I've tried this formula but can't get it to return all IDs for permissions that are associated with each permission group.

      let ids := numbers('User Group');
let num:=number(select 'User Group'.Permissions where contains(Permissions,ids))
select Permissions where id = num;
    • Fred
    • 5 mths ago
    • Reported - view
     said:
    just to have the large freedom to add multiple granular permissions

    I guess we are thinking differently about what a permission is. My thinking was in terms of permissions to do some action in a DB (i.e. read, write, delete) so there is not that many possible different permissions.

    I know Nioxus has some good security templates available that you can access with a subscription. Maybe you can join and review what they did. No need to reinvent the wheel.

    Now on to your code.

    1) which table is this code in?

    2) what kind of field is Permissions in 'User Group'?

    3) line 1 gets the selections from the dynamic multichoice field 'User Group'. This will be an array.

    4) line 2, the where needs to follow the table name.

    5) line 2, the contains() command is formatted with the large group followed by a single item. So you can't use the variable ids since it is an array. It looks like you want to find the User Groups selected in line 1 so that would look something like:

    let ids := numbers('User Group');
let num := select 'User Group' where contains(ids,Id)

    You have to be aware of what data you have and compare it to the relevant data.

    Since dynamic fields give you specific record Ids then another way to get specific records is to use a for loop and the record() command.

    let ugRecs := for loop1 in numbers('User Group') do
    record('User Group',loop1)
end

    As long as you don't have multiple selections in 'User Group' dynamic choice then you can use the variable to show the selection of Permissions in 'User Group':

    ugRecs.Permissions

    This breaks as soon as you select a second 'User Group'.

    So maybe the use of the N:N tables will be more useful.

    If you can upload a sample DB, I can see what is going on.

    • stephenjtaft
    • 5 mths ago
    • Reported - view

    Here is what I have

    User.ninox
    • Fred
    • 5 mths ago
    • Reported - view

    Hi

    I just noticed that I haven't responded to your last post. I'm sorry about that.

    Do you still need help with this?

      • stephenjtaft
      • 4 mths ago
      • Reported - view

        ,

       

      I would appreciate some assistance on this still. 

      • Fred
      • 4 mths ago
      • Reported - view

      so where are you at right now?

    • stephenjtaft
    • 4 mths ago
    • Reported - view

    I've attached my sample database. 

     

    Basically, in System User table, a user can be assigned multiple 'User Group' to have various different permissions. 

     

    Within table 'User Group' there is a dynamic list of 'Permissions' that are listed in the Permissions table.

     

    My goal is to return a list of the granular user permissions that the specified user has access to. So look at System User.User Group and return all of the assigned permissions in each group. These are all dynamic, and have no relationships. 

     

    Thank you,

      • Fred
      • 4 mths ago
      • Reported - view

      You can try this in the view element:

      let userGroups := numbers('User Group');
select 'User Group' where contains(userGroups, number(Id))

      or this in a formula field and it will just return a string of the permissions:

      let ids := numbers('User Group');
let returnIDs := for loop1 in ids do
        record('User Group',loop1)
    end;
let x := for loop2 in returnIDs do
        loop2.text(Permissions)
    end;
x
      • stephenjtaft
      • 4 mths ago
      • Reported - view

       

       

      Thank you Fred. That worked!! I greatly appreciate your help on this!!

      • stephenjtaft
      • 4 mths ago
      • Reported - view

       

      I am now looking to make this a global variable that can check if the current user User() has access to a certain 'User Permission'. Any tips?

       

      Thinking something like:

      do as server
    let currentUser:=first(select 'System User' where int_linked_user = user() or Email = userEmail());
    record('System User',currentUser).'Permission List') contains "Passed Specific user Permission"
end;

       

      So I can call in my table checkPermission("User Maintenance Access") and if true is returned the user has permission to view/modify as set.

       

      Do you have any suggestions?

      • Fred
      • 4 mths ago
      • Reported - view

      I don't have any off the top of my head. I'm the only user of my DB so I haven't delved into security very much. Nioxus has many videos and templates on security so you may want to get a subscription and check out what they have to say. No need to reinvent the wheel.

      • stephenjtaft
      • 4 mths ago
      • Reported - view

       I got it!

      Setting a global function, I have:

      function permCheck(access : text) do
          let currentUser := first(select 'System User' where int_linked_user = user() or Email = userEmail());
          record('System User',currentUser).'Permission List' like access
      end

Login to reply

Content aside

  • 1 Likes
  • 4 mths agoLast active
  • 13Replies
  • 137Views
  • 3 Following