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.

tim
-------------------------------
Departments: la

john
-------------------------------
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.

Thanks,
Tony

ASKER CERTIFIED SOLUTION

Ess Kay

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Ess Kay

heres an example how to call an sql query and fill a dataset
http://stackoverflow.com/questions/9911742/a-single-sql-query-to-return-as-multiple-tables-in-a-dataset

the rest is your job
hope that helps

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

}

}

Clay Fox

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

hide

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

askurat1

ASKER

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.

@clayfox
Yes, I have tried using username() but not sure how to compare to a SQL table field.

askurat1

ASKER

This help put me on the right track.