Correctly writing script that uses two database files

[Deed]

Hello, I'm a relatively scripting newbie and wondered whether anyone could get me started with a script that accesses two separate Filemaker database files. One file contains a list of phonathon volunteers (name, email address, and recordID). The other file contains a list (approximately 10 times more names than the volunteer list) of donors (name, email address, and recordID). I would like to assign a volunteer to each donor, and I would like the volunteer workload to be equal. I'm hoping for a script that accurately and evenly apportions donor names to each volunteer. I'm anticipating a script that counts the number of volunteers, counts the number of donors, starts in the volunteer database, finds the first record, jumps to the donor database, assigns the correct number of donors to the first volunteer, jumps to the second record in the volunteer database, assigns donors to that volunteer, and so on, exiting at the end of the pairings. I'm not sure in which file I should write the script, and I don't know the correct coding to get the job done. Thanks for any advice!

[fmbiz.net]

Hello Deed,

Your description of what you are looking for sounds like it would best be served with a Relationship between your 2 files.

Instead of using a script to walk through the Phone Volunteer-to-Donor match up, try setting up a key field that matches the one Phone Volunteer with many Donors. This will be a one-to-many relationship that will help to quickly group and identify the appropriate Matches.

Follow these steps:

1. Verify that the Volunteer file has a field that is a unique ID field which is a number and make certain that it is a sequential number; 1-100, 10-200, etc. something like that. Ideally it would be setup as a Auto-Enter Serial number.

2. Add a new foreign key field to the Donor file, call it volunteer_fk and make it a Number data type.

3. Create a simple Utility script that will assign the correct number to the volunteer_fk. Simply divide the number of Volunteers into the number of Donors to determine the maximum number of Donors to assign to a single volunteer. Let's say you have 100 Volunteers and 1000 Donors. Each grouping of records would be 10.

Using a an OMIT technique constrain your Donor found set to 10 Donors or the result of the division step above.

Using a utility script, Assign the numeric value of the first Volunteer ID number in the Volunteer file to the volunteer_fk field in the Donor file. (This is done by choosing the lowest number in the Volunteer ID field, say 1 or 5 or 10). Loop through this group of 10 records setting the volunteer_fk field to the Number.

Choose your next set of 10 from the Donor file, excluding the ones that are already set. Increment the Volunteer ID value by 1 and run the Utility script again until all of your Donors have been assigned to Volunteers.

4. Now setup the Relationship in the Table Occurrence graph. You should already have at least 1 instance of the Donor table. Now if you do not already have an External reference to the Volunteer, create one now.

5. Create a 'join' between the Volunteer ID field in the Volunteers table with the volunteers_fk field in the Donor table. Use the = sign for the Join type.

You should now have a working Relationship that will properly match each group of Donors to a single Volunteer. To show this on a layout in the Donor file just create a portal that uses the context of the Volunteer table.

Add an identical relationship as the one above if you want to also use a Portal in the Volunteer file.

If you are new to FileMaker and relational database concepts this will seem like a lot. Just break it down into small steps and the reasons for the instructions outlined above will become clear.

Hope this helps.

Best Regards,

Charlie - FMBiz.net

[Deed]

Thanks for your advice. Could you critique the attached script, which is meant to perform the tasks of your step #3? For some reason, in the first 8 records of the donor database, the script sets the VolunteerID to "1", which is what I want; then, in all of the other donor records, VolunteerID is set to "2", which is not what I want.

AssignVolunteerScript.pdf