New Record in Set

[Bruce Roberson]

I have a set with two tables as follows:

Parent Table:
*New_Name c 33
*Prod_Code c 9
Property_Number c 9
Property_Sub c 9

Child Table:
*New_Name c 33
Pun c 9
Suffix c 9
*Prod_Code c 9
then 5 others fields of various types, character, numeric, and logical.

The Parent and child are linked together in a One to Many relationship by an expression that ties the New_Name and Prod_Code fields together as denoted by the *.

First of all, if you try and link a set in this way using the referential integrity button other than "None", it gives you this message after you press ok "for integrity, use parent field name". If you agree to only link on one field without an expression, it allows you to cascade adds and deletes for example. I am not sure I understand first of all the reasoning for this limitation but that is a separate question from the real question in this topic.

So, back to the table in question, which works as described in the paragraphs above:

If I use a setup form which I call "Property Setup Screen", I have the four parent fields on the top, and an embedded browse layout for the bottom part of the form. Within the browse layout, the linked fields appear of course. But it seems redundant to have to fill in the "New_Name" field and the "Prod_Code" field in the embedded layout. If I am entering a new record at the set level rather than at the table level, then I want to be able to have some means of automatically feeling in the New_Name and Prod_code fields in the child based on the entries I have just made to the new record of the parent table.

So, how can I eliminate the need to keep the fields in both parts of the form?

Thanks


Bruce

[Al Buchholz]

RE: New Record in Set

It sounds like you always want the values to stay in synch. If New_Name or Prod_Code changes in the parent, you want all the child records to change also.

If that's true, then what about using a field (Property_Id) on the parent that links to the child records?

Define Property_Id as an autoincrement.

Put Property_Id on the Child and use it as the link between parent and child. The Property_Id on the child will be assigned by the linkage.

You can choose to show or not to show Property_Id on the browse for the child. The default form has it not showing.

Take New_Name and Prod_Code off the child table.

When you need to change New_Name and Prod_Code, change it on the parent record. Always use the set when reading the child and you'll have those values from New_Name and Prod_Code from the parent.

[Bruce Roberson]

RE: New Record in Set

Al, I don't see how this approach using the ID could work since there is a one to many link between parent and child, instead of a one to one link, that would allow for the possibility of synchornizing the record ID you mentioned.

In other words, for one parent record, I may have one child record today, but tommorrow I may need to add a child record to a parent who already has a child. And then in the same user session, I might need to add a separate parent record with its own associated child record. In this case of course the ID approach would definitely get out of sync wouldn't it?

If I'm misunderstanding what you are saying, then please clarify further.

Bruce

[Al Buchholz]

RE: New Record in Set

Actually the id would stay in synch for both of those scenarios.

It is assigned automatically when a parent record is entered and is picked up by a child whether the parent has just been entered or was previously entered.

You will only have one parent selected at a time and the child will be assigned that parent's id number.

It's one of the advantages of using a set.

[Al Buchholz]

RE: New Record in Set

ps The autoincrement option is only used to define the parent field. The child picks up it's value from the parent because it's the linkage field in the set.

[Bruce Roberson]

RE: New Record in Set

Ok I am starting to play with this option right now.. Trying to update existing records to match up first before I can ask any possibly intelligent questions.

Thanks for the approach. I re-read your first post, and was thinking of auto increment as a numeric representation somehow that would be 1,2,3, for each record etc.

I will post back once i have some test data to use.

Thanks again.

[Al Buchholz]

RE: New Record in Set

Do you have data to convert or can you empty the tables and start over?