Hello,
I am trying to create an aggregate or SQL statement that will return all of the Groups that a user is not already apart of.
On the UserDetail screen, I have a GroupUser table, listing all of the groups that that user is in. There is a link to another screen to add that specific user to additional groups.
I am not very familiar with SQL, and I think you need SQL for what I'm trying to do. I've tried using an aggregate with the filter of "group_user.user_id<>UserId", but the aggregate still returns groups with group_users of other userId's.
Please tell me if I can clarify more, and I appreciate your help!
Hi Andi,
You can accomplish this with and aggregate with the entities Group and Group_User. Join them like this "Group" With or without "Group_user" and the this condition:
SyntaxEditor Code Snippet
Group.Id = Group_User.Group_Id and Group_User.User_Id = UserId
Just finish it by filtering by the ones which Group_User.Id = NullIdentifier()
Hope this helps.
Regards,
Marcelo
Marcelo Ferreira wrote:
Thank you so much! It's that filter I was missing. You rock!
You should be able to do this with an aggregate.
The basics are you need to have an aggregate with both the Group and Group_User entities from System (you will probably need to add these using the Manage Dependencies window).
Start by creating a Preparation for your screen, and dragging the Group entity onto the flow. Then double-click and add a new source, using Group_User as the additional source. The default join should work for this.
Then add the filter:
Group_User.User_Id <> GetUserId()
This should return all groups that the logged in user is not a member of. If you want to get groups for an arbitrary user, you would use an input parameter with the user ID of that user, instead of GetUserId().
From what you describe above, it sounds like this is what you've tried, and I would expect it to return groups for other user IDs, since those groups might not include the desired user.
If the above doesn't help, please share a small OML that reproduces the issue. That might help with troubleshooting.
Hi All,
I had started working on a different answer to this, as I understood the question, you want to get a list of the groups that a given user is not part of.
If I'm reading the solution of Marcelo correctly, this will only return groups that no users are a member of, so as soon as any other user is in that group, it will not show up anymore for selection.
And I think Andrew's solution will still show groups that the current user is in, if at least one other user is also in it.
With SQL, this use case can be solved with for example a NOT EXISTS clause, I haven't looked into recreating this with an aggregate, probably not straightforward, so I think SQL is the simpler solution here.
SELECT * FROM {Group} WHERE NOT EXISTS ( SELECT * FROM {Group_User} WHERE {Group_User}.[User_Id] = @InUserId AND {Group_User}.[Group_Id] = {Group}.[Id] )
Dorine
Ok,
so out of curiosity, I tried to concoct an aggregate that would do the same.
It is possible, but I still think the SQL is the simpler solution and probably the faster.
so basically, here's how you could do it with an aggregate :
1) sources are group with or without group_user
2) add calculated column with formula
If(Group_User.User_Id = varUserId and varUserId <> NullIdentifier(), 1, 0)
3) add groupby on columns group id and group name, and max on this new calculated column
4) add filter on the maxed column
UserIsInGroupMax = 0
as you see, very convoluted, so only to be used by those for who using SQL goes against their deepest beliefs.
See attached oml
Hi Dorine,
I think you are over complicating. My solution works because I'm not joining with all the records of Group_User. I'm only left joining with the ones that belong to the current user
and Group_User.User_Id = UserId
Give it a go and let me know if you have any doubts.