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.
- ID: 1234
- QID: 1
- Test: Pre
- Result: 4
- ID: 1234
- QID: 1
- Test: Post
- Result: 6
As you can there, there are many, mana records in this database.
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?
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.
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.
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:
B: QID Calculation Resut
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:
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).
On what table is your report layout based. The table of questions or the table of responses?
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.
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.
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.
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).
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.
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.
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.
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.)
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?