multiple records with same id, need to combine

[littleblackdog]

I am trying to help out an elementary school create a report card from a grade program. The grade program exports the student grades in a flat file which I can import into FileMaker. Each subject ( Math, Reading ) comes in as a new record:
Example: Record 1:Name (John), ID (1001), Subject(REading),Grade( A+)
Record 2: Name (John), ID (1001)Subject(Math), Grade (B)

and so on. Each student ends up with 5-7 records, depending on how many subjects the teachers set up in the grade book program,.

** I will set up a template for the teachers after I get the report card working so they will use my temp. and I will control what subjects the create.

I am trying to combine all records imported ( ie. all records with same ID or same Name. I have sorted the records and they are grouped.

I then want to copy the multiple records with same ID or Name in Name field to one record ( new layout ), and copy/paste the subjects (Math,Reading) and Grade to the new record...so each student gets 1 final record, with all grade subjects on 1 record...

I can do this in php with a mysql, I know the process, but I feel so limited in FileMaker. The database is not queried as easily..

Does anyone have time to help on this project? An example script that would loop through the records, if the records have same ID or Name then pull specific field names - copy and paste into layout 2. Like: If Subject = "Math" then Copy from Layout #1 and paste into Layout #2 in Field "Math"... not correct syntax but thats the general idea. Then I'll need to set it up for different quarters or terms, but I can do that later.

Thanks for your time.
Robert.

 

[lebisol]

"then Copy from Layout #1 and paste into Layout #2 "

this still is working on the same file...Layouts are just Views of Design....it would be like creating antoher form and posting into and and then submitting it....( so would create even more dupl. records)
make sure u use another file NOT just antoher layout

there is way to script this but I suggest u 1st decide on your full DB setup....get the realtionships set up....
probably
1 StudentID---to---many-->SubjectID

ones u have the clear picture of DB structure u should try to export data:

import into Subjects.fp5-----------------------------
- sort based on UniqueID
so u can sort based on SubjectID or SubjectName (eg. Math)

=======================================
SubjectName:...:StudentID:..Grade..:
=======================================
Math.....Stud101... B
Math.....Stud204... C+
math.....Stud220... A-
History...Stud101.. C-

=======================================
then simple export all the Math Students and just leave out the Subject Name....ones you import them u can calsify them as 'Math'


BTW FileMaker will run wil PHP on the web ;-)
All the best!


> need more info?
:: don't click HERE ::