Link to home
Start Free TrialLog in
Avatar of askurat1
askurat1Flag for United States of America

asked on

autofill fields by getting current user and comparing to SQL table

What I am trying to do is populate a field based on the current user.
The field I am trying to populate is pulling from a SQL database.

For example, I have one field named Departments. That field is going to pull the department names from a SQL database table but it is only going to get the departments that current user has access too.
So I currently have a data connection pulling the info I need from the SQL table:

Name       Username
cen           john
cen           bob
chi            john  
chi            bob
la              john
la              bob
la              tim

So as you can see from the table john has 3 departments, bob has 3 departments, and tim has 1 department.

Let's say john is filling out the form, the departments field will only display his 3 departments. If tim is filling out the form it will only show his 1 department in the field.

Departments: la

departments: cen, chi, la

Now I now how to compare data in info path but not between SQL and the current user.

If I have to do some programming that is fine. I am just curious if this is even possible.

Avatar of Ess Kay
Ess Kay
Flag of United States of America image

Link to home
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
heres an example how to call an sql query and fill a dataset

the rest is your job
hope that helps

using (var sqlConn = new SqlConnection("Server=localhost;Database=Test;Integrated Security=SSPI"))
                string sql = "Select * From table1; Select * From table2;";
                using (var sqlCmd = new SqlCommand(sql, sqlConn))
                    var da = new SqlDataAdapter(sqlCmd);
                    var ds = new DataSet();
                    Console.WriteLine(ds.Tables.Count); // Will show 2 !


Are you using the username() function?  how are you getting the user name.

First, put the field containing username on your form to verify

Then put the data connection data from the department list on your view as a repeating table.

add a formatting rule to the repeating table.

set condition to username not equal to name in list


this should then only show you the department records from the list which match the username.
Avatar of askurat1


Thanks for the reply. I need a bit more help then that.

How am I suppose to determine the current user, the windows user filling out the form, then display that user's departments?

I am new to c# and infopath so bare with me.

Yes, I have tried using username() but not sure how to compare to a SQL table field.
This help put me on the right track.