copy all child records to new record

[gmeredith17]

I have a form based on a set with the following structure.

Devicecode 1:M link to Specrev which has a 1:M link to Standard

Each device can have several revisions of the electrical specification that is stored in the Specrev table. Each revision will have several parameters that are stored in the standard table.

My form has a combo box to select the devicecode and two browses, one based on the Specrev table and the other on the standard table. There is a button to add a new Specrev record and another button to add a record to the Standard browse.

What I would like to do is set the cursor to a record in the specrev browse and then when I click the button for a new specrev record it will create a new specrev record and copy all the child records from the currently selected specrev record into the standard table. This will help reduce the amount of data entry as the specifications tend to have only one or two fields changed.

Can this be done with action scripting? If not what is the best way of achieving this using xbasic?

Regards

Geoff

[gmeredith17]

Re: copy all child records to new record

I have attached a demo of the database to help with the explanation above.

[Tom Cone Jr]

Re: copy all child records to new record

Geoff,

You need to tell us what rules you want applied to prevent the new grandchildren from being linked to the wrong child. Your tables have no field rules yet.

Consider this:

a) if we duplicate the child table it will have the same link field values as the record being duplicated. You probably do NOT want duplicate primary keys.

b) if we then duplicate the grandchild records they'll have the same link field values as the children being duplicated. so they'll appear to be linked to the original child, not the newly created one.

The script that creates the new records can assign new link field values, but you have to tell us the rules. Alternatively, its possible to use autoinc field rules to supply new link field values, too. Either way you need to tell us the rules for distinguishing (assigning unique primary key values) the new child table record and then linking the new grandchild table records.

[gmeredith17]

Re: copy all child records to new record

Hi Tom,

I didn't think of the primary keys. Okay, what I want to copy is the data in browse 1 which is based on the standard table. This would be fields insertion, isolation and return. These fields from the relevant records would then be copied to the new revision number child records.

For example if I wanted to base the new specification on revision 6 and the new revision will be 10.

Then the child record fields insertion, isolation and return from revision 6 will be copied to the new revision 10 child records. The primary key for the standard table is deviceid which is equal to device code(devicecode table) + specrev(specrev table).

I am not sure I have explained it very well but hopefully you can get the idea and I appreciate any help you can offer in steering me to a more concise and clear description. My original concept of copying the record wasn't correct as I don't want the whole record copied just certain fields.

Thanks

Geoff

[Tom Cone Jr]

Re: copy all child records to new record

That's ok. Understanding the problem is the key to finding the solution.

How will the specrev field value in the "new" specrev record be determined? Our script needs to do this automatically, but you haven't told us what value it should be assigning to this field.

[gmeredith17]

Re: copy all child records to new record

The specrev is entered manually by the operator. Currently it is a numeric field but I will probably have to change this to a character field as there may need to be letters used as a prefix or suffix to the number. I will have to set up a field rule to make it a unique field only but that is the only rule to be applied. This is really an exercise to understand the method and how to apply it before I create the required database.

If the script has to generate the specrev value could a dialog box be opened with the last records specrev value in it which can then be modified to the new required value by the user?

[Tom Cone Jr]

Re: copy all child records to new record

Geoff,

Dr. Wayne included a discussion and sample code in his book "Xbasic for Everyone" that shows how to duplicate records from a set. It was discussed recently here because the script in his book has trouble in vers 8. Here's the link to the thread. The code discussed there could be readily adapted to your exercise.

http://msgboard.alphasoftware.com/al...ad.php?t=67709

[gmeredith17]

Re: copy all child records to new record

Tom,

Thanks for the link.

I decided to do this in a couple of stages. First I want to get the copying of the child data to work and then I will work on the primary key for the specrev table. For now I have decided to just increase the specrev field value by one which should be fine for experimentation purposes as long as I am on the last record when I run the script.

I modified the xbasic as follows and I have put comments on each line with my interpretation of what it is doing.

