1 2 Previous Next 21 Replies Latest reply on Jan 31, 2011 9:50 AM by BG_1

# Calculating Change Between Two Records Out of Many

### Title

Calculating Change Between Two Records Out of Many

### Post

Hello everyone,

I have data from students who took a test before taking a class and after.  I am looking for changes in attitude of each particular student (meaning I don't want to calculate the total average change in attitude from pre-test to pro-test). I tried to use a pretty standard survey set-up, using the forums as a guide.

It is set up so that each question has it's own record. Each student has his/her own ID#, and will have records for every questions they filled out times 2 (since they took a pre-test and post-test).  What I need to do is have Filemaker calculate the changes in the 73 questions (from pre-test to post-test).

I don't know how to tell filemaker to do this.  Put in more mathy terms:

If Duplicate Student ID exits, Take the Difference of Q1 in Pre-Test and Q1 in Post-Test = Attitude Change

If Duplicate Student ID exits, Take the Difference of Q2 in Pre-Test and Q2 in Post-Test = Attitude Change

If Duplicate Student ID exits, Take the Difference of Q3 in Pre-Test and Q3 in Post-Test = Attitude Change

....

The fields I am using:  ID (Student ID), QID (Question Number, not result), Answer (Student Response)

What makes it hard to do for me is that I need it to check for multiple things.  1) It must know to find identical ID#s to compare them.  2) When duplicates are found, I need compare submitted answers of between pre-test and post-test.

So, it's basically checking for the same person, then the same question, then it can finally compare the Answer Fields for those 2 particular records.

Sorry for the mess, but it's the best way I can describe it, thanks!

• ###### 1. Re: Calculating Change Between Two Records Out of Many

Just to add:  QID is not what the student responded to, it identifies that it is question X.  The "Answer" field within that particular combination of QID and ID record must be compared with the record with the same QID and ID.  There's another field that says whether it is a pre-test and post-test.

Record X:

1. ID:  1234
2. QID:  1
3. Test:  Pre
4. Result:  4

Record Y:

1. ID: 1234
2. QID:  1
3. Test:  Post
4. Result:  6

As you can there, there are many, mana records in this database.

• ###### 2. Re: Calculating Change Between Two Records Out of Many

Relationships can match on more than one field.

Responses::ID = PreTestResponses::ID AND
Responses::QID = PreTestResponses::QID AND
Responses::cPreKey = PreTestResponses::Test

cPreKey would be a calculation field that returns the text "Pre". PreTestResponses would be a table occurrence of Responses.

And now you can perform calculaitons such as Responses::Result - PretestResponses::Result to take the difference between the two responses.

If "table occurrence" is a new term, see here:  Tutorial: What are Table Occurrences?

• ###### 3. Re: Calculating Change Between Two Records Out of Many

After reading your response and the link, I think I understood more of what you're saying.  However, I still don't quite understand the point of the cPreKey calculation field, and relating it to the "Test" field in the PreTestReponses table occurance.

Glancing through my database, the cPreKey field always returns Pre (as expected), and the sample calculation uses always returns the same thing as the initial response.  I'll do some more reading to see if I can figure out how this works a bit better.

Thanks!

Edit:  To add, I just wanted to mention that the comment on my ExampleCalcultion field says, "Unstored, from Responses, = Responses::Answer - PreTestResponses - Test"  I'm not exactly sure what unstored means, but I'm pretty sure it's not returning an expected value.

Edit2:  Ok, I think I understand why the calculation was not working.  It is because of me not understanding what they cPreKey did.  When I gave the example, I simply put "Pre" and "Post" when in reality the code was "BIO 111 WI 09" for Pre and "BIO 112 SP 09" for Post (I just wanted to make explaining my database easier, when in fact, it caused trouble).  That misunderstanding was completely my fault, but after switching it around, it now seems to be working.  The cPreKey field is now outputting a "?" but I don't think that matters, as I am going through the database manually and am getting the expected results.

