Hello list,
I am creating a db with Filemaker and I am quite disappointed since I
tought I would have found a good and easy managing of autocomplete and
dropdown lists on related tables. Maybe you can help me though.
Let's assume I have 2 tables: Devices and Restorations. The fields are:
Devices: Brand, Model, KindOfDevice
Restorations: Brand, Model, KindOfDevice, ......(many others)

I setup 3 relationships between these fields. I want to let the user to
enter the Brand Name and make him see suggestions in a drop down list
and use also the autocomplete feature. Then she would enter the model
name, with suggestions based just on a selection based on the brand.
Same with the last field. At the same time I enabled the check in "edit
relationship" that allow the creation of a Devices record from a
Restorations form.
I really can't achieve the fact that a value list for a field
dinamically shrink according to the other 2 fields. I need some advice,
thank you
Ale

I have been working on something similar in my inventory database.
First choices are Food Beverage or Other
If Food choices are Meat Produce Dairy Grocery
If Meat choices are Seafood Poultry Beef Pork

If I click on Food, I get the four choices. If I click on Meat I get
all third level choices which include beverage and other sub-choices.
TK

alek cosiac wrote:

File demonstrating the technique is available at
http://www.virtualvermont.com/FMP/DynamicVL.zip

Matt

In article <1161816152.167012.32070 (AT) k70g2000cwa (DOT) googlegroups.com>,
"alek cosiac" <alessandro.saccoia (AT) gmail (DOT) com> wrote:

Thanks,
but if I use this tecnique within my format I always get "This item
cannot be modified until 'model' is given a valid value", because I
want to let the user create a new 'device' record too. It looks like I
can't really achieve what I want. I did it with 4d, mysql, sql
server.... how come I can't do it with Filemaker?
ale

Matt Wills wrote:

Isn't it a matter of order of sequence? Before you could post an
invoice to a new vendor you would have to create the vendor, then when
you enter the invoice the drop down will show the vendor name.

Isn't this what your problem is? I might start entering an invoice to
find that the vendor isn't there when I look at the drop down, but I
script a button to go add that vendor and then return to adding the
invoice.

Wouldn't a sequence similar to this work for you?
TK

alek cosiac wrote:

In article <1161816152.167012.32070 (AT) k70g2000cwa (DOT) googlegroups.com>,
"alek cosiac" <alessandro.saccoia (AT) gmail (DOT) com> wrote:

I'm not sure where auto-complete comes into things. Unless it's an
addition in newer versions of FileMaker, you can't type into pop-up
list fields, so I can't see how you can auto-complete them.

But, ignoring that, setting up a three field dependcy is a little
tricky. You also have to be aware that using these types of
dependencies means that Finds have to be catered for with a separate
layout (see below) since the Value Lists via relationships don't work
in Find mode.

You've got your two files with fields:

Devices: Brand
Model
KindofDevice

Restorations: Brand
Model
KindofDevice

To BOTH of these tables you'll need to add another two fields, each of
which uses an auto-enter calculation:

AllBrands {Text}
Auto-enter Calculation
= "ALL"

BrandModel {Text}
Auto-enter Calculation
= If(Not(IsEmpty(Brand)) and Not(IsEmpty(Model)),
Brand & " - " & Model,
"")

In newer versions of FileMaker the BrandModel auto-enter Claculation
should be set to update itself. In older versions of FileMaker you're
basically stuck once you'ved entered both the Brand and Model -
auto-enter calculations won't change once entered, so if you make a
mistake you just have to delete the record and start again.


Into the Devices table you can now enter all the possible combinations
of Brand, Model and KindofDevice.
eg.
Record #1:
Brand = "Sony" Model = "Recorder" KindofDevice = "VCR"

Record #2:
Brand = "Sony" Model = "Recorder" KindofDevice = "DVD-RW"

Record #3:
Brand = "Sony" Model = "Console" KindofDevice = "PS2"

Record #4:
Brand = "Sony" Model = "Console" KindofDevice = "PS3"

Record #5:
Brand = "Microsft" Model = "Console" KindofDevice = "Xbox360"

etc.


Next, you need three Relationships in the Restorations table:

rel_Brand
Restorations -> Devices when AllBrands = AllBrands

rel_Model
Restorations -> Devices when Brand = Brand

rel_KindofDevice
Restorations -> Devices when BrandModel = BrandModel

and from these three Value Lists:

vl_Brand
From field via Relationship rel_Brand::Brand

vl_Model
From field via Relationship rel_Model::Model

vl_KindofDevice
From field via Relationship rel_BrandModel::KindofDevice


Now you can set-up the fields in the Restorations table.

Onto the Layout put the Brand field and format it as a pop-up list
using the vl_Brand Value List. Then the Model field and format it as a
pop-up list using the vl_Model Value List. Finally the KindofDevice
field and format it as a pop-up list using the vl_KindofDevice Value
List.

All done!! )

When a user clicks on the Brand field, the list of all Brands from the
Devices table will be displayed. They choose "Sony" (using the example
Devices records above), which completes the second Relationship. When
they then click on the Model field it will then display a list of just
the Sony Models (ie. "Recorder" and "Console"). They choose "Console"
and the field BrandModel is automatically given the value of "Sony -
Console" which completes the third Relationship. When the user then
clicks on the KindofDevice field it will display a list of just the
Sony Console devices (ie. "PS2" and "PS3").

Obviously, if the user tries to enter data into these fields in any
other order (ie. clicks first on the Model field) nothing can be
displayed since the higher level field is empty and the Relationship
has no connection to pick up the appropriate values to display.


To be able to perform Finds, you need to create another Layout. On this
"Find layout" you can either have these fields are set-up to display
every possible value, which means creating three more Value Lists:

vl_ALLBrands
From field via Relationship rel_Brand::Brand

vl_ALLModels
From field via Relationship rel_Brand::Model

vl_ALLKindsofDevices
From field via Relationship rel_Brand::KindofDevice

OR you can create your own fake-Find mode that utilises Global field
and the original Value Lists and then script the processing of the
Find.





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