'Date Created: 01-Jun-2007 03:12:57 PM
'Last Updated: 06-Jun-2007 01:40:22 PM
'Created By  : Lance
'Updated By  : Geoff Meredith
dim rev as p
dim sdata as p
dim old_parent as n
dim countsdata as n
dim i as n
rev=table.current(2)
sdata=table.current(3)
countsdata = count(standard->freq,grp->specrev,grp->devicecode)

if countsdata>0 then
dim vfreq[countsdata] as b
dim vins[countsdata] as b
dim viso[countsdata] as b
dim vret[countsdata] as b

i=0 
sdata.fetch_first() ' goto first record from standard table
while i<countsdata ' while variable i is less than countsdata value repeat this loop
i=i+1 ' increase the value of i by 1
vfreq[i]=parentform:Browse1:freq.value ' set the value of variable vfreq[i] to the current freq field value
vins[i]=parentform:Browse1:insertion.value ' set the value of variable vins[i] to the current freq field value
vins[i]=parentform:Browse1:isolation.value ' set the value of variable viso[i] to the current freq field value
vins[i]=parentform:Browse1:return.value ' set the value of variable vret[i] to the current freq field value
sdata.fetch_next() ' goto next record
end while ' end of while loop
end if ' countsdata>0
old_parent=parentform:Browse2:specrev.value ' set variable old_parent to current specrev field value
rev.enter_begin() ' start a new record in the revision table
specrev.value=old_parent+1 ' put the value of old_parent into the new record specrev field +1
rev.enter_end(.t.) ' end  
parentform.resynch()   'force record pointer in parent table to refresh
ui_yield()  'force script to wait for user interface to catch up
parent.resynch()

if countsdata>0 then
for j=1 to countsdata  ' set count from 1 to total number of records to be copied
sdata.enter_begin(.t.) ' start new record in the standard table
parentform:browse1:deviceid.value=("devicecode+specrev->specrev") ' put the value of the deviceid field to devicecode+specrev values
parentform:Browse1:freq.value=vfreq[j] ' put the value of variable freq[j] to into the freq field
parentform:Browse1:insertion.value=vins[j] ' put the value of variable freq[j] to into the freq field
parentform:Browse1:isolation.value=viso[j] ' put the value of variable freq[j] to into the freq field
parentform:Browse1:return.value=vret[j] ' put the value of variable freq[j] to into the freq field
sdata.enter_end(.t.) ' end data entry
next ' loop again until j = the countsdata value
end if ' countsdata>0
parent.resynch()
end

I am getting and error at line 32 which states that 'enter already began'

rev.enter_begin() ' start a new record in the revision table

I am sure that I am missing something fundamental. Please forgive the poor xbasic modifications as I tend to do something with a script and then copy the code to try and get the correct syntax which is what I based the the following type of code on.

vins[i]=parentform:Browse1:return.value

Geoff

[gmeredith17]

Re: copy all child records to new record

Forget the error it disappeared after I shut A5 down and restarted. I also found some typos when putting the field values into the the variables. To much use of cut and paste. Code still doesn't work yet. I think the line where I increase the value of the specrev field by 1 is wrong.

[Tom Cone Jr]

Re: copy all child records to new record

Geoff,

Here's an example for you to ponder.

It uses a different approach, and is closer to how I'd do it personally. I'll be interested in your comments, and those of anyone else who looks in.

-- tom

[gmeredith17]

Re: copy all child records to new record

Thank you Tom.

There seem to be so many commands in x basic that I have difficulties finding/choosing the appropriate ones. However the code I presented earlier had so many mistakes I feel somewhat embarrassed. I was going through it last night at home with the x basic reference manual and I realised the syntax had a lot to be desired among other things.

I have looked at your code and really appreciate the effort you have gone to explaining each line. It has been a great help and an additional step in my understanding x basic and why and how to do certain things.

Once again thank you for your assistance.

Geoff