Menu Security BAQ

Epicor ERP 9
1

Hello Everyone,

I want to create a BAQ that shows the Parent Menu and Sub Menu and the security group associated to each. I would appreciate if someone can tell me how to create the relationship.

Thanks in advance.

Miguel

2

You need multiple copies of the table. Chain them together with:

  • table0.ParentID = table1.MenuID (outer left join)
  • table1.ParentID = table2.MenuID (outer left join)
  • table2.ParentID = table3.MenuID (outer left join)

and so on for how ever many levels deep you menus go.

BTW - the first instance of a table will just be the table name with no number after it. I used table0 to represent this in the above.

3

Hello Calvin,

I am still new to this, would I create 3 copies of the Menu or SecGroup? and how do I tie them together.

Thanks in advance.

Miguel

4

Start with the menu table. Just put it in once. Then add some columns (I’d suggest the menuid description and parentmenu) and test it. Note that those names are guess off the top of my head.

After you test it you’ll notice that the parent menu value for some rows is the menuid for one of the rows.

Now add another instance of the menu table (it will be named with a 1 at the end) and add a relationship between them connecting parentid of the first table to the menuid of the second.

Now add column for the description from the second.

If you test it now you’ll get what had before and now the new column shows the name of the menu holding the menu item. Like ‘Part’ and ‘Setup’

Ad the table again (name will end with 2) and relate to tabel1 with
table1.parentid = table2.menuid. Then add the description column from table2 and test it.

You should see the menu, its parent, and its grandparent.

Now go and add the second table and relate it to the secID of the first table. Then choose the columns to display from this new table.

Hope that helps

5

Or see the following:

2 Likes
6

Whoops …

I went through all that thinking you were on E10. I’ve never used E9, so no idea on if any of that applies

7

Hello Calvin,

That worked as you can see I now can display the structure.

image
image621×538 9.74 KB

Now I can display the security group associated to the menu?

Thanks in Advance.

Miguel

8

Hello Calvin,

Is it possible to display the security group associated with the functions listed on the menu?

Thanks in Advance,

Miguel

9

Or a CTE query. :wink:
Edit: E9 I don’t think supported recursive queries.

@RC2020, a BAQ setup like this should work. You should already have the top row of Ice.Menu just need the link to Ice.Security

image
image694×551 13.1 KB

1 Like
10

Hello Randy,

I don’t see ICE.Menu as an option (See my available selection below)

image
image445×721 19.3 KB

How to add the table in your example?

Thanks in Advance.

Miguel

11

You already have the Menu tables in your BAQ. Just add the Security table to the first Menu table

12

Hello Calvin,

How I setup the Table relations between the Menu and the SecGroup?

Thanks in Advance,

Miguel

13

Company to Company and SecCode to SecCode
(The field names might not be exactly those spellings)

Then add a columns from Security.

You’ll have to do some decoding of those security table fields as they will be combinations of checkboxes, and tilde separated strings.

1 Like
14

Hello Calvin,

The Security table works, but I am trying to get the Security Group (SecGroup) but it doesn’t have any relations other than Company which just puts any Group with any function and not what is actually assigned.

Below is my setup less the Sec Group which I would like to put on the dashboard.

image
image704×354 14 KB

Thanks in Advance,

Miguel

15

Here is where it will get difficult. I believe that the approved “users” on a record in the Security table will be a tilde delimited list. Like the column Security.EntryList.

For example: ckrusen~alicej~bobq~oe101

And some of those might actually be security groups. In my example oe101 is a group for Order Entry that we created.

The relationship between Security and SecGroup, can’t be made very easily (if even at all in E9). In my example, the SecGroup record for oe101 would be desired, but the oe101 is part of that delimited string.

edit

it is actually way more complicated than that. SecGroup only holds the description for the group. Members of the group are based on which Userfile records have the SecGroup.SecCode in the Userfile.GroupList field.

For example, if the UserFile table was:

DcdUserID   GroupList
=========   ==========
ckrusen     oe101~po102
alicej      oe101~po102
bobq        po102~acct202
joeq        inv-321

I (ckrusen) would be a member of Sec Groups oe101 and po102 as would user alicej.
use bobq would be a member of Sec Groups po102 and acct201

If a Security record had an EntryList filed of joeq~oe101 then the following users could acces a menu that specifies that secCode:
myself (I’m a member of oe101), alicej (also a member of oe101), and joeq (he’s explicitly listed in the EntryList)

16

Calvin is correct and I’m not 100% sure it can be done in E10 much less E9.

We have two BAQs; one for Menu Security and one for the Users. Paste Each into Excel and our audit team does Excel formula/pivots.

1 Like
17

Hello Gentlemen,

So since the only report I was able to generate was users with security groups. Does anyone know how I can segregate the groups column into separate columns, if possible.

Example:
User ID Groups Grp1 Grp2 Grp3 Grp4
mlopez AP~AR~PUR~SHP and have the groups show AP AR PUR SHP

Thanks in advance.

Miguel

18

Hello Everyone,

Is it possible to display groups and the users within the group?

Thanks in Advance.

Miguel

19

If you pull the report into Excel, you can use the “Text to Columns” function (on the “Data” tab of the ribbon bar)

1 Like