ExpertCentral.coman About company
Your Search is Over!
Expert Home Sign Up My ExpertCentral Answer Library Help
Search for Experts in 
View question by Expert saintly
Question History!
From : anavat
To : saintly
User Comment : very helpful answer, thank you
Rating :
Message Status : Confidential

[08-22-2000] anavat : I have a problem with FileMaker Pro 5. I am trying to create fields in drop down list style. I want to have a drop down list of the first field to be product list and then second field to be colour. What I am trying to do is that the lists of colour in colour field depends on the selection of the product field. For example if select product 1 may have drop down list of blue, red and yellow. If select product 2 may have drop down list of green and white.
I tried to use define value list but I don't really know how? It can only get the value from other fields.
Please give me some suggestion, Thank you
[08-23-2000] saintly : (Attachment)
Wow! And this sounded like a simple question at first. It's amazingly difficult to do the way you're thinking it should work. There are some significant problems with trying to manipulate the contents of value lists.

1. Value lists can only either be predefined and unchangeable or "use the value of a field".
2. If you use the value of a field, it CAN'T be a calculation or a global (allowing you to change it easily) or a 'related' field in another file (allowing you to look it up and change it that way).
3. If you use a regular field, then the value list contains everything in every instance of that field throughout the file. You can't just change the contents of the field for your record and change the value list (unless it is the only field with data, it adds to all the other fields).

I ended up using three (!) files to do it, and even then you have to push a button (run a script) to make the second drop-down list change. If that's okay with you, here's how I did it.

File 1: Has the two fields that you want to do the drop-down thing on. I called one field "Product" and the other "Options". There is a third, Global (text) field called "Scratch". I defined two value lists. One is called "Product-list" and has the values "PRODUCT A" and "PRODUCT B" in it. The other is called "Option-list" and is set to "Use the value of a variable" which is File 3, Scratch. I defined one relationship to File 2 using both the fields named "Product" as the relationship fields. There is a hidden (white-on-white font), non-printing, non-editable Scratch field.

File 2: Has two fields "Product" and "Option Values". I set up two records. One record has "PRODUCT A" for the product and "RedGreenBlue" for the Option Values. Option values is three lines. The other record has "PRODUCT B" for the product and "BlackWhiteGreen" for the Option Values.

File 3: Has a single record with a single field in it, called "Scratch".

There is a button in File 1 called "Foo". When it is pushed, it sets the value list for the field called "Options". Unfortunately, there were several problems involved with making it so you could just tab over to the next field and it would be set up. You have to make a choice in the first field, hit the "Foo" button and then make a choice in the second field.

The script looks up the options in File 2 using the data chosen in the "Product" field, puts it into a scratch variable, copies it out to the Scratch file (using a second script there that just pastes it in), then clears and positions the cursor at the "Options" field.

It sounds really complicated right? I've attached a .zip file containing the three database files I used. You can look at the "Main.FP3" file to see how it is put together. It has the main script and the fields that it is trying to change. You can look at the layout, relationships, value lists and scripts to see how they are set up.

I have FileMaker Pro 4.0. There *might* be an easier way in FileMaker Pro 5, but after reviewing the documentation available for it, I still couldn't see any new support that may help. Try to make a field called "Calculation" and set the Calculation to be (including the quotes)
Then try to set the value list to use the Calculation field for "Use the variable...". If that makes a working pull-down with "Green" as the only option, then you're in business. Write back and I'll help you set up a value list that changes based on what the first value list is. If that doesn't work, then sorry... this is about the best option I could make.

Hope that helps!
[08-23-2000] anavat : Thank you for your effort, now I can get around that problem by creating new file with product field and colour field. And use define value list from this new file( related file). I ma not sure that this is only new function for version 5. But it's work.
I would like to ask your opinion that what is the advantage of FileMaker 5 ocer Access 2000. Eg, Flat file system VS Relational file and SQL support. I need to select one software for small task in department with maximum 6 users to access the database.

Thank you very very very much
[08-23-2000] saintly :
I don't think the Access vs. Filemaker is really a question of which is better. It depends on what you want to use it for. Filemaker is a full relational database with powerful scripting capabilities, runs well on both Mac and PC, has server modes to share files with other users on the network, and straight-to-web access as well. On the other hand, Access works better with other products in he MS Office suite. You can build standalone products on top of access databases easier. The Access interface looks like MS Office tools that may be familiar to Office users. SQL works better in Access than in Filemaker.

You have to decide what you want to do and use it for. If it's to make Microsoft-looking products or applications to run in Windows, then you probably want Access. If you want more powerful features or cross-platform compatability, then you probably want Filemaker.
Home | Sign Up | My ExpertCentral | Answer Library | Help | Log Out
Public Board | How it Works | Why Join? | Tell a Friend | About Us | Contact Us

Copyright © 2000, Inc. All Rights Reserved.
ExpertCentral and are trademarks of, Inc.
Use of this site constitutes your acceptance to the terms and conditions of the ExpertCentral Member Agreement.