I want to create a Value List from a related table using only some of the records based on examining fields in the related table.

The create Value List dialog box only has 3 options:

1). Use values from field

2). Use Value List from another file

3). Use custom values

In order to create this list I would have to examine fields from the related file to determine if that record should be included in the Value List. Any thoughts will be greatly appreciated!.......Ray

Edited April 19, 200718 yr by Guest

You will want to use option 1, Use values from field, and then use the the include only related records option. You will need to devise a relationship that gives you the records you are looking for and select it here.

Edited April 19, 200718 yr by Guest

Many thanks for your help Greg...........Ray

I finally found time to create this relationship and the associated Value List. It works fine with the first related field (FieldA = FieldB) but when I add the second field (DateA => DateB) the Value List is blank.

The relationship graph is showing a (X) between the two tables........

Any thoughts will be greatly appreciated........Ray

It should be showing a [X] (multicriteria) not a X (cartesian). Looks similar but not the same.

Are both DateA and DateB properly setup as date fields?

Hi John, the DateA field type is "Date" and is never blank. The DateB field type is "Date" and is auto-entered with "Get(CurrentDate)." I really want to compare it to today's date.

The symbol between the two tables is [X] as best I can tell. I am not familiar with the cartesian character....Many thanks for your help...

John, I have narrowed the problem down to the date relationship and am working on it. I will report back with my findings.

Get(CurrentDate) will never update an Auto-Enter date field. It will often not update a Calculation field, unless you set the field to not store results, but recalculate when necessary. In half the situations (depending on which side of the relationship you're evaluating from) an unstored calc will not function in a relationship. Bit of a bind. It could work, but takes a bunch of tweaking.

Instead I recommend creating a global field that is Set to get(CurrentDate) on File open.

DJ

Hi David, thanks for your thoughts, I was slowly coming to this conclusion. I will use your advice and set up a Global field that I update each time the file opens............Ray