Using a lookup to get previous record values

[Simon]

I would like to have 2 dropdowns on a form. 1 with a list of companies, and
1 with a list of projects.
I would like the drop down list of projects to only show projects associated
to a company based on previous records created.
Not sure if that is possible to do - but it seems like it should be with
lookups - but I can't get it to work.

So when I click a company name in the company drop down on the form, the
project drop down gets re-populated with projects found for the selected
company.
something like
SELECT DISTINCT ProjectName FROM PreviousFormData WHERE Company =
<CompanyListBoxValue>.

 

[Fabrice BARBIN [Groove MVP]]

Yes.
It is doable.

I guess that you already have a form/view (let's call it "Company-Project")
in which you have linked a projects to a company

In a second form, add 2 drop-down list.

In the first drop down list, create a lookup on "Company-Project" and list
all companies.

In the second drop down list, create a lookup too :
1/ Check the "by key" option
2/ Choose the "Company-View" as source
3/ In the key column, choose the "Company" field (from the "Company-Project"
View)
4/ In the key value, choose the field corresponding to your first drop-down
list in the current form
5/ Check the "Key value is a field name" option
6/ In the "lookup column", choose the "Project" field (from the
"Company-Project" View)
7/ Check "Unique" to get distincts value
8/ Check "No cache" to be sure to have up-to-date results
9/ And finally click on "Apply"

It should fit your needs !

HTH
--
---------------------------------------------------------------
Fabrice BARBIN [Groove MVP - Groove MCTS]
http://fbarbin.spaces.live.com

Hommes & Process (www.hommesetprocess.com)
GrooveIT! (www.grooveit.biz)

 

[Rob Roy]

Fabrice - this is a good solution, and I have it working in Groove 3.1
across two tools in the same workspace, but I can't seem to get it working
between two tools in Groove 2007. Is there a trick that you know of? I have
checked the box for allowing a view to be used as a lookup between tools,
but whenever I create a lookup, the only option for tool is "This Tool".

Thanks for any advice you or others can offer.

"Fabrice BARBIN [Groove MVP]" <fabrice dot barbin at hommesetprocess dot
com> wrote in message

Yes.
It is doable.

I guess that you already have a form/view (let's call it
"Company-Project") in which you have linked a projects to a company

In a second form, add 2 drop-down list.

In the first drop down list, create a lookup on "Company-Project" and list
all companies.

In the second drop down list, create a lookup too :
1/ Check the "by key" option
2/ Choose the "Company-View" as source
3/ In the key column, choose the "Company" field (from the
"Company-Project" View)
4/ In the key value, choose the field corresponding to your first
drop-down list in the current form
5/ Check the "Key value is a field name" option
6/ In the "lookup column", choose the "Project" field (from the
"Company-Project" View)
7/ Check "Unique" to get distincts value
8/ Check "No cache" to be sure to have up-to-date results
9/ And finally click on "Apply"

It should fit your needs !

HTH
--
---------------------------------------------------------------
Fabrice BARBIN [Groove MVP - Groove MCTS]
http://fbarbin.spaces.live.com

Hommes & Process (www.hommesetprocess.com)
GrooveIT! (www.grooveit.biz)
---------------------------------------------------------------

I would like to have 2 dropdowns on a form. 1 with a list of companies,
and
1 with a list of projects.
I would like the drop down list of projects to only show projects
associated
to a company based on previous records created.
Not sure if that is possible to do - but it seems like it should be with
lookups - but I can't get it to work.

So when I click a company name in the company drop down on the form, the
project drop down gets re-populated with projects found for the selected
company.
something like
SELECT DISTINCT ProjectName FROM PreviousFormData WHERE Company =
<CompanyListBoxValue>.

 

[Fabrice BARBIN [Groove MVP]]

It should be OK with Groove 2007 as it was with Groove 3.1...

The only difference is that in Groove 2007 you have to explicitly authorize
a "source" forms tool/view to expose data to lookups requests coming from
other forms tools. In the form designer, in the settings and options window,
in the "feature options", check the "Allow this tool to provide data to
lookups from other workspace". You also have to open your View's designer,
click the "Option" tab, and then check "Allow this view to provide data to
lookups from other tools"....

With these 2 settings, it should be OK.

HTH.
--
---------------------------------------------------------------
Fabrice BARBIN [Groove MVP - Groove MCTS]
http://fbarbin.spaces.live.com

Hommes & Process (www.hommesetprocess.com)
GrooveIT! (www.grooveit.biz)
---------------------------------------------------------------

Fabrice - this is a good solution, and I have it working in Groove 3.1
across two tools in the same workspace, but I can't seem to get it working
between two tools in Groove 2007. Is there a trick that you know of? I
have checked the box for allowing a view to be used as a lookup between
tools, but whenever I create a lookup, the only option for tool is "This
Tool".

Thanks for any advice you or others can offer.

"Fabrice BARBIN [Groove MVP]" <fabrice dot barbin at hommesetprocess dot
com> wrote in message

Yes.
It is doable.

I guess that you already have a form/view (let's call it
"Company-Project") in which you have linked a projects to a company

In a second form, add 2 drop-down list.

In the first drop down list, create a lookup on "Company-Project" and
list all companies.

In the second drop down list, create a lookup too :
1/ Check the "by key" option
2/ Choose the "Company-View" as source
3/ In the key column, choose the "Company" field (from the
"Company-Project" View)
4/ In the key value, choose the field corresponding to your first
drop-down list in the current form
5/ Check the "Key value is a field name" option
6/ In the "lookup column", choose the "Project" field (from the
"Company-Project" View)
7/ Check "Unique" to get distincts value
8/ Check "No cache" to be sure to have up-to-date results
9/ And finally click on "Apply"

It should fit your needs !

HTH
--
---------------------------------------------------------------
Fabrice BARBIN [Groove MVP - Groove MCTS]
http://fbarbin.spaces.live.com

Hommes & Process (www.hommesetprocess.com)
GrooveIT! (www.grooveit.biz)
---------------------------------------------------------------

I would like to have 2 dropdowns on a form. 1 with a list of companies,
and
1 with a list of projects.
I would like the drop down list of projects to only show projects
associated
to a company based on previous records created.
Not sure if that is possible to do - but it seems like it should be with
lookups - but I can't get it to work.

So when I click a company name in the company drop down on the form, the
project drop down gets re-populated with projects found for the selected
company.
something like
SELECT DISTINCT ProjectName FROM PreviousFormData WHERE Company =
<CompanyListBoxValue>.