Avatar of askurat1
askurat1
Flag 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.

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
InfoPathMicrosoft SQL ServerC#

Avatar of undefined
Last Comment
askurat1

8/22/2022 - Mon
ASKER CERTIFIED SOLUTION
Ess Kay

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
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.
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
askurat1

ASKER
This help put me on the right track.