DMT - Remove company from existing user (UserComp)

Epicor ERP 10
1

I am using DMT with Powershell to provision users in Epicor 10 (multi-company) which works great.

The issue I have is when an existing Epicor user needs to be added to a different company and removed from a company. I cannot find a way to remove a company from the user account without deleting and re-adding the user.

Is it possible to remove a company from an existing Epicor user with DMT?

Thanks

2

Hi Dave,

Thereā€™s a field called CompList that holds a delimited list (ā€™~ā€™ separated) of the companies that the user has access to. There are also UserComp# records for each Plant. So you may need to do this in two runs. First, run a delete batch to remove the UserComp records for the old Company/Plants for deleting company and then (if DMT doesnā€™t do this automagically), remove the company id from the CompList field.

Mark W.

1 Like
3

You are mostly correct @Mark_Wonsil.

That field mentioned is a rollup. The real authority is SysUserComp - itā€™s a child of the SysUserFile table. Upon saving all the companies for the User, that field is summarized up into the concatenated string you mention.

Most people find it easier to just delete the SysCompRow - no parsing or mess and the code will run a few nanoseconds faster removing the row :wink:

1 Like
4

Hey, thatā€™s better than my record at homeā€¦

1 Like
5

Mark, thanks for the reply. In the testing I have done anytime I send a delete the entire user record is deleted. I am not aware of how to issue a ā€œDeleteā€ that only removes specific data and not the entire user.
I know for may fields I can send an update with a blank field to clear a field, but that doesnā€™t seem to work for the company field.

Bart, are you suggesting that I update a SQL table directly to remove the syscomprow? I am really hoping to only use DMT if possible.

Thanks,

6

Oh, @Bart_Elia would NEVER recommend direct editing of the databaseā€¦

If you add the new company first and then in a separate run delete the old company, does it still delete the whole record?

But, if itā€™s deleting the whole record including the user and other companies then it may be time to contact Epicorā€™s DMT Support about how to format the record to do this (and repost what you learn!)

Mark W.

1 Like
7

Thanks, In my testing it does always delete the user record. I do have a case open with Epicor but was hoping for a quicker answer.

Thanks again. I post any progress.

8

The normal process would be to mark the child table (SysUserComp) with a rowmod of ā€˜dā€™ and send up the whole dataset graph. It is rather verbose but works. There are smaller foot print approaches but it depends on the context of how you are using the server. From a customization, an integration, REST, etc.

And +1000 to @Mark_Wonsil 's comment on direct db access. ESPECIALLY UserFile which is wholly cached and replicated across app servers. FAST way to really mess yourself up is to deal with those tables via direct db access. I have had to hep unwind db tables before when a customer fried their db. NOT fun - for us or them.

1 Like
9

Bart, thanks for the info. I am very new to Epicor so I have to admit I donā€™t understand your post. I donā€™t see sysusercomp in the DMT user template so I am not sure what a romod is or a dataset graph.

Thanks,

10

Dave,

The RowMod of the dataset is part of the .Net programming framework. DMT hides this for you. However, for ā€œvery new to Epicorā€ big props to you for using PowerShell to automate tasks. A lot of people might be interested in your scripting savvy.

What version of Epicor are you running?

Mark W.

1 Like
11

This template will do it (Run it as Update)

image
image.png760Ɨ416 18.7 KB

Template: DMTUser.csv (113 Bytes)

2 Likes
12

As @Mark_Wonsil mentioned, itā€™s a status flag on a row as to how to process the row.
Think of the graph of data sent to a server- A DataSet, an XMLBlob, a JSON blob, it does not matter. How do you indicate the row is deleted or needs updating or is new? There are many options.

If I was starting from scratch it would have probably done something different but when I walked into the project of converting 9 to 10, there was 14 years of Vantage / E9 history and patterns and the RowMod A/U/D was well established. Itā€™s functional so no complaints.

Trying to convert it to something a little more modern like changing all the APIs or placing an attribute on a row or partial graph was not worth it. I am sure I would have been egged by this audience for breaking a lot of prior art if I had to be ā€˜architecturally pureā€™.

So historically the personality of E10 is having a RowMod column on every row as a ā€˜calculated fieldā€™ - it never goes to the db. Setting that value on any client is how you Indicate itā€™s desired processing. There are alternative APIs that are specific - DeleteByID is pretty self explanatory. For the normal processing patterns, set RowMod.

13

Jose, UserComp#RowMod was the piece I was missing. Running a file that adds a new company and deletes the old company using the rowmod is perfect, it gives me the exact results I need.

UserComp#Company,UserComp#CurPlant,UserComp#PlantList,UserID,Name,UserComp#RowMod
FCN,MfgSys,MfgSys,dwilhelm,Denis Wilhelm - dwilhelm,A
FIM,MfgSys,MfgSys,dwilhelm,Denis Wilhelm - dwilhelm,D

Thanks everyone for your help!!!

3 Likes
14

BTW - Grats on the automation via PowerShell. We do a huge amount of PS internally. Between PS for environment stand up and CURL against the REST services, you can make a very nice automation environment.

1 Like