Vertical Portal

[Cory_nz]

Hi People

Is it possible to get a vertical portal, instead of the default horizontal format in FMP?

I've got student assessment results that I'd like listed vertically by attempt. So x axis would be "attempt #" and y would be "question #". The resulting data would therefore be result.

Any ideas?

thanks

Cory

[bcooney]

Sounds like a list view with subsummary parts or a cross-tab report, which is difficult in FM, but can be done: Edoshin's Cross-tab technique.

Edited September 4, 2009 by Guest

[Cory_nz]

Thanks for the link.

Looks like it could work - only difference is my main data set is text, and not numbers. The example in the link does a number of calculations and summarys, so not sure how I can get it to work.

Any advice?

thanks

Cory

[comment]

It would help if you explained - in detail - what your data is, how do you want to see it and esp. how much of it is there in each dimension (portals do not automatically add columns - nothing in Filemaker does).

[Cory_nz]

ok - here goes:

A student sits an assessment, which is made up of a number of questions. It is marked, and a result is given to each question. A student can sit an assessment as many times as is necessary for them to get 100% correct. Answers are marked purely by "Competent" or "Not Competent" so the results are text, not numbers (i.e. 85%).

What I would like is to have a report that shows along the top x axis Attempts, y axis Question Number (just a label), and the data set within the report will be the result of the question or Q_00x as I've called - data displayed is "NC" or "C" accordingly. The report needs to show only the data on one student, and one assessment - as students will have many assessments.

This way, we can see at a glance the total results a student has had with the one assessment. I want to go horizontally as the question label can be relatively long i.e. "Task 3 - Tutor Observations" and I want to keep the layout clean.

Table/Date Info:

ASSESSMENT

_fk_student_id->link to student table

attempt->number starting from "1" and increasing at each new attempt

_pk_assessment_id->primary key

unit_id->name of assessment (i.e. 111 - is stored as text as it's the name of the assessment)

MARKING

_fk_assessment_id->foreign key linking to assessment table

Q_001->the result of the marked question - "NC" or "C"

Q_002

...

Q_015

With the number of questions, I've found the assessment with the highest number of questions (around 15), and have made that the highest number. So for assessments that have less numbers of questions, (i.e. 7) they will populate Q_001 - Q_007, and then leave the rest blank.

I think I've covered most of it off, but from previous experience I've probably missed a bit, so if there's any confusion, just ask.

thanks for your help in advance

/Cory

[comment]

Before I can get into this, I am missing a table somewhere.

You say an assessment is made up of a number of questions - and a student can sit an assessment many times. To me, these are two different entities: (1) a set of questions; (2) an attempt by student to answer a set of questions.

I believe the described situation should be modeled as shown in the attached ERD.

[Cory_nz]

As an assessment comes in, it is stored in the ASSESSMENT table. Everything about the assessment is stored there i.e. unit_id (effectively the name of the assessment), and the attempt.

When it comes time to actually marking that assessment, those details are stored in the MARKING table. Along with the details of who marked that assessment, the marking results are stored in there as well. If an assessment only has 5 questions, then 5 out of the 25 question fields (or basically result holders) will be filled in - the rest are left blank. Another assessment will have 20 questions and so will populate 20 results, leaving 5 blank.

I don't need to store the actual questions anywhere. All I need is the results, which is why they've been included in the MARKING table.

So in answer/response to your statement of two entity's - yes they are, and have been set up accordingly.

[comment]

I am sorry, but I am still not entirely sure I get it:

Suppose student 001 took 2 units. He attempted Unit 101 once, and Unit 102 twice. IIUC, there would be 3 records in the ASSESSMENT table:

AssessmentID: 1

StudentID: 001

UnitID: 101

Attempt: 1

AssessmentID: 2

StudentID: 001

UnitID: 102

Attempt: 1

AssessmentID: 3

StudentID: 001

UnitID: 102

Attempt: 2

Now, let's say Unit 101 has 5 questions, and Unit 102 has 10. How many records are there in the MARKING table?

IMHO, there should be 25 such records (1*5 + 2*10). But you say the marks are FIELDS - so I understand that you only have 3 records in the MARKING table. IOW, the relationship between ASSESSMENT and MARKING is one-to-one.

[Cory_nz]

Yeah - you're right. It was always meant to be but I guess during the development it slipped through.

If I shift the 'attempt' field over to the MARKING table, then it will resolve the relationship.

[comment]

OK, so what now? There will be two records in the ASSESSMENTS table (which actually becomes Registrations) and 3 records in the MARKING table (which really means Attempts)?

[Cory_nz]

Well wouldn't everything else still stand? The results of the assessments are stored in the MARKING table.

Bringing it back to my initial query, I want the results to be able to displayed vertically, with the header row being the attempt number.

I can get it done easily enough horizontally, using a sub-summary sort. But I want it all to be displayed vertically - as it will make the overall layout design better.

[comment]

Well wouldn't everything else still stand?

I don't know - I haven't yet got to the part where everything else was clear to me, even before this change. And I am having a hard time getting it clear, because on one hand part of my brain keeps screaming this is wrong, and on the other hand I am not getting responses to my questions.

[Cory_nz]

Sorry - missed this one:

You asked: There will be two records in the ASSESSMENTS table (which actually becomes Registrations) and 3 records in the MARKING table (which really means Attempts)?

In response: Yes - that sounds about right.

Have I missed any of your other questions? What do you think doesn't sound right here?

[comment]

Have a look at the attached file. I don't see another way of doing this with your current structure without jumping through major hoops.

I believe that marks should be in their own table (a child of Attempts), with each mark being a separate record.

I am not saying that doing so would make the issue at hand trivial. As I said earlier, Filemaker is not flexible in the horizontal direction - and with a variable amount of attempts there will be always the problem of adjusting the number of columns.

But if it were possible to address the marks individually, there could be another way - perhaps by using a custom function or a script to generate the report in a text field.

Attempts.fp7.zip

[Cory_nz]

Thanks for that! That file was very helpful!

Seems the answer was quite simple in the end:

Separate portals showing only 1 row, but starting from sequential rows.

The fact that we discussed everything BUT this tells me I need to work on my FM Communication skills :