Conditional Value List

[Tracy]

I'm working in FM11 and trying to figure out a conditional value list issue. Let me setup of what I have and what i want to do:

Table#1 - called "Main"

1) In this table, I have a text field called "Product Code" which is also a drop down list consists of "GC" & "VSC".

2) In this table, I have another text field called "Material List".

 

Table#2 - called "Material Archive"

1) In this table, I have a text field called "Materials Available" which has 20 records created.

2) In this table, I have another number field called "Serial1" where 11 of the 20 material records are earmarked with a "1" in the Serial1 field.

3) In this table, I have another number field called "Serial3" where 14 of the 20 material records are earmarked with a "3" in the Serial3 field.

          *5 records consists of both Serial1 = 1 & Serial3 = 3 on the same record.

 

What I'm trying to figure out is how to populate a drop down list from the "Material Available" records in Table#2 in the "Material List" field in Table#1?

So if, Product Code = "GC" on Table#1, then I want the "Material List" field on Table#1 to display a dropdown list of the "Materials Available" records from Table#2 marked with

Serial1 = 1. But, If I select Product Code = 3, then the records marked with Serial3 will be displayed. I can display one or the other through the relationship, but I can't figure out

how to display one or the other in the same field (Material List)?

Thanks,

Tracy

[Tracy]

Also, I know I could combine Serial1 & Serial3 into one Serial field and it will work, but I don't know what to do when the there are multiple serial numbers for the same record. I simplified descibing my issue when in reality, I have over 28 different ID numbers designation (1 thru 28) randomly across three different serial fields in Table#2 where I have over 2,000 "Materials Available" records. So, there is alot of records with multiple Serial1, Serial2, & Serial3 numbers on the same material record.

 

Let me know if I'm not setting this up correctly and should be only using one Serial field in Table#2.

Thanks,

Tracy

[comment]

28 minutes ago, Tracy said:

I don't know what to do when the there are multiple serial numbers for the same record.

Assuming that all serial numbers are of the same kind, you have two options: (a) enter them as multiple records in a related table; or (b) enter them as a return-separated list into a text field. Note that a field formatted as a check-box set stores the checked values as a return-separated list. If all your records contain some combination of 28 possible values, this could be a convenient solution - albeit with some limitations on reporting.

I am afraid I did not understand the rest of your description. Please do not use abstract names like "Table#1" or "Main". Describe what does a record in each table represent in real life.

 

[Tracy]

See the attached document. I tried to explain more if it helps. Let me know.

Example.doc

[Tracy]

I figured it out. I consolidated the ID field into one by making ID1 a repeating field. This way, I can add the ID#'s from 2&3 into #1. It work perfectly.