Hey guys. I'm having an issue getting user's manager info. We use a custom system that houses manager information. Fortunately, all the data is kept in SQL tables, so I can use Dynamic SQL Service to get at it.
However, I have a Catch-22. There is one table in the system that stores AD Username information. It's called ControlRecord. It has very few columns, just User ID (a unique integer for each associate), UserCode (a string, the DOMAINusername of that associate, which is the information I'm after), and "Active" (integer representing whether or not that person is still employed). Both "Active" and "UserCode" are keys.
1) To find a user's manager, first I need his user ID (integer). Simple. Use Dynamic SQL Service's read method, with the input parameters "0" for "Active" (0 means active in our system), and "ProcessOriginator:Name" for UserCode. I then return the UserID column of the result into a data field in my process, and bam, I have the Originator's User ID.
2) Now I need their manager. Easy again. Another table, Associate, has the UserID as a key, and ManagerID as a regular column. Store the corresponding ManagerID in a data field, and I'm done.
3) Now I have a problem. I have the ManagerID... and need his UserCode (AD username). ControlRecord is the table that holds this info, but the UserCode is a key, so Dynamic SQL Service forces it to be a required field in the input mapping... and I need it as an output!
The table is very easy to query to get the info I need, but SQL Service's interface requires all keys as inputs, and I need to use a general column as an input, and pull back the key into a process data field.
Can I change which columns are used as inputs somehow? Note that I can't just change which one is the key in SQL, as I actually need to use UserCode of the originator as an input in (1).
Thanks for any help!