When the initial setup was completed for our AP automation, the approvers were setup using a user code in Epicor that used then the description 1 as the “group” and the email address to then identify the individual users. It’s a little overkill for where we have ended up. Our accounting team is now asking for the approvers to instead be the list of active users in ECM who can approve things. When I look at the approver block, it’s linked to the datalink that was create to use this user code from Epicor. I’d like to swap it to using the list of users based on the group that they are in in ECM so that there’s only one spot to manage it. Images below on how it’s currently setup:
We still then have to maintain a list in Epicor because the list is coming from an user code defined within Epicor itself, not ECM. Which means then having users setup in ECM to be able to access ECM for approving and adding them to the user codes.
You need to make a select that returns a column called LongDesc from where you want to get the list from. Where is the list of active users who can approve things?
ICE.UDCodes is the user defined codes table in Epicor.
I would prefer to call the group “APApprovers” that is found within ECM, then everything lives within ECM since if someone is added or removed then you add them or remove them from ECM itself.
I created a new datalink that removed anything related to the group ie. “CodeDesc = @Department” and am working with a copy of one of our workflows for testing. I redirected it to use the new datalink and it’s not returning just the list of email addresses. I have reassigned a document to the new workflow and restarted it a few times.
It just seems like extra IT management to have a distinct list in Epicor and also users to maintain in ECM for this to work.
We just need to be able to do a test live in datalinks for now. You can just make an _test datalink. I am assuming APApprovers is a role. I am looking at how to link Role to RoleMembership to UserID which is what I think you will need to make the list.
I have a connection called Approver_Matrix that points to the DocStar database. You will need to use one like that or make one for this datalink.
My approvers group has a space.
SELECT --r.name,u.*
u.Username as LongDesc
FROM [AAAA0001].[dbo].[ReadOnlyUserTable] u
inner join RoleMembership rm on u.Id=rm.UserId
inner join [Role] r on r.Id=rm.RoleId
where r.name = 'AP Approvers'
Thanks Greg. Our DB was a little different, the users were in a diff DB (Astria_Host vs AAA) so I did a cross join
SELECT
u.Username as LongDesc
FROM [Astria_Host].[dbo].[User] u
inner join RoleMembership rm on u.Id=rm.UserId
inner join [Role] r on r.Id=rm.RoleId
where r.name = ‘AP Approvers’
I’m late to the party, but as an alternative based on what you said initially… We don’t do any of the datalink stuff, we just have an ECM group and do it like this. Our ECM group is even connected to our Active Directory group, so we control group membership via AD. It’s not Department based or otherwise filtered (if you need that).
In the Action block, the task for “Select Approver” is
@MikeGross I am not a SQL person since I had to bug @Chris_Conn to help me. This way instead works great for those not wanting to setup a datalink and just want to avoid it
