Data from value list

[ianmuir]

Hi, I am trying to sort out our company Database.

We have a field called Company_name which is a drop down from a value list with all the company names in it.

I have a field underneath called Products.

what i need to be able to do is select a company name and the product field only shows the products for that particular company in a drop down list.

 

any ideas?

 

 

 

[Justin Close]

Ianmuir, 

    What a coincidence, I was just working through this problem earlier today.  Sounds like you want a dynamic value list:  you want to choose from a list of ALL companies, but then show only products related to that one company.  So if CompA has products (1A, 75B, 443C), and CompB has products ( 21U, 153Q, 847P), you want to have a pop-up that shows you "Company A", "Company B".  When you pick "Company A" from that pop-up, you want another pop-up to show you only "1A", "75B", and "443C".  Right?

 

There are two apsects to this task:  you need to create structure in order to develop two values lists - one for Companies, one for Products (the dynamic one).  Then you need to store those results somewhere.  There are a couple of variations on how to do this, but they are pretty close in structure.  I will outline the one with the fewest tables (but this means that it isn't the most normalized or data-pure structure).

 

Table:  Companies

   Fields:  CompanyID, CompanyName

 

Table:  Products

    Fields:  ProdID, ProductName, CompanyID_fk

 

Table:  Orders

    Fields:  OrderID, OrderDescription, CompanyID_fk, ProductID_fk

 

Relations:

    Orders::CompanyID_fk   <->  Products::CompanyID_fk

    (yes, the table 'Companies' doesn't need to be related for this to function)

 

Value Lists:

    "Companies": shows all values from table Company::CompanyID, show 2nd value from Company::CompanyName; could also only show 2nd value (if you want); but STORE only the first value (i.e. the CompanyID field).

    "Products":  values from field Products::ProdID, 2nd value Products::ProductName; show ONLY RELATED values when starting from "Orders" ; but STORE only the first value (i.e. the ProductID field).

 

Layouts:

     "Orders" [ based on table 'Orders' ]

     On layout include the fields: 

               OrderID  (extra)

               OrderDescription   (extra)

               Orders::CompanyID_fk ; format as pop-up list ; associate with "Companies" value list

               Orders::ProductID_fk  ;  format as pop-up list ;  associate with "Products" value list

 

That about covers it.  You don't really need the OrderID and OrderDescription fields on the Order layout for this to work, but they are really helpful for keeping records straight for humans.  You could put lots of other information in the Orders table as well.  And the Products and Company tables would also most likely have a lot of other information in them.  This is just the basic outline to get the dynamic value list functionality working.

 

Now, for the UI concerns of showing the CompanyName instead of the CompanyID, if you format the fields as pop-ups as described above, they should show you the CompanyName, even though the data in the field is an ID number.  You COULD just use the Name as the primary key in the table and in the relationship, but then you lose some flexibility with the company Name at a later date (e.g. if you wanted to change the name of a company). 

 

There are lots of nuances here.

 

--  J

[ianmuir]

Many Thanks. I did manage to sort it out!

[Justin Close]

Glad to hear it.