AnsweredAssumed Answered

Custom Value List with Related Fields

Question asked by dvarsam on Apr 6, 2010
Latest reply on Apr 6, 2010 by philmodjunk


Custom Value List with Related Fields




I have downloaded the Trial version of FileMaker Pro.

I have managed to connect to MS SQL Database.


I am evaluating whether I can use "FileMaker Pro" in my business.

I am experiencing the following difficulty:


Tables are shown here:



I have created a Layout with the following Fields:


a. CompanyCode (i.e. Drop-down with company.codeid & values where comid is saved into table "adv_machines_fintrade.comid" )

b. CustomerCode (i.e. Drop-down  with customer.code & values where code is saved into table "adv_machines_fintrade.CusCode" )

c. CustomerName (i.e. a Plain Field which just displays the Name of the Customer depending on what I select on step "b" above.


Sample Data:


Table Company:

1. Value "1" (means Company named "Test")

2. Value "2" (means Company named "Coca-Cola")

3. Value "3" (means Company named "Pepsi")

4. Value "4" (means Company named "Hot Springs")

5. etc. etc.


Table Customer:

1. Values "2", "01256", "Tony", etc. (means on Company "2", Customer  with number "01256" is Mr. "Tony")

2. Values "3", "01256", "Tony", etc. (Mr. "Tony" is also a "Pepsi" Customer)

3. Values "4", "01256", "Tony", etc. (Mr. "Tony" is also a "Hot Springs" Customer)

4. Values "2", "00909", "Bob", etc.

5. etc. etc.


Table adv_machines_fintrade:

This table is connected on the Layout Fields I have created...

All my data is saved on this Table.




1. On the above "a" (CompanyCode) Drop-down I want to be able to exclude from the Dropdown "where comid=1" because such a company does not exist on my Database (data is junk & I can not Delete).

Is there a way I can exclude values from the Drop-down menu by adding a command "where comid<>1"?


2. If on the Layout I have created, I decide to make an Entry for Comid=2 (Company 2="Coca-Cola" company), when I am to select the Customer.Code, the Drop-down should ONLY show Customer Codes that exist for that Company 2 (i.e. for the "Coca-Cola" company).

Example: If on step "a", I chose company=2, then the customer Drop-down should NOT show Table Customer Records 2 & 3.

How can I create a "conditional Drop-down list" depending on a separate Layout Field (i.e. comid)?


3. When on above question "2", I select Customer Code, I want to know on which customer I am about to write the order for, i.e. If I select customer code "01256", I want to be able to SEE that this customer is Mr. "Tony" - I can't memorize all customer Codes-Names!!!

So this field outputs the Name of customer depending on the Code I have typed in above Question 2.

And this Field must be locked for Editing (only viewing is allowed).

How can I do that?


Any help is appreciated.