Looping Script to copy records to another table

[Adam M.]

I'm in the middle of building a database for a school. I've run into a minor problem with assignments. The way I set it up, I have two tables for assignments.

1. Assignments (Contains information on the assignment with an AssignID)

2. AssignmentResults (Contains the score for every student on that particular assignment)

I have a script that goes to the related records of the enrolled students. Then in a new window, I open a utility layout for AssignmentResults. I used a loop to go back and forth between the windows copying and pasting the EnrollID into the AssignmentResults table. This was working fine. Now, it still works, but it seems really slow. It takes around 10 seconds to copy 10 records compared with a loop used in another table that is almost an exact duplicate that takes only 4 or 5. I've tried using Set Field instead of copy and paste, but that doesn't seem to make any difference. I started to think that problem was because of some lookup fields, but there are actually many more lookup fields on the table with the faster script. Any ideas or recommendations? Is there a better way than using a loop? Any help is appreciated.

[Fitch]

The more records, with the more indexed fields, the slower record creation will be.

Import might be faster.

Or, you could create the records directly through a relationship. Maybe someone else will be kind enough to jump in here and point you to the forum posts that describe this process. But for example, if you create the records through a portal, then commit them all at once, our tests have shown a significant speed increase.

Or, you could put all the IDs into a single variable (maybe with Get Nth Record), then use Set Field with the GetValue function. This would save you the back and forth; maybe window management is related to the slowdown.

[Søren Dyhr]

When things slows down beyond tolerance, could the relational structure be the one to blame ...the bleeper always goes on inside my head when people says "Copy" ...when there seems to be a many2many structure at stake!!!

--sd

[Adam M.]

I tried using the portal idea. It works, but it's not any faster. This table has a few indexed fields, but they're needed. And it has less than the other table I mentioned that runs the script quicker. Does this mean that as there are more records in this table, that it will slow down even more? I'm already concerned about this solution being even slower running on FMServer. There must be a quicker way. You mentioned importing. How can I do this on a multi-user solution?

[Fitch]

It's not a problem to import from one table to another in a multi-user solution using the Import script step.

And yes, the more records you have, the longer your indexes will take to update, so it does affect performance, but it's usually negligible until the number of records gets quite large; how many records are we talking about?

[Adam M.]

The solution is brand new and won't be implemented until August so there are no records yet. There will be on average around 180-200 records added every day. Around 30,000 records every year I'm guessing. There are only 4 indexed fields. Hopefully it will work smoothly.

As far as importing as opposed to a loop script, I would have to export first. If I have multiple users using the solution, they can't all be exporting to the same file, can they?

[J__]

How about using "Freeze Window" and "Refresh Window" script steps wrapped around your loop?

The idea here is that you have one layout with the records you are going to copy to another table. One way to get them into that other table is to create another layout with the second table as the table for that layout.

Once you have these 2 layouts set up, you can create a loop which goes through all the records in the table. For each record in the table you can copy the values to global fields (or script level in FM8), then you switch over to the second layout, and read the global field values, create a new record and put them into that table.

This can have mixed results, but it works ok for me. The trick I used was to Freeze Window before I start my 'busy' loop. So when I am swithing to the second layout, you won't see this happening because the window has been 'frozen'.

Once you are done the loop, you refresh the window.

thos

Freeze Window

Move to First Record in Table 1

Loop until End Of File

Copy values in Table 1's record to global vars

go to layout ['layout for table2']

create a new record in Table 2

Copy values from Globals to record in Table 2

go to next record in table 1

end loop

Refresh Window

I have had a decent experience with this, maybe it might help yout? Hope so.

sincerely,

J__

[Søren Dyhr]

And the tables can't be related instead of copying? The ideal for normalized structures is to prevent syncking issues by keeping each data in just one location.

I started to think that problem was because of some lookup fields, but there are actually many more lookup fields on the table with the faster script.

Reserve lookups to stuff that needs to get altered and by it has a historic meaning to the data. I'm suspecting that you are overusing lookups to things where it isn't required ...watch this video:

http://previews.filemakermagazine.com/videos/541/GraphRules_full.mov

--sd

[Adam M.]

No, I haven't overused lookups. I have to have lookups because there's a possibility that the information might change. It's because the Assignment Results need to be related to the students that I need to copy the EnrollID.

Let me re-explain my problem. For example, say we have 10 students in a class. Each student is on a students table with StudentID. Each Class has a ClassID. The Enrollment table has a field for the StudentID and the ClassID, but the primary key is the EnrollID. My script is creating an assignment for the class, getting the EnrollID for all 10 students from the enrollment table and creating a new Assignment Results record for every student. I'm not duplicating information. I'm inserting the EnrollID for each record so the records can be related to individual students. Trust me, I know I'm somewhat new to this, but the concepts are easy to grasp. I understand avoiding duplicate information.

