Counting Related records based on multiple criterias

[innodat]

I know there's an easy way to count related records: Count(RemoteRecordID). But what if I need to count only those records, which match a criteria in a second remote field?

I have Events with Positions which need to be assigned to Volunteers. I need to count all Positions which have not been assigned yet. There's a field on the Position record which states Assigend: yes/no. The information is there, but how can I integrate it into a Count calculation?

[IdealData]

You need another relationship to suit the match you need to count.

In the PARENT TABLE create a FIELD 'AssignedStatusYes' which you can seed it with the value "Yes" as an auto-enter.

Equally, another field 'AssignedStatusNo' which you can seed it with the value "No" as an auto-enter.

These fields can then be used to form 2 relationships to the CHILD TABLE and you can then COUNT either of them as needed.

Please note your existing data will not have the new fields with data so you will need to REPLACE them manually first.

[innodat]

Thank you!

I don't know why I was trying to add the additional criteria in the calculation - of course it needs to be done in the relationship.

Works like a charm!

[comment]

You need another relationship

Not really - you could count how many times "yes" appears in List ( Positions::Assigned ), using the existing relationship.

Or, if the Assigned field were a Boolean (which is always preferable, IMHO), you could get the same result by simple Sum ( Positions::Assigned ).

However, there's something bothering me about this Assigned field: where is the actual assignment? If the assignment is done in the Positions table itself (by filling in a VolunteerID foreign key field), then what do we need the Assigned field for? The presence of a value in VolunteerID provides sufficient indication of the status - and a count of Positions::VolunteerID can provide the required stat.

In the PARENT TABLE create a FIELD 'AssignedStatusYes' which you can seed it with the value "Yes" as an auto-enter.

If you really must filter this way, an unstored calculation field would be a better choice IMHO - and it wouldn't require populating existing records.

[IdealData]

You are quite right comment, however I was answering the question in the way it was asked and presenting the solution in style of the question.

I did consider the SUM option, but so often I see answers to questions that didn't address them in the way the questioner would understand and then we end up with a massive debate about the pros and cons of various choices.

That said, you are still very correct and I agree that your solution is potentially better.

[comment]

Of course - but it's not about you or me. It's about choices - and it's up to OP and anyone else reading this to decide which suits them best. A "massive debate about the pros and cons..." is generally a good thing, IMHO.