By using this website, you agree to our Terms of Use (click here)
Has anyone had any luck working out where the Restriction Group Data is stored?
I'm trying to build a simple Inquiry so that a client can easily review which accounts and sub accounts have been added to a Group.
When I inspect element it isn't really useful
😉
Acumatica stores the Group information in a table callesd RelationGroup. It stores what object is part of each Group Restriction information on the records themselves. In your case, that would be the Account and Sub tables. It stores this in the GroupMask field, which is present on each of these tables as well as any others that can be a part of a group (BAccount, InventoryItem, etc.). All of the GroupMask fields are stored in the DB as a varbinary type (which is basically a list of 1s and 0s). This lets them store multiple pieces of information in the same field, and they use bitwise Math to get the values out. Anyway, how it works is a lot to get into here, but if anyone is interested let me know and I can dive deeper into it when I have some time.
To answer your question. The simplest way to check if a record is in a restriction group:
Create a GI with the tables RelationGroup and what you want to check. For this example, I will use Account, but it could easily be Sub, or InventoryItem, or Users.
Make a Relation. Account is Parent, RelationGroup is Child
Link the Relation on Parent: ='YourGroupName' Equals Child: RelationGroup.GroupName
Include the fields you want in Results Grid. Add this line:
=CInt((CLong([Account.GroupMask])%(CLong([RelationGroup.GroupMask])*2))/CLong([RelationGroup.GroupMask]))
This field will now return a 1 if the Account is part of "YourGroupName" or a 0 if it isn't.
I don't have the written out for the A, A Inverse, B, B Inverse types, but if you want to take this further, the check out RelationGroup.GroupType.
@wyatt-erp Hey mate, I couldn't get that formula to return a value in the field. Could you potentially attach a screenshot for reference?
Cheers,
Hi @kalumw. Double check your relation. I am using Account, so make sure you change the column equation to look at your table of interest. To test your join, add RelationGroup.GroupName as a column. It should have your group name in it. If it doesn't there is something incorrect with your join. Check this part:
Make a Relation. Account is Parent, RelationGroup is Child
Link the Relation on Parent: ='YourGroupName' Equals Child: RelationGroup.GroupName
If it's still not working, attach the GI I can take a look to see what's up. When I last upgraded I reset my dev copy, so I don't think i have anything laying around with this anymore to share.
@wyatt-erp Thanks for following up. I've attached my GI below.
I double checked my relations and it seems to be correct. I'm also using account so it should be exactly the same as your formula. Let me know how you go.
It is working for me. Is your group name actually 101? In the relation you have ='101'. These are the results I got when testing using that:
@wyatt-erp I think the issue may be something to do with the fact that I am using a restriction by Branch and GL Account. Not just GL Account per group.
@kalumw I tested a new group on GL Accounts by Branch Access and it worked fine for me, no changes to the column's code needed. Can you try creating a second group to see if it picks anything up with that? I attached the GI again for good measure.
@wyatt-erp Thanks for you help. Unsure if I'm going to be able to get this one working. Imported the GI that you attached and tried it with new groups. Still no luck. Its not even displaying numbers in the field. See attached file.
