Linking values from table 1 to table 2

[Tom Sullivan]

What is the correct mechanism to transfer a value from a field with a drop down list in table 1 to a linked field in table 2?

For example: table 1 has a field called "Coats". The choices are: winter, fall, spring. Table 2 is linked 1 to many with drop down list of Red, Blue, Green, etc. How do you isolate the input form in table 2 so all of the records only apply to a choice selection from the table 1 drop down? I want to be able to input 50 - 100 records at a time that only apply to 1 choice from the table 1 drop down list. Then to be able to select a different choice from the table 1 drop down, and input 50 - 100 records into the form in table 2 that apply to that choice.

Appreciate any help you can give me.
Tom

[Tom Sullivan]

RE: Linking values from table 1 to table 2

Sorry...the linked fields are Coats (the dropdown in table1) to Coates2(a field in table2). The dropdown field in table2 is not linked.

Thanks,
Tom

[Tom Cone Jr]

RE: Linking values from table 1 to table 2

Tom, to me the answer to this turns on how you are doing your data entry.

For example, if you are using a form based on the set, with an embedded browse for the child table, values will be inserted into the linking field of each child record automatically, by Alpha Five.

On the other hand, if your child records are being entered using a separate form (based on the child table) called from an earlier form in which the user entered a new "parent" record, you must pass the key value from the first form to the second, and then insert it into the linking field for the user. This is my own preferred data entry routine by the way. You can pass the value by saving it in a global variable in the first form, then declaring and using the global variable in the second form. Or, you can have the second form retrieve the value from the first form, assuming it will *always* be there at run time. In any case, the field in the second form should be set read only, so your user doesn't mess up the key value (which of course is needed to maintain the relationship with the parent table).

Hope this helps. Let us know how things turn out.

-- tom

[Tom Sullivan]

RE: Linking values from table 1 to table 2

Tom:

Thanks very much for your help. Unfortunately, I'm still stuck. When you select a value from a choice list in drop down, is that value active as long as the form is open? If I have a button on the form that opens form 2 in table 2 what is the verbage on the button(action) to copy or fill that value to a field in form2?

Thanks again,
Tom S.

[Tom Cone Jr]

RE: Linking values from table 1 to table 2

Tom,

There are probably lots of ways to do it, but here's one.

In the attached file you will find a database called Testing. Unzip it to its own folder.

You will find that it contains two tables, Names and Addresses.

It has one set, Names, linked one-to-many to Addresses.

It's a fragment of an imaginary contact manager, in which you are trying to track multiple addresses (home, office, vacation get away, etc.) for each person.

Open the Names form. Navigate through the records. There are only 2.

Then push the button to "Call the Addresses form, begin new record, and pass ID".

You will see the addresses form on screen, with the value of the field I want to use as my linking value filled in automatically.

Here's how it works:

1. The addresses form is the default form for the addresses table, except I've removed ID from the tab order and have made it read only. This prevents the user from being able to change a key value I need to link back to related parent records.

2. The names form is the default form for the names table. I've added the button to call the Addresses form. This is where the heavy lifting is done.

3. The onPush event of the button loads the addresses form; begins data entry; and passes the ID value to the ID field on the addresses form. Did not use any global variables. Just used the current value of the ID field from the Names table.

Hope this helps.

-- tom

[Tom Sullivan]

RE: Linking values from table 1 to table 2

Tom:
I can't tell you how much I appreciate your help. That was a most thorough explanation. It works perfectly on navigating into form 2 and passing the value from one table to the next. Problem now is that when I press button on form 2 to enter new record, the value in the field which had been transferred, disappears.

What do you think happened?

Tom S.

[Tom Cone Jr]

RE: Linking values from table 1 to table 2

The example I sent you simply passed the value from form1 to form2. It did it by placing the value in a specific field of the table upon which form2 was based - after beginning to enter a new record.

When you begin entering a second record the old value is gone.

One solution to this would be to store the value in a variable attached to form2, and then place the value of that variable in the designated field each time you begin entering a new record. It could behave sort of like default field values, if you see what I mean.

-- tom

[Tom Cone Jr]

RE: Linking values from table 1 to table 2

I reworked the earlier forms to permit one to enter multiple addresses consecutively, for a single name. Instead of passing the key value directly to a field in the called table, I store it in a global variable, and then read the global variable from the called form every time a new record is entered. Here are the details.

The attached file should be unzipped into its own folder.

I made no changes in the tables or indexes.

On the Names form, I modified the script which runs when the button is pushed to call the addresses form, begin a new record, and pass the id value. The value of the current id is first stored in a global variable called idnumber. Then the addresses form is called, and a new record is started. The script no longer stuff the id value into a field in the addresses table. It just stores the value in a global variable.

In the Addresses form a number of changes were made.

Save Button - it no longer closes the form, it just saves the pending changes.
Cancel Button - it no longer closes the form, it just saves the pending changes.
Close Button - this is new. It closes the form, but only when there are no changes pending.
New Record Button - this is new. It causes the form to enter a new record, but only when there are no changes pending.

The OnEnter event for the Addresses form now contains a new script. This script declares a global variable with the same name as that declared in the Names form. This causes the Addresses form to be able to "see" the value of the global variable that was set back in the names form, when the button was pushed to enter a new address. This value is then assigned to the ID field in the Addresses form, and the field is refreshed, causing the display to show the actual value. Because this script runs every time the form enters a new record, it will run each time the user pushes the New Record Button. Remember, the id field in the Addresses form is "read only" so the user can't destroy the link between the current address record and the name in the parent record back in the names table.

Pretty nifty, isn't it?

-- tom

[Tom Sullivan]

RE: Linking values from table 1 to table 2

Tom:

Thanks a million for all your help with this. Can't tell you how much I appreciate it.

I finally got it to work using your first explanation. The field value was getting transferred to the form in the 2nd table ok, but came up blank when I entered a new record in the child table. I changed the field rule for default mode to: use previous value as default, and it works fine.

You guys are really great. Thanks again,

Tom