Listbox querying a secondary data source based on a text box.

[Nick Hobart]

I am new to infopath and have developed a form that has users enter a value
in a textbox to retrieve/edit records relating to that value.

The form itself works fine this way. However, I would like add one more
"error proofing" item to the form.

I have created a table in my database that lists all userids and the values
that they should receive access. With only the two fields (userid,value)
there is no primary key as multiple users have access to the same value and
some users have multiple values.

I would like to have the user enter their id which would populate a
drop-down list box with those values they can access. Once this occurs they
can select the value and query the database like the original form allows.

Is this possible? If so, can someone direct me to a how-to resource?

 

[INTP56]

Nick,

First, I believe you do have a primary key in your 2-column table, it's the
combination of userid and value. Right? Does it make any sense to have the
same user with the same value more than once in that table?

To address your question. Have a variable named userid on your form, and
make it a drop down list, and populate it with the userid's from your table.
However, edit the SQL to say SELECT DISTINCT userid FROM yourtablename.

Then, have another dropdown list for values. Grab the data from your table
but after selecting the value field hit Filter data, and set the userid value
equal to the value of the field in the first dropdown box. Now you only get
the values associated with that userid.

If you use Windows authtication to link to the database, and you can make
sure the userid matches the domain username. Now you can create a view in the
database like

CREATE VIEW dbo.vUserValues AS
SELECT
value
FROM
dbo.yourtablename
WHERE
userid = SUSER_SNAME()

Now you don't need the userid field. You populate the values dropdown
directly from the view, and you will have a list of values appropriate for
whoever is logged in.

Hope this helps,

Bob

 

[Nick Hobart]

Bob,

I appreciate your response and it did give me a lot of insight.

Is it possible to have a text box entry for my userid instead of a drop down?

Nick

 

[INTP56]

Nick,

Sure ... I tend to use dropdowns to reduce typing, but fundamentally the
idea is the same, filter based on the value of the userid field.

Bob

 

[Nick Hobart]

Are you saying to add this other table in my main connection? When I do that
it disables my submit function saying that a one to many relation may exist.
What I am I doing incorrectly?

Nick