Losing pointer in parent table

[djc728]

I have a flat file table and I'm looping thru each record and creating new records in a child table.

As I'm looping thru in order to create the child record I use a New Window command, Goto Layout, Set my fields, Commit record and then close the window.

But what I'm finding is that I'm getting duplicate records in my child table. The looping seems to go thru ok part of the way but then for some reason it will go back some records and start over. I'm not sure why I'm losing the pointer in my parent table. I'm thinking it has something to do with the New Window command.

Any ideas?

Dom

[bcooney]

You're capturing the parent ID in a variable, too, correct?

So it's:

Loop

set $parentID to ParentID

New Window

Go to Layout Child

New Record

Set foreign key to $parentID

Close Window

Go to Next Record (exit after last)

End Loop

[djc728]

Yes I am - thats exactly how I'm doing it.

[bcooney]

Well, I'd say something's different, as you wouldn't end up with duplicate children with this script. Post your script? (You can print to pdf and post that or unload a zip of the file).

[djc728]

Scripts enclosed.

ParentScript.pdf

Subscript.pdf

[bcooney]

My goodness, quite a script.

Without the relationship graph, it is difficult to understand what setting SOP to SOP Form::_SOPID; $ForeignKey does. What is the name of the child table here?

Could this be done via an import?

Edited December 27, 2009 by Guest

[IdealData]

There's something peculiar about the subscript as the second last "End If" is not indented correctly, but that might just be the way it printed - but the script could also be corrupt!

You could simplify the subscript too.

1. The New Window command only needs to appear once, place it immediately after the first IF statement. The window does not need to be named as your script doesn't make a reference by name to the window

2. The show/hide and adjust window commands are not required.

3. The commit records is also not required (although it is good practise) as the following close window will do this any way.

The current script requires that all the parameters are found - if the first block of tests fails then there is no default result and the script will not create a new window nor change layout. It will also execute the close window and close your original window under these conditions.

Do you know how to use the script debugger? You can place stop points in the script to monitor it as it runs and also see the $vars and other field values as they happen.

[djc728]

Thank you all for your input. I found out the problem. My script was running correctly - the parent table does not have unique primary keys so it appeared like I was getting duplicate records but that begs a totally different issue altogether - why is the key field not unique.

Thank you all again for your input.

[bcooney]

Out of curiosity, what are your table names? I couldn't figure them out from your script.

[IdealData]

why is the key field not unique

Use an Auto enter SERIAL for the key field.

Is it possible you have reset the serial value, either manually or scripted?