AUGForums.com

An Acumatica User Group

  • Forums
  • Podcast
  • Blog
  • Live
  • Login
  • Start Here
  • Rolodex
  • Courses
  • Consulting
  • Register
Acumatica Forums

By using this website, you agree to our Terms of Use (click here)

  • Overview
  • Member List
  • All-Stars
  • Stats
  • Recent Posts
  • Help
Forums
AUG Forums
Everything Else
Importing Acumatica...
 
Notifications
Clear all

Importing Acumatica User Roles / User Security Permissions from Excel  

    Last Post
RSS

Tim Rodman
Posts: 2517
 Tim Rodman     ★★ All-Star ★★
August 19, 2020 9:41 am
(@timrodman)
Over 200 Posts
Joined: 5 years ago

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:

Z_Import User Security.xlsx

 

 

 

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.

image

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.

image

 

 

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:

image

2. On the right-hand side, right-click on Import and left-click on Edit:

image

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

image
image

4. The name of your Tenant that is needed can be found in the Access Rights by Role (SM201025) screen:

image

 

 

You can import the spreadsheet into Acumatica by doing the following:

1. Upload the spreadsheet to the Data Providers (SM206015) screen in Acumatica:

image

2. Setup an Import Scenario in the Import Scenarios (SM206025) screen in Acumatica:

image
image

3. Run the Import Scenario using the Import Scenarios (SM206025) screen.

3 Replies
AhmedBahar
Posts: 28
 AhmedBahar
January 20, 2021 2:38 pm
(@ahmedbahar)
21-50 Posts
Joined: 11 months ago

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."

image

Here is the import scenario. Am I missing something?

SM201020-0 Access Rights Import Scenario NEW.xml

 

Reply
Adam-McGuinnes
 Adam-McGuinnes
    ★★ All-Star ★★
(@adam-mcguinnes)
Joined: 2 months ago

2-5 Posts
Posts: 2
January 26, 2021 12:14 pm
Reply toAhmedBaharAhmedBahar

@ahmedbahar I am experiencing the same issue. I am on 2019R2, and managed to run a few traces. It seams to be some sort of issue with "View name: EntityRoles." in the import scenario. Have you had any luck getting this bug worked out?

Reply
Adam-McGuinnes
 Adam-McGuinnes
    ★★ All-Star ★★
(@adam-mcguinnes)
Joined: 2 months ago

2-5 Posts
Posts: 2
February 2, 2021 11:16 am
Reply toAhmedBaharAhmedBahar

@ahmedbahar If anyone else running across this error I have figured it out. I don't know why but you cannot import the .XML. You have to build it and follow the last screenshot of the import scenario to a tee. I ended up building the import scenario from scratch, being carful to add all the fields. There are 3 extra fields that that have to be added as well. Below is the step by step order I used to build it.

Name the Scenario.

Select Access Rights by Screen (SM20.10.20)

Select the Data Provider (also build from scratch)

 

*Target Object -> Field/Action Name*

Entities -> Path

EntityRoles -> Role (it will add 3 other fields automatically)

EntityRoles -> Access Rights

EntityRoles -> <Action: Save>

*you now have to add the 3 missing from the screenshot and move them to the correct order*

Entities -> <Parameter: NodeID>

Entities -> <Parameter: CacheName>

Entities -> <Parameter: MemberName>

 

This is the stage I had to add some extra columns to the screen to make sure they are filled in correctly. If you look at the screenshot I am missing data in a few of the Source Field/Value.

image

I don't know why those didn't carry over but that was a major issue on my end. I filled those in.

*Target Object -> Source Field/Value*

Path -> Path

Role -> Role

Access Rights -> Access Rights

 

The last step doesn't make much sense but following it allowed my import to work. You need to make sure the checkbox's are all checked EXACTLY as the screenshot. I had to uncheck "Commit" for Access Rights and uncheck "Active" for Role.

 

After following these steps I got it working. I have also updated Tim's Spreadsheet with some simple data validation to correct for reports having Granted & Revoked and all other screens have a larger set of options. Note if you leave them blank for any of the roles it should just stay as Not Set.

Hopefully this helps a few people.

Import User Security With Validation.xlsx
Reply
  All forum topics
  Previous Topic
Next Topic  
  Forum Statistics
11 Forums
1,702 Topics
7,858 Posts
8 Online
1,153 Members

Latest Post: Adding Formulas to Results Columns Our newest member: maxmce Recent Posts Unread Posts

Forum Icons: Forum contains no unread posts Forum contains unread posts

Topic Icons: Not Replied Replied Active Hot Sticky Unapproved Solved Private Closed

Online Members

Acumatica Forums

Terms of Use & Disclaimers :: Privacy Policy

Copyright © 2021 · AUG Forums, LLC. All rights reserved. This website is not owned, affiliated with, or endorsed by Acumatica, Inc.

‹›×

    ‹›×