The only thing I'm slightly concerned is that when I am looking at a Pre test, it seems to be taking the difference of itself.  That's totally acceptable, since getting a value there is irrelevant (it's always going to output 0).  When I create my results layout (this website had a very useful guide on how to do that), I'll just make sure to only output the differences for the "Post-Test" calculations.

• ###### 4. Re: Calculating Change Between Two Records Out of Many

Creating a Results Report yielded some unexpected results.  I wanted to have all the results sorted by ID number.  Underneath this, I would have the Question #'s right before the Calculation Resut, so I could see what the change was for each of their questions.  It took the format of:

H: ---------

S: ID

B: QID   Calculation Resut

F: ---------

I made sure to sort by ID, then QID but when I entered Preview Mode, each ID seemed to have 8+ of the same QIDs for each of the 73 questions.  It basically ended up looking like this:

ID# 1234

Q1    1

Q1    2

Q1   -1

I don't know why it would do this. (Edit:  previously, the picture looked like it was correct, i.e. showing one of each question, instead of multiples of the same question).

• ###### 5. Re: Calculating Change Between Two Records Out of Many

On what table is your report layout based. The table of questions or the table of responses?

• ###### 6. Re: Calculating Change Between Two Records Out of Many

It is based off of the table of responses.  I thought that would be ok, since that table includes IDs, QIDs, and the Calculation Resuts.

• ###### 7. Re: Calculating Change Between Two Records Out of Many

The question here, is why do you see three response records for 1 student ID, for a given question ID?

You'll need to examine your data and your relationships to understand that. Unless you performed a find to filter out all Pre test repsonses, I would expect to see a maximum only two records under each QID sub summary part (I think that's what you have here), if you are sorting first by Student ID and then by Question ID.

• ###### 8. Re: Calculating Change Between Two Records Out of Many

I had a script that filered out all pre-tests and then sorted it by UID, then by QID.  The only sub-summary part I used was ID.  I tried using QID as the next sub-summary but the results weren't much better.  Even when I scroll around to find if any entries seemed normal and found one that didn't have repeats, the ID was missing and a "?" was displayed.

I have gone back to my Results table and checked the data.  After searching that way, there were never more than 2 repeats of each QID.  There must be something wrong with how I have the report set up.

• ###### 9. Re: Calculating Change Between Two Records Out of Many

I suggest adding extra fields to the body to display, UID, QID and your test field (the one I originally thought had "pre" or "post" in it.) That should give you a clue as to why you are getting multiple response records with each QID after first sorting by UID. You can remove these later, once you've figured out what is going wrong.

Keep in mind that the type of your field (Number, text or date ) will affect how the records sort. (Numbers sort by different rules than text does).

• ###### 10. Re: Calculating Change Between Two Records Out of Many

Great call there.  After trying that out, it seems that even though I have the first sub-summary as UID (which should sort them into different sections), it is including other IDs.

I don't quite understand why it won't keep these IDs separate.  I'm not sure if this is a possible cause, but some of the IDs aren't actually full ID#s.  For example, students that did not put their ID# (which is U########) where given a fake/substite value of A 09 (09 is the year of the test), B 09, C 09, etc.  But I honestly don't know why that would cause trouble anyway.  Is Filemaker unable to "see" a difference between these numbers?

Edit:  It probably has something to do with these fake IDs, since they seem to be the only ones that are not being displayed properly.

• ###### 11. Re: Calculating Change Between Two Records Out of Many

Sub summaries don't actually sort your data. It controls what you see if you do sort the data in a sort order that includes that field. Make sure you have actually sorted the data into the order you need. The layout will not sort the data for you automatically.

Note that what you are describing is text not a number as you are including letters with the numbers. If the field is of type number, the non numeric characters entered into it will not be affected by any sorts that you perform.

Thus, A 09, B 09, C 09 in a number field will all be treated as though they have just 9 entered into the field. (This will also affect relationships that match on this field.

• ###### 12. Re: Calculating Change Between Two Records Out of Many

Sorry, I misspoke.  I understand that the information must be pre-sorted, I should have just said that the Report wasn't working the way I wanted it to.

Crap, this will be a tough fix.  Since Filemaker won't be able to sort or recognize them, I'll most likely have to rename them manually.

Edit:  I suppose that since I can isolate the fake IDs, I could theoretically change their field type to letters.  This way, I could sort by letter and use scripts to replace the fields.  However, I don't know if you're allowed to change a field type for a select few while keeping the others the same.

• ###### 13. Re: Calculating Change Between Two Records Out of Many

FileMaker can, sort and match values, you just need to use the correct field types. If the data includes non-numeric characters, use fields of type text instead of type number.

I would think you could use a text field for all your records, not just these special cases. (or you find and fix the records with this issue so that letters are no longer used.)

• ###### 14. Re: Calculating Change Between Two Records Out of Many

I corrected all of the improper ID #s with scripting.  When viewing my report ( with sub-summary ID#, then sub-summary QID#, with all of my records sorted by ID# and then by QID#), now all I see is "?"s for my UIDs.  Everything else seems to be displaying correctly.  I'll continue researching, but I thought up a few questions.

1)  When sorting, does exluding records distort a report?  I know a specific range will not have useful data, so I've been exluding those

2)  Is it ok to have 2 sub-summaries?

3)  When sorting, I'm assuming that I sort by the first sub-summary part, then by the second.  Is that correct?

Thanks again!

1 2 Previous Next