|
|
Our Hot Pick: Rising Antivirus 2006 - Certified by TUV & Checkmark! Get 10% discount by entering this coupon code: ONDISCOUNT10
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?
|
|
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" wrote:
[Quoted Text] > 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?
|
|
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" wrote:
[Quoted Text] > 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" wrote:
>
> > 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?
|
|
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" wrote:
[Quoted Text] > 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" wrote:
>
> > 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" wrote:
> >
> > > 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?
|
|
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
"INTP56" wrote:
[Quoted Text] > 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" wrote:
>
> > 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" wrote:
> >
> > > 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" wrote:
> > >
> > > > 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?
|
|
|