As far as the Freeze Window and Refresh Window, I'm already doing that. It doesn't affect speed unless the layout is graphic intensive, which these aren't.

[Søren Dyhr]

creating a new Assignment Results record for every student

Not quite with here - you can you make a screendump of your relations?? I would display such matters from either a TO showing records from Class or Student. A join table is only required if historic data needs to get stored eventhough the relation is a many2many...

Investigate this template:

http://www.dwdataconcepts.com/dl/tw/Many2Many.zip

(...it's in an earlier version, so converting it to FM8 requires you drag all files in the folder, ontop of the application icon to start a proper conversion)

A more contemporary approach to the matter is this:

http://www.filemakermagazine.com/modules.php?op=modload&name=News&file=article&sid=615&mode=thread&order=0&thold=0

...focus here on the second half of the video, which BTW neatly explains when to apply lookups - Unfortunately does it require you to subscripe to Matt's services - but just do it!

I'm fully with this endorsement from David Kashel:

What the videos available at FileMaker Magazine have to offer is not so much the same old how-to advice as it is instruction in how-to-think. You can get tips and techniques anywhere, but Matt’s videos teach you how to think like a FileMaker developer, and this has value beyond any clever technique. The fact that many of the techniques are useful (and clever) is just a bonus. I’ve been a subscriber to FileMaker Magazine for a long time. Still am. I try to read/see everything Matt puts out, though in truth I’m usually 2-6 months behind. If this surprises you (after all, a big shot like me should already know everything, right?), it shouldn’t. I may be familiar with much of what Matt puts out, but certainly not everything. I may even have a better approach to a few of the techniques he teaches (we all have egos). But Matt’s productions make me think, and they have often provided the seed for a new idea. Even if you never use a single technique or tip Matt has published, he will still make you a significantly better FMP developer, because he will make you think. This is a worthy resource.

...snipped from this indispensable document:

http://www.foundationdbs.com/Downloads/WhitePaperForFMPNovices.pdf

--sd

[Adam M.]

No, offense, but I don't think you're understanding what I'm doing. I'm looking for an alternate way to create a new record in the AssignmentResults table for every student for every assignment. If you have a way to do this, let me know. Your posts don't relate to this. For a little clearer view, look at this graph.

[Søren Dyhr]

How about this:

[Adam M.]

Where do the scores go? That's what the AssignmentResults table was for. There are many assignments in one class, many results for one assignment (One for every student in the class). The assignments don't need to be related to the student, the scores do. The biggest problem with the relationship graph you posted is that the scores can't be related directly to the student. That would cause you to see all of that student's scores summarized together. They need to be separated by class. That's why the AssignmentResults has to be related to the Enrollment table, not the Students table.

I'm not having any trouble with the relationships. The entire solution is working perfectly. I was just looking for a faster way to automatically create records in the AssignmentResults table after creating an Assignment. Right now, I'm using a loop. It creates the records so all the teacher has to do is create the assignment, the records show up in a portal, and the teacher enters the scores.

[Søren Dyhr]

Where do the scores go?

To the enrollment table, which ought to be called single_tasks instead. I think I'm able to do get similar functionality by making the join-table a star (tri) between 3 TO's Class, Student and Assignment. What it does, is that you can see eaverything a relation away without having to rely on lookups at all, but instead could access data in tight spots via an unstored calc'field.

I attach my rough, with carries a bug or two, but as I see it as I also suggested yesterday, shoulc enrollment instead establish a set of ID's to choose from. Methinks that such a matter easiest is established by splitting the graph in two (3) one to taking care of making a valuelist work in the next graph ...giving a found set of students to assing task to due to their enrollment. At present is it a carthesian produkt relation making all students availiable.

Then is there the third graph (second because I havn't made the enrollment thingy), which owes it's respect to Geoff Canyon, who suggested this as a way to assing markings/grades results in an article in Advisor this Sept 2005 called "Use Dynamic Relationships to View Data"

I think I could get away using this layout alone by turning allow creation of related record, and systemizing the generation of Valuelist, but it's just there to show another approach in adressing the data.

But by'n'large do I think that your relations are inconvenient! Then to your question how to enter an entire class to a new tasktype. Well If you know all the ID's of the student's you wish to assign, could you put them in a textfield return delimited.

Then in a new calc'field could you split each ID via Get(CalulationRepetition) provided that the calcfield is large enough. What it then takes is store the two remaining ID for Assignment and Class in a pair of global. Single out the record it's done in by:

Show All

Omit

Show Omitted

...

Then import between tables the repeating value, while exploiting the feature of splitting into separate records. You can get an idea of how to do this by investigating my template here:

http://www.filemakerpros.com/Splitting.zip

But why not poke into my approach to the present problem with the relational structure as well??

--sd

SansLU.zip

[comment]

I was just looking for a faster way to automatically create records in the AssignmentResults table after creating an Assignment.

First, there's no need to create records in advance. They will be created as the teacher enters the scores. There are several ways to do this conveniently, here's a simple one:

You will need a global field in the Students table holding the current AssignmentID, and a relationship from Students to a new TO of AssignmentResults:

Students::StudentID = CreateResults::StudentID

AND

Students::gAssignmentID = CreateResults::AssignmentID

Allow creation of records in CreateResults.

Make a list layout of the Students TO, and place the Score field from CreateResults on the layout.

Now, when it is time to enter the scores, run a script (from the selected assignment record) first, put the selected AssignmentID into a variable or script parameter. Next, GTTR to the list layout of Students, creating a found set of that class. Finally, set the gAssignmentID to the selected AssignmentID. Now you can simply fill the scores, creating records as you go.

EDIT: If you prefer to have EnrollmentID instead of StudentID as the second foreign key in AssignmentResults, you can use the Enrollments table instead of the Students table - it's pretty much the same thing.

---

Søren:

I did not understand your demo - I suspect you're talking about a different situation. Here, the assignment is given to an entire class, not to individual students.

Edited April 21, 2006 by Guest

[Søren Dyhr]

Here, the assignment is given to an entire class, not to individual students.

This is where the knowledge of the studentID's (from enrollment) comes in, by say ValueListItems( or GetNth( they are stuffed into a textfield that gets split by a repeating calc'field utilizing Get(CalculationRepetition) and import between tables.

It's not in the template yet, admitted!

--sd

[comment]

I think I may be beginning to understand where you are going with it, but the relationship still can - and IMHO, should be - as outlined by OP above. That is, there should be only one enrollment record for each student-to-class occurence.

You can just as easily get the VL of enrolled students in the Assignment record, and import the 2 repeating fields into AssignmentResults. But it requires a layout with enough repetitions. And I'm not too keen on having core data depend on a script, either.

[Adam M.]

Brilliant! I already tried it and it works perfectly for entering scores. I decided to use a portal instead of a separate layout. Thank you!

[Søren Dyhr]

Students::StudentID = CreateResults::StudentID

AND

Students::gAssignmentID = CreateResults::AssignmentID

Yes thats a nice way to handle it!

but the relationship still can - and IMHO, should be - as outlined by OP above.

Except that Assignment_Result_by_Student is wrongly positioned

But then take a closer look at the apparent lookups.

Assignment Description shouldn't belong to both the tables for Assignment and Assignment_Result unless one of them is an unstored calcfield if it "...along the path" can't be seen. The work of a Lookup isn't required at all, and will burden the responsiveness ...unless the students should learn to benefit from chasing an ever moving target. In such a case should the agility belong to the student table instead. I suggests that if a value can't be seen "...along the path" then yet another TO instead of jumping into either lookups or unstored calcfields.

--sd

[comment]

Nor neccesarily wrongly, just less conveniently. And yes, some of the fields seem redundant.

[Adam M.]

Most of what you labeled redundant fields are calc fields. The only true redundant field is the date on AssignmentResults. The date has to be a lookup in order to separate grades into quarters using a relationship. Most everything else is a calc field. I used calcs in most cases so I wouldn't have to have so many TOs. If that's not a good idea, let me know of a better idea. I'm open to suggestions. You guys have way more experience than I do.

[Søren Dyhr]

If that's not a good idea, let me know of a better idea. I'm open to suggestions.

It escapes me, if it's mentioned anywhere the Anchor Buoy whitepapers - but when using it as work method isn't hessitation shown in as far as I can tell, neither have I investigated which method produces the slimmest file or behaves most agile in networked solutions ...but it could indeed be interesting to get it investigated.

But if you watch the Graph Rules video of Petrowskis, is the deployment of TO's "Thou shalt have as many TO's as need, the key to the graph is organization" ...By the way does he present a third way for positioning the "scores" ...take your time to watch it again:

http://previews.filemakermagazine.com/videos/541/GraphRules_full.mov

--sd

[Fitch]

As far as importing as opposed to a loop script, I would have to export first.

Sorry to get behind on this thread... No, you do not have to export first. Furthermore, you do not have to even go to the layout that is the target of the import. Just find the records you need, then Import, specifying the source and the target.

This works differently in a script than when you choose Import from the menu. Try it! I'm not asserting that this is the definitive solution to your current problem, but it's a great technique to know.