AnsweredAssumed Answered

Custom Value List with Related Fields

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

Title

Custom Value List with Related Fields

Post

Hello.

 

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:

[IMG]http://i69.photobucket.com/albums/i58/dvarsam/FileMaker%20Pro/FileMakerProTables.jpg[/IMG]

 

I have created a Layout with the following Fields:

 

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

b. CustomerCode (i.e. Drop-down  with customer.code & customer.name 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.

 

Questions:

 

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.

 

dvarsam

 

Outcomes