Script to create mutiple records in related db

[Ronzues]

Hi,

Can you help me with this?

I have 2 related databases. One is called "Contacts" and the other is called "Activity Log"

I have a portal set up to show multiple past activities from the activity log for each contact and I have it set to allow creation of related records.

Here is what I need to accomplish:

I need to be able to do a "Find" for a set of contacts in the "Contacts" database and then create a new record through the portal in the "Activity Log" with the exact same data in the exact same fields for each contact found.

There are only 4 fields shown in the portal.

"Date", "Time", "Regarding", and "Artist"

Right now I do a find for all necessary contacts and then scroll to the bottom of the portal of each contact and add the same data for all four fields one at a time and this is very time comsuming. I would love to have a script or something that will allow me to just add all these new records for all contacts in the found set into the activity log at once.

Any help would be greatly appreciated.

Ron

[John Caballero]

Howdy,

Have a look at the attached file.

John_Sample1.zip

[Ronzues]

John,

Thanks for the help. Sorry it took me a couple of days to get back to you, it's not that I am ungrateful, but that I have spent every night trying to disect what you sent me and make it work with what I have. It looks like this may be exactly what I need, I am just having trouble implementing it.

Ron

[John Caballero]

Hi Ron,

Don't worry at all about appearing ungrateful. I tend to learn best by spending just digging into whatever is tossed to me - I'm notoriously challenged when it comes to following directions! You may do better with a little instruction. Not a problem at all.

In the Main table there are some global fields:

one each that corresponds to the fields in the related table, and one (gznID) to capture the ID of whichever record in Main is being acted upon.

There's also a field called zcn1 that is a calculation that returns a value of 1.

There is also a zcn1 field in the Data table - you'll understand why in a bit.

Basically the script first looks to make sure your found set actually contains records. If it doesn't, it gives you an error message, finds all records, and halts. Obviously, if that condition isn't true, then your found count must contain records and so it proceeds to the loop section.

The loop starts with the 1st record of the found count and sets gznID (a global field) with the unique ID of that record. (I use an auto-enter serial # field called znID that contains the unique ID for each record.)

The script then calls up a subscript in Data which creates a new record and sets the value of the "linked to" field in Data (nMainID) with the value that is set in gznID over in the Main table. The reason for this step is so that the newly created record in Data can be shown in the portal of its parent record in Main, and that's because in Main I have a relationship that is znID::nMainID. Remember, we've only acted on one record in Main at this point - all we're doing is capturing the record ID of the currently acted-upon record in Main (because we're temporarily storing its value in gznID) and setting it in the nMainID field of the newly-created record in Data.

How that works is because I have a relationship that goes from Data to Main that is linked by each table's respective zcn1 fields. EVERY record in Main will have zcn1 value (because it's a calculation) and EVERY record in Data will have a zcn1 value, so it doesn't matter which record you're on - the relationship will always be valid IF both tables have records. Because gznID is a global field, you don't have to be looking at any particular record in order to bring a global value through the relationship. All I need is a valid relationship, and the constant-to-constant relationship (zcn1 to zcn1) is perfect for that. The constant-to-constant relationship is valid the very instant the new related record is created.

The next script steps in Data set the values of the target fields of the newly-created record in Data with the values you specified in the corresponding globals over in Main.

Program flow is then returned back to Main. The script in Main then goes to the next record and repeats the loop until all records in your found set have been acted on.

So the whole process of setting gznID in Main with a record's znID and then creating a new record in Data and populating its fields with globals through the constant-to_constant relationship gets repeated for each record in Main until all the found records have been acted on.

Does that help?