Access SQL database through web portal

Question

I have filled CustomAttributeText1 with a comma delimited list of numbers for a user. These numbers are unique identifiers for locations stored in out MS SQL database table.
I want to be able to get this list when I select the user from a drop down and use it to query a MS SQL database table to match it with the location names. I then want to display these location names on the web portal so that web portal users can see the locations that the user is tied to.
Is there a way for me to do this kind of customization to the web portal? Can you direct me towards tutorials for this sort of thing?

Thanks!

Edit: Another thought on how to handle this:

Is there a common place in AD where we could store a delimited list of key/value pairs? We are thinking that we might just store all of the location ID’s and location names in AD in one place and a list of location ID’s on the user object in the CustomAttributeText1 field. We could display a picklist with all of the locations and mark the locations that the user is tied to. If a new location is selected or an old location is deselected we could add or remove the location ID’s from the user’s list.
The main list of locations in AD would be kept up to date by a scheduled script and the locations tied to each specific manager would be pulled into our SQL database tables by a different scheduled script.

Answer 1

Hello Mark,

There is no common place in Active Directory where you might store the list of locations and their IDs. As a solution, you can use Property Patterns and one of Adaxes custom multi-value attributes (e.g. CustomAttributeTextMultiValue1). The list of allowed values for the attribute will be formed by a PowerShell script that will be querying your MS SQL database on a schedule basis. The values in the attribute will have the following format: locationName (locationID). This will allow you to have only one attribute storing both location names and identifiers instead of two. However, the drop-down list of locations will always be the same. There is no possibility to mark or exclude locations that are already selected for the user. When the attribute will get updated for a user, a Business Rule will query your MS SQL database and make required changes (assign/unassign the user to locations).
If this solution meets your needs, we will provide you with detailed instructions.

Comments

Since we would not be able to use the web portal to make changes to the list of locations for a user this approach won't work. We are now looking at using security groups as locations and add users to those groups.