Still Issues With Duplicating Potral Information

[mzimm]

This doesnt seem to work! Here is what I've done so far. I have created a relationship between the two files called requestnumber and a global field in the child file (TestedParts) called gRequestNumber. The scripts look like this:

(in the parent file)

Go to related Record[show,"TestedParts::gRequestNumber", "RequestNum"]

Duplicate Record/Request

Set Field[TestedParts::gRequestNumber", "RequestNum"]

Perform Script[sub-scripts, External:"TestedParts.fp5"]

(in the child file)

Unsort

Go to Record/Request/Page[First]

Loop

Duplicate Record/Request

Set Field ["RequestNumber", "gRequestNumber"]

Go to Record/Request/Page[First]

Omit Record

Exit Loop if["Requestnumber = gRequestNumber"]

End Loop

What is wrong, all this does is nukes the records in the child and freezes up (stuck in the loop). Is the lines for Set Field correctly formatted. I noticed in Chucks example for this script there was no quotes in some of the set field lines between the field names.... how do I get this to work. Thank You for any help with this mess.

[-Queue-]

Go to related Record[show,"TestedParts::gRequestNumber", "RequestNum"]

This makes no sense. There are too many parameters for the function and "TestedParts::gRequestNumber" is not a relationship; it's a related field.

[mzimm]

OK, I changed it to Go to Related Record[show, TestedParts]

It still doesnt work, what am I doing wrong? Are my Set Field formats OK?

This one is sure kicking me in the butt!

[-Queue-]

As long as your RequestNumber is auto-incremented, I think this should work fine. Check the attachment for an example.

duplicate.zip

[Vaughan]

Your relationship cannot be valid if it references a global field in the remote file. The remote key must be indexed, and global fields are not.

Even if global fields could be indexed, it makes no sense using a field in the remote file that has the same value in each record. The relationship would permanently relate to all records.

[-Queue-]

It doesn't reference a global, it references the foreign key from the parent file and allows new child record creation via the portal. The global is only used to provide a flag for when duplication of child records should stop.

[Vaughan]

Oh, my misunderstanding about the global field's purpose.

It sounds like you want to duplicate a record in the master file, and recreate the original file's set of related records as well. This is always a tricky process.

First job would be to duplicate the master record and put the new local key into a global field. Then return to the original master record, go to its related records then duplicate them one at a time, changing the remote key field as you go through them. You need to make a relationship from the related file back to the master (a reverse relationship) that matches all records: do this by creating a calc field in both files where the calc is "1" and use this as the keys on both sides.

It's also worth remembering that new (or duplicated) records always appear last in unsorted databases, but are "next-in-line" if the file is sorted. This makes them much easier to wotk with.

The script would look something like this (which is a little bit different from the description above) starting in the master file:

#Assume the current record is the one to duplicate.

#Find the set of related records to duplicate now because it's convenient.

Go to Related Record [relationship, show only related records]

#Duplicate the master record.

Duplicate Record/Request

#Set the new key field value into the global field.

Set Field [gKeyField, KeyField]

#Duplicate the related records and relate them to the new record.

Perform Script [external, relatedfile::Duplicate Related Records]

#Return here when finished.

Refresh Window [bring to front]

The script in the related file called Duplicate Related Records would be:

#Assumes the current found set is the records to duplicate.

#Sort the database, so the new records are inserted next to the originals.

Sort by KeyField

#Start the duplication process.

Go to Record/Request [first]

Loop

Duplicate Record/Request

#Give the duplicated record the key of the duplicated master record.

Set Field [KeyField, relationship::gKeyField]

Go to Record/Request [next, exit after last]

End Loop

It really doesn't matter what field the records are sorted by, as long as they are sorted. Also, the relationship specified in the Set Field [] step is the constant reverse relationship, it is used to pull across the key of the new master record.

[-Queue-]

That's what my example file does, except it sets the global in the related file at the beginning (since setting a related global works for virtually any and every relationship) and it Unsorts the related records, Duplicates, sets the foreign key, goes to the first record, Omits, and so forth. Oh, and I'm not using a constant relationship from the child file, though I would assume there should be one regardless; one never knows when it will be useful.

Did you look at the files?

[Vaughan]

Me? Naaa., too busy. I'm sure your method is equally valid.

"... setting a related global works for virtually any and every relationship"

Good point, I hadn't though of it like that before.

[mzimm]

Hello: I got this script working. But there is a new issue. I can execute this script with my master password but the other users with limited access in the related database cannot use it. It will "bomb" on them giving them a "password wont allow access or cannot modify record" type of error and it locks them in (only way out is ctrl-alt-del). My question is can I add something to the script to give them my access level to complete the script then put them back to their level when it completes? Like change to my user name or group without them having the password and change back after the script runs...

[BobWeaver]

No. There is nothing you can do in a script to change the user privileges. However, if you have the whole solution set up so that record creation and modification are controlled by scripts (by removing access to the menus) then you can give the users more access privileges, and then let the scripts decide what operations can be done.