AUGForums.com

An Acumatica User Group

  • Free
    • Start Here
    • Rolodex
    • Podcast
    • Blog
    • Forums
  • Course
  • Consulting
  • Login
  • 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

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).
Sincerely,
Tim Rodman

#AcumaticaTnT Importing Acumatica User Roles / User Security Permissions from Excel

Everything Else
Last Post by Eva Yang 2 years ago
6 Posts
4 Users
2 Likes
2,264 Views
RSS
Tim Rodman
Posts: 2969
 Tim Rodman
Admin
Topic starter
August 19, 2020 9:41 am
(@timrodman)
Member
Joined: 8 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.

5 Replies
AhmedBahar
Posts: 29
 AhmedBahar
January 20, 2021 2:38 pm
(@ahmedbahar)
Member
Joined: 3 years 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
(@adam-mcguinnes)
Joined: 2 years ago

Member
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
(@adam-mcguinnes)
Joined: 2 years ago

Member
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
AhmedBahar reacted
Tim Rodman
 Tim Rodman
Admin
(@timrodman)
Joined: 8 years ago

Member
Posts: 2969
March 19, 2021 5:04 pm
Reply toAdam-McGuinnesAdam-McGuinnes
AhmedBahar

Nice @adam-mcguinnes! Thanks for sharing!

Reply
Eva Yang
Posts: 1
 Eva Yang
July 31, 2021 1:35 am
(@eva-yang)
Member
Joined: 2 years ago

Hello all, 

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!

image

Hopefully this helps 🙂

 

Reply
Tim Rodman reacted
Forum Jump:
  Previous Topic
Next Topic  
Forum Information
Recent Posts
Unread Posts
Tags
  • 11 Forums
  • 2,268 Topics
  • 9,982 Posts
  • 2 Online
  • 1,834 Members
Our newest member: Derek Wong
Latest Post: Change the number of rows in the check remittance field
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

 No online members at the moment

Acumatica Forums

Terms of Use & Disclaimers :: Privacy Policy

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

‹›×

    ‹›×