Append Records with Related Data

[Lare2]

I want to append records from the waiting list database to the clients one. The clients database has a one-to-many relation with the ages database as well as the waiting list database has it with waiting ages.

I have been able to append records from the waiting database to the clients database without a problem. I just don�t understand how to move the related data also.

The Client Id is an auto increment field as well as Waiting ID and they don�t match because not all the clients come from the waiting list, some are directly entered into the clients database.

This is for a non-profit organization idea is that they have a waiting list of people for sponsorship. When a new person is going to be sponsored they will select a person from the waiting list, click a button and the record gets moved to the clients database so they can fill the rest of the information while the record gets deleted from the waiting list

Is there a way to achieve this. Thank you in advance for the time given to this post

[Stan Mathews]

Re: Append Records with Related Data

I may misunderstand your process but it seems to me you don't want an append at all.
You want to choose a record in the waiting list table, begin a new record in the clients table so the auto increment kicks in, then copy the matching fields from the waiting list table to the new record in the clients table?

Since the tables don't have identical structures it seems that <TBL>.RECORD_TO_VARS() would offer the best way to avoid a lot of heavy lifting.

[Lare2]

Re: Append Records with Related Data

I may misunderstand your process but it seems to me you don't want an append at all.
You want to choose a record in the waiting list table, begin a new record in the clients table so the auto increment kicks in, then copy the matching fields from the waiting list table to the new record in the clients table?

Since the tables don't have identical structures it seems that <TBL>.RECORD_TO_VARS() would offer the best way to avoid a lot of heavy lifting.

That is exactly what i want to do. Can you please elaborate more on how to accomplish this using <TBL>.RECORD_TO_VARS()

[Stan Mathews]

Re: Append Records with Related Data

Not knowing anything about your normal workflow....

I would have a form displaying records in the waiting list table.
I would find the record for the person to move to the client database.
I would have a button on that form which used <TBL>.RECORD_TO_VARS() to create shared variables for the record.
I would switch to a form based on the client table.
I would have a button on the client form which started a new record and set the field values, other than the auto increment field, to the values in the shared variables.

The example in the help file seems pertinent to your situation with the exception of changing

tbl = table.open("customer")

to

tbl = table.current()

to reference the record you see on the form.

[Stan Mathews]

Re: Append Records with Related Data

The attachment illustrates a solution much easier than I first envisioned.

The process is much the same. Both forms need to be open, navigate to a person on the waitingform, switch to the clients form, push the button, switch back to the waiting form and delete the record.

[MoGrace]

Re: Append Records with Related Data

I would add a field to the Client table to receive the value of the Waiting ID from the append - without any field rules. To both tables add a one character field to use as a link with its default set to "X" and populate the field using an Update. Use this field to link the tables in a set: Now all the tables are available for your script.

Clients
  |->Ages
|=>Waiting
  |->Waiting_Ages

Display two browses on your form showing their ID, Name & Age or as much info as you need to identify them. Have your button that copies the record from one browse to the other, blank out the "X" in Waiting which will remove it from the display. Then on closing the form you can use delete_range() to find the blank values in Waiting and delete those records. Or if referential integrity is set in Waiting_Ages, both records can be deleted together.

I have a similar form I use for moving machines, I will see if I can adapt it for what you are trying to do.