[sims] SIMS ordering - priority email

[shaunbch]

I am running a report based on pupils on roll, which pulls back the emails for parents/carers. In the output, it always puts the priority 2 email first, and then the priority 1. I can't figure out a way to sort it so that priority 1 shows first - it doesn't offer up priority as a sort field, even though it's one of the fields I pull for the query. Is there a way?

 

 

 

[synaesthesia]

That sort of thing is generally easier in spreadsheet - so run the report and export to spreadsheet and use the sort function there to work it to your whim.

[David44]

I'm not sure that report makes sense, does it? You are fetching all the contacts, then running a subreport of each student attached to each contact, so a student with a Mother, Father and an Uncle will appear on the report three times already and then for each student you are getting their parents priority.

 

If you make a Student report and then have a contacts subreport, each student will be shown once and each of their contacts will be listed once (there will be duplicates if the student has a sibling with the same parents). You can then order by priority in the contacts subreport. Something like this...

 

[shaunbch]

That is working better, thank you! Priority 1 is appearing first, which is helpful.

 

 

However, I have added filters for FSM = Y, priority = 1, parental responsability = Y, and email = not null.

 

 

 

The FSM filter is working, it's pulling back rows that fail the other filter rules:

 

 

 

Any idea why?

[David44]

I think this is because you are asking for students who are eligible for FSM (which isn't all students, so this bit appears to work) where contacts exist, parental responsibility is true, priority is 1 and the email address is not null. If you think about it, ALL (most) students will have at least one contact that has PR, has P1 and has an email address. Your filter as it stands, IS working, it's just returning all (most) of your students because they all (most) meet that criteria.

 

What you probably want is to filter on the Contacts rather than the Students. Remove the current filter except the FSM part, go back one step to the 'Select Data Fields' part of Report Designer, click on 'Contacts' and then click on this little fella... and set your filter up against the Contacts rather than the Students. You probably want to untick 'Include records with no such Contact' as well so that it only returns students that meet the Contact criteria, unless you want to identify the students who don't meet that criteria too.

 

Does that make sense?

[shaunbch]

Ah, that is brilliant, thank you! I didn't realise you could add a filter at the 'Select Data Fields' stage. Running beautifully now!