Related drop down field when another field is selected

Hi all,

FM Pro 9 adv. Mac OS-X Leopard

I have a database where I have two fields.
One called Area, the other called Sub group Area.
I have a fixed 43 Areas each with a sub group of 4 or 5 items.

The field Area has a drop down box where you select one of the 43 Areas.
So I select value KRA. Now I go to another field called Sub group area and
a drop down box should appear with only the related values for "KRA".
Those choices are Undergrad, Masters, PhD, & Executive.

An example is where I put in the field Area (drop down select) "CFS".
Now I should be able to go to the Sub group area field and select from that
drop down box any of these values: CDFS, CSR, F&N, & HTM.

In essence that same Field Area needs to change and its related Sub group
area field needs to appear so as not to have a long value list.

I tried to figure it out using self-join relationships but my tables are
huge with other field values.

Can someone explain to me how to set this up or have a better way than using
Self-Join relationships.
Consequently do I have to have sub group area's of each Area in its own
table?

I thought about scripting it to match Area to related Sub group, but that
seems to be out of my league too.

Thanks for any suggestions.

Re: Related drop down field when another field is selected

In article , Robert
wrote:

> Hi all,
>
> FM Pro 9 adv. Mac OS-X Leopard
>
> I have a database where I have two fields.
> One called Area, the other called Sub group Area.
> I have a fixed 43 Areas each with a sub group of 4 or 5 items.
>
> The field Area has a drop down box where you select one of the 43 Areas.
> So I select value KRA. Now I go to another field called Sub group area and
> a drop down box should appear with only the related values for "KRA".
> Those choices are Undergrad, Masters, PhD, & Executive.
>
> An example is where I put in the field Area (drop down select) "CFS".
> Now I should be able to go to the Sub group area field and select from that
> drop down box any of these values: CDFS, CSR, F&N, & HTM.
>
> In essence that same Field Area needs to change and its related Sub group
> area field needs to appear so as not to have a long value list.
>
> I tried to figure it out using self-join relationships but my tables are
> huge with other field values.
>
> Can someone explain to me how to set this up or have a better way than using
> Self-Join relationships.
> Consequently do I have to have sub group area's of each Area in its own
> table?
>
> I thought about scripting it to match Area to related Sub group, but that
> seems to be out of my league too.
>
> Thanks for any suggestions.

A self-join Relationship is probably not what you need - they obtain
data from records in the same Table. These can be useful for
calculating sub-totals or counting the number of records with the same
data.

What you need is a separate Table and a normal Relationship to that.

First you need a new "Areas" Table with just two fields: key_Area and
option_SubArea. In this Table you need to create records for every
possible combination of Area and Sub Area.
eg.
Record 1: key_Area = KRA option_SubArea = Undergrad
Record 2: key_Area = KRA option_SubArea = Masters
Record 3: key_Area = KRA option_SubArea = PhD
Record 4: key_Area = KRA option_SubArea = Executive
Record 5: key_Area = CFS option_SubArea = CDFS
Record 6: key_Area = CFS option_SubArea = CSA
Record 7: key_Area = CFS option_SubArea = F&N
Record 8: key_Area = CFS option_SubArea = HTM
etc.

Now you can create a Relationship from the main Table to the Areas
Table based o the Area field.
eg.
rel_SubAreas Match records in Main Table with Areas Table
when Area = Areas::key_Area

Then you can create a new Value List that obtains its value fomr this
Relationship.
eg.
vl_SubAreas values from field, only related values
rel_SubAreas::option_SubArea

Finally set the Sub Areas field in the Main Table to use this Value
List.

All done.


There are two problems with this kind of set-up though.

Users must enter data into the Area field first, otherwise the Sub Area
field doesn't know what values to display. Not a huge problem as long
as users understand this.

A bigger problem is that such a set-up does not work in Find mode. When
in Find mode Relationships don't function because the request "data"
doesn't officially exist, so FileMaker again can't display anything in
the Sub Areas field for users to choose from. The best way around this
is to have a second Layout that is used for Find mode, and either:

- let users manually type in the Sub Area

or - use a second Value List that retrieves ALL the Sub Areas

or - use Global fields with a second Relationship and a fake
"Find" mode that is really scripted in Browse mode.



Helpful Harry
Hopefully helping harassed humans happily handle handiwork hardships ;o)

Re: Related drop down field when another field is selected

On 4/3/08 2:16 AM, "Helpful Harry" wrote:

> In article , Robert
> wrote:
>
>> Hi all,
>>
>> FM Pro 9 adv. Mac OS-X Leopard
>>
>> I have a database where I have two fields.
>> One called Area, the other called Sub group Area.
>> I have a fixed 43 Areas each with a sub group of 4 or 5 items.
>>
>> The field Area has a drop down box where you select one of the 43 Areas.
>> So I select value KRA. Now I go to another field called Sub group area and
>> a drop down box should appear with only the related values for "KRA".
>> Those choices are Undergrad, Masters, PhD, & Executive.
>>
>> An example is where I put in the field Area (drop down select) "CFS".
>> Now I should be able to go to the Sub group area field and select from that
>> drop down box any of these values: CDFS, CSR, F&N, & HTM.
>>
>> In essence that same Field Area needs to change and its related Sub group
>> area field needs to appear so as not to have a long value list.
>>
>> I tried to figure it out using self-join relationships but my tables are
>> huge with other field values.
>>
>> Can someone explain to me how to set this up or have a better way than using
>> Self-Join relationships.
>> Consequently do I have to have sub group area's of each Area in its own
>> table?
>>
>> I thought about scripting it to match Area to related Sub group, but that
>> seems to be out of my league too.
>>
>> Thanks for any suggestions.
>
> A self-join Relationship is probably not what you need - they obtain
> data from records in the same Table. These can be useful for
> calculating sub-totals or counting the number of records with the same
> data.
>
> What you need is a separate Table and a normal Relationship to that.
>
> First you need a new "Areas" Table with just two fields: key_Area and
> option_SubArea. In this Table you need to create records for every
> possible combination of Area and Sub Area.
> eg.
> Record 1: key_Area = KRA option_SubArea = Undergrad
> Record 2: key_Area = KRA option_SubArea = Masters
> Record 3: key_Area = KRA option_SubArea = PhD
> Record 4: key_Area = KRA option_SubArea = Executive
> Record 5: key_Area = CFS option_SubArea = CDFS
> Record 6: key_Area = CFS option_SubArea = CSA
> Record 7: key_Area = CFS option_SubArea = F&N
> Record 8: key_Area = CFS option_SubArea = HTM
> etc.
>
> Now you can create a Relationship from the main Table to the Areas
> Table based o the Area field.
> eg.
> rel_SubAreas Match records in Main Table with Areas Table
> when Area = Areas::key_Area
>
> Then you can create a new Value List that obtains its value fomr this
> Relationship.
> eg.
> vl_SubAreas values from field, only related values
> rel_SubAreas::option_SubArea
>
> Finally set the Sub Areas field in the Main Table to use this Value
> List.
>
> All done.
>
>
> There are two problems with this kind of set-up though.
>
> Users must enter data into the Area field first, otherwise the Sub Area
> field doesn't know what values to display. Not a huge problem as long
> as users understand this.
>
> A bigger problem is that such a set-up does not work in Find mode. When
> in Find mode Relationships don't function because the request "data"
> doesn't officially exist, so FileMaker again can't display anything in
> the Sub Areas field for users to choose from. The best way around this
> is to have a second Layout that is used for Find mode, and either:
>
> - let users manually type in the Sub Area
>
> or - use a second Value List that retrieves ALL the Sub Areas
>
> or - use Global fields with a second Relationship and a fake
> "Find" mode that is really scripted in Browse mode.
>
>
>
> Helpful Harry

Thanks Harry!

My question is where does rel_SubAreas and vl_SubAreas come from?

Where you have:

>>Now you can create a Relationship from the main Table to the Areas
>>Table based on the Area field.
>>eg.
rel_SubAreas Match records in Main Table with Areas Table
when Area = Areas::key_Area

>>Then you can create a new Value List that obtains its value from this
>>Relationship.
>>eg.
vl_SubAreas values from field, only related values
rel_SubAreas::option_SubArea


After creating the new Areas table with the two fields and adding in the 8
records for testing purposes, how do I create the relationship between the
main table to the areas table? I need to go to the relationships tab in
Manage database and select my area field from the main table and tie it to
the Key_area in the Areas table - correct? That's the rel_SubAreas?
Then when I go to a layout with my subgroup field, I am suppose to assign
the field using field/control setup displaying values from ?.

Looks like I am lost at getting relationships.... More help please. Thanks
again.

Re: Related drop down field when another field is selected

In article , Robert
wrote:

> On 4/3/08 2:16 AM, "Helpful Harry" wrote:
>
> Thanks Harry!
>
> My question is where does rel_SubAreas and vl_SubAreas come from?
>
> Where you have:
>
> >Now you can create a Relationship from the main Table to the Areas
> >Table based on the Area field.
> >eg.
> > rel_SubAreas Match records in Main Table with Areas Table
> > when Area = Areas::key_Area
> >
> >Then you can create a new Value List that obtains its value from this
> >Relationship.
> >eg.
> > vl_SubAreas values from field, only related values
> > rel_SubAreas::option_SubArea
>
> After creating the new Areas table with the two fields and adding in the 8
> records for testing purposes, how do I create the relationship between the
> main table to the areas table? I need to go to the relationships tab in
> Manage database and select my area field from the main table and tie it to
> the Key_area in the Areas table - correct? That's the rel_SubAreas?
> Then when I go to a layout with my subgroup field, I am suppose to assign
> the field using field/control setup displaying values from ?.
>
> Looks like I am lost at getting relationships.... More help please. Thanks
> again.

rel_SubAreas and vl_SubAreas are just the names I gave to the
Relationship and the Value List definitions.

I'm not sure, but it sounds like you have got the Relationship defined
correctly. Once you've defined the Relationship you then need to define
a new Value List. This is done in the same way as for the Area pop-up
list field you already have, but this time the Value List uses the
"from field" option and "only related" with the new Relationship to
obtain the data from the option_Areas field.

Then on the data entry Layout you can Format the SubArea field to be a
Pop-up List using this Value List.


Helpful Harry
Hopefully helping harassed humans happily handle handiwork hardships ;o)