In order to allow me to focus on Consulting (click here) and Courses (click here), I continue to review all posts here in the Forums, but, in general, I'm only personally responding to posts that I mark as #AcumaticaTnT (click here). For Questions (click here), others may respond, or you can post over at Community.Acumatica.com (click here).
#AcumaticaTnT Importing Acumatica User Roles / User Security Permissions from Excel
Sometimes it's easier to setup User Security in a spreadsheet, rather than in Acumatica, then import the spreadsheet into Acumatica.
Here is an Excel file that you can use for this purpose:
There are two Worksheets in the Excel file:
Site Map - This is where you can manually populate the security. To populate this Worksheet, you can export the Site Map (SM200520) screen into a spreadsheet, then copy/paste the values into the Table in this Worksheet. Only keep the Screen ID, Title, Workspaces, and Category columns. Search for a comma in the Workspaces column to find any instances where a screen is listed in more than one Workspace and remove all Workspaces (including the commas) other than the first Workspace listed (if you don't do this, the import won't work). Then add additional columns for each User Role security group that you want to setup in Acumatica (make sure you setup each User Role in Acumatica before running the import). I setup User Role 1, User Role 2, and User Role 3 as examples in the attached spreadsheet.
Sheet1 - This Worksheet has the format needed by the Import Scenario and it gets generated from the Site Map Worksheet when you press Data -> Refresh All on the ribbon.
To prepare the Excel file, populate the Site Map Worksheet manually. Then edit the query that populates the Sheet1 Worksheet by doing the following:
1. Click Data -> Queries & Connections to make them available on the right-hand side:
2. On the right-hand side, right-click on Import and left-click on Edit:
3. Make sure that View -> Formula Bar is selected, then left-click Added Column on the right-hand side and change TIM - SALESDEMO to the name of your Tenant
4. The name of your Tenant that is needed can be found in the Access Rights by Role (SM201025) screen:
You can import the spreadsheet into Acumatica by doing the following:
1. Upload the spreadsheet to the Data Providers (SM206015) screen in Acumatica:
2. Setup an Import Scenario in the Import Scenarios (SM206025) screen in Acumatica:
3. Run the Import Scenario using the Import Scenarios (SM206025) screen.
I tried a simple test in 2020R1 and 2020R2 and it doesn't work seem to work: I get an error about "Error: The view doesn't exist."
Here is the import scenario. Am I missing something?
I am on 2021R1 and somehow Adam's method can only update the first role
(I guess that's the result of unchecking "Active" for Role)
Per Tim and Adam's progress so far, I started from scratch once again.
and found that "Role" and "Role Description" are both key for view: EntityRoles
(I guess that's the reason for error: 1 items have not been processed successfully. View name: EntityRoles.)
So, I added "Role" and "Role Description" for input. then everything works as expected!
Hopefully this helps 🙂