Related Records and Repeating Fields

[rayj]

Hi, I am trying to create a dynamic value list using a related table but have a bit of a twist. Here’s the scenario:

Two tables: Products and Invoices

What should happen:

Invoices table – user enters in a product ID, the product description is pulled in from the products table using the product ID as the key field, the user then needs to select the color of that product through a pull-down menu. The colors that are available for that product are generated through a value list using the related records option.

The twist: In the Products table, there is one record for each product (i.e. there’s no duplicate records for each product); each product has a product ID, description and color field. The color field is a repeating text field. The problem is when I set up the value list, it will show all of the colors from all of the records not just the colors from the repeating field in the one related record.

Suggestions?

[comment]

Define the value list to include only related values, starting from Invoices.

[rayj]

Yes, that's the way it should work but it doesn't seem to do the trick. Here's how it's set up:

INVOICES

Product ID (text) - key field

Description (related field to PRODUCTS)

Color (text, value list related to PRODUCTS)

PRODUCTS

Product ID (text) - key field

Description (text)

Color (text, repeating field)

I have two relationships set up, Product 1 and Product 2, both using the Product ID as the key fields.

In the value list setup, it's set to use related values from the Color field in Products. In the value list dialog box, the top portion is set to "Use values from the first field" = Product 1 with the Color field highlighted. In the lower portion of the window, it's set to "Include only related values starting from" = Product 2. (I tried using only a single relationship (Product 1) but I can't select the same defined relationship in both the top and bottom value list settings.)

With the above settings, it's pulling into the value list all of the colors from all records in Products. What I want it to do it to only pull the colors in the repeating Color field in Products from the one related record not all!

The procedure should be very straightforward but it doesn't seem to be working.

[comment]

I got lost in your description.

You only need one relationship:

Invoices::ProductID = Products::ProductID

The value list Colors should be defined to use values from field Color in the Products table. No second field is required.

At the bottom, select "Include only related values" starting from Invoices.