Need help with related fields...

[mudrunner]

Good Day,

I'm having some difficulties. First off, I want to have a field on my primary layout which allows me to either select a value from the corresponding field on another table or, if the desired value is not available, be able to add it by typing it in the field on the primary table. This seems like a simple process, but I'm not getting the results I want. Either the field fills in with the ID value for the record from the associated table, or, if two records of the primary layout have the same value for this field, I get redundant records on the secondary table.

Also, I'd like to have a field whose available values are dependent on the value of a previous field. For instance, if I had a field for COLOR and another field for SUBCOLOR, I'd like to only see SUBCOLOR values associated with "blue" if "blue" is selected in the COLOR field. Once again, I'm sure this is a simple process that I'm just not understanding.

Thank you...

[bcooney]

I see three techniques that you need:

1. How to use a value list to store a foreign key, but not show the key, just the related value.

2. How to set up a conditional value list.

3. How to add values if the desired value is not available in the value list.

1. When you define your value list, select the option to only show values from the second field. This will "hide" the ID and show the related value. This only works if the foreign key field in the parent that uses this value list is set to a popup menu.

2. Here is a link to examples of conditional value lists. Conditional Value Lists

3. The goal for this technique is to create a record in the table that drives the value list. Then the value is available and the user can select it.

One method is to have an "Add New" button next to the field that will hold the foreign key. This button

1. opens a new window and switches to a layout that is based on a table occurrence for the value list table. Add a resize to fit script step to have the new window small and offset from your base window just like a dialog.

2. creates a new record (you may have a cancel button on this popup window that would delete the new record (without a dialog) and close the window).

3. The script then pauses.

4. User enter new value list item and clicks an OK button. The OK button closes the popup.

The new item may now be selected.

[mudrunner]

Thank you for your post...the demo file on Conditionals was very helpful. I'm still having a problem with redundant records in my supporting tables. Whenever I add a new record to the main table, I select from the drop-down lists for each of the fields which are related to other tables. When I am done, I look at the supporting tables and see the same value I just selected repeated. It seems as though every time I use a value from a drop-down list it wants to create a new record on the support table even though that record is already there??? To use the previous example, say I select "blue" from the COLOR field drop-down list (because it has already been used on a previous record and is therefore part of the Value List); when I go the COLOR table, there are two records for "blue". How can I prevent the creation of new records on the support tables. I already tried unchecking the "Allow creation of records..." in the relationship setup between the two tables. This just keeps me from accessing the drop-down list of values; even though I can still use the drop-down list on already existing records. Please help me with my confusion...

[bcooney]

I think I know what's going on.

Let's say we have two tables, Products and Colors.

Products has the field _kF_ColorID which uses the value list Colors and is a popup menu (this is important). The value list Colors is created from two fields in the Colors table, __kP_ColorID (autoenter serial) and ColorName. You have "show second value only" selected.

When a user specifies a Color for a Product, they are really entering the Color's __kP_ColorID into the Product's _kF_ColorID. There does NOT need to be a relationship from Products to Colors at all.

I think that you have the ColorID field from Colors table on your Product Form and it's creating Color records.

Edited February 4, 2008 by Guest

[mudrunner]

Thanks again...but it seems to me this would not allow me to add a new color record to the COLOR table if that color was not already in the drop-down list. I would like to be able to simply type in the new color information while filling in the product information on the PRODUCT layout and have the new color record populate in the COLOR table.

Also, you say I should use a pop-up menu? Wouldn't this also disallow the addition of new records in the COLOR table by addition in the PRODUCT layout?

Thanks again...

[bcooney]

It is better to store IDs than to store text values in order to ensure consistent reporting.

So, the answer is "no" you cannot add to a value list by typing in a new value (drop down list) if you want to store IDs and not text values.

It's a trade-off, but IDs are the more consistent approach.