7 Replies Latest reply on Jan 21, 2012 9:35 AM by TedKraus

# using key fields to share calculated data between two tables

### Title

using key fields to share calculated data between two tables

### Post

HI.  New to this site, so please bear with me.

I am a teacher who has created a database for my grades.  I have a final project that is team based, and I would like to create a table where I can calculate the grades of the final project, one for each team, and then have that information show up in the main table.

I have successfully created a simple key field using sequencial numbers, but I cannot replicate it using a caluculated field.  Could someone walk me through this, or tell me that FMP does not allow this to be done?

Great forum by the way.  Very helpful.

• ###### 1. Re: using key fields to share calculated data between two tables

What tables have you created?

How are they linked in relationships?

Please explain what you want to do.

Why do you need to "repliate" sequential numbers with a calculation?

Explain what results you want to see as though your were working out the math "on paper" instead of in the database.

• ###### 2. Re: using key fields to share calculated data between two tables

I have created a database for keeping track of my dtudents grades?  I have successfully used to track "one-to-one" relationships. such as individual homework assignements within each students "grade sheet."  Within this table, I have created a layout for the final project, and then transfered the total from that layout to the summary sheet.  This semester, however, I hit a snag.

I have another class that is based on team performance.  I am trying to create a new table that will allow me to calculate the totals of each team.  I then plan to link the final grade field from this new table to the final grade field in the summary sheet layout of the Student grade table.  The relationships are linked by the team id primary key.

As for my comment on "repliating" sequential numbers, let me clarify.  First. I meant to type was replicate, not repliate.  Thanks for putting in quotes, I am sure it was very confusing.  Second, my point was not that I am trying to replicate the addition of a sequencing value, but to suggest that I have only been successful in creating a linked field that displays sequential fields.  As I clarrified, I am trying to link a calculated field from one table to another.

Ted

• ###### 3. Re: using key fields to share calculated data between two tables

Hmm, yes but I still don't really know the current set up of your database. I can guess that you have a table where each record represents one student. I don't know if you do or not, but if I were still teaching school, I would also have a table of assignmentsTestsQuizzesProjects so that I have this relationship:

Students::StudentID = Asignments::StudentID

As I understand from your posts, you then have organized students into teams and each team will recieve a grade that must them be applied in some manner back to the class grade for each individual student.

It's possible that you have this relationship:

Teams::TeamID = Students::TeamID

With a grade field defined in Teams to assign a grade for each team. Don't know if you have a single grade value for each team or if, like individual students, you have awarded multiple grade values for each Team. And does the team grade represent a student's entire grade or just a portion of it?

• ###### 4. Re: using key fields to share calculated data between two tables

would a set up like this work for you.  The blue boxes are different table occurrances of the Grade table ( a join table )  The red boxes are table occurrances of Assignments.

For group assignments you would create a team, assign students to that team.  Then when you grade that teams assignment you will be able to see the grade for each student through the student-team to grade relationship and individual grades through the student-grades relationship.

• ###### 5. Re: using key fields to share calculated data between two tables

thanks for the replies.  I will upload a image of what my relastionships look like.  There are still way to many fields in them, but you should be able to get the point.

The problem I was having was in defining the ID field as the primary key.  I figured taht out, and then everything started populating as expected.  I think my set up is similar to waht you have suggested.

• ###### 6. Re: using key fields to share calculated data between two tables

I have now posted a related question titled, "Calculating a Portal Column."  I am trying to get away from having to define field by specific assignments.  If you are interested, please take a look.

I appreciate the help.  I am new to fmp, and feel like I am about to make a huge leap in my uderstanding of the power of databases in general.

• ###### 7. Re: using key fields to share calculated data between two tables

Mark,

Here is the image of the datebase.  I realize that it is "busy" and not as streamlined as it should be, and once I get this worked out, I am planning to clean it up.