Custom report logic help

I'm trying to write a custom report to identify which children (if any) are assigned to more than one primary class, but I can't figure out if there are adequate criteria to do this.

(If I could write raw SQL, this is easy.)

This arises from the fact that we've split primary classes into 5A/5B, 6A/6B, etc., because of their size. Assignments to the classes are arbitrary, so in MLS, the membership criteria in each class pair are identical.
So, when a new family moves into the ward, a 5-year-old is automatically assigned to both 6A and 6B. We go and manually clean this up when we print a new roster/roll ... but the process depends on us being smart enough to remember to check the new families. I'd like to just have a report ready that the primary secretary can check.

geek wrote:I'm trying to write a custom report to identify which children (if any) are assigned to more than one primary class, but I can't figure out if there are adequate criteria to do this.

There's not enough flexibility to create a single custom report to do this, but it's easy enough to create a report for each age group where there are two classes.

Create a custom report with criteria:
Organization Class is Valiant 11A
and Organization Class is Valiant 11B

That report will tell you everyone who is in both classes. Make a similar report for each of the other similar classes.

If custom reports were a bit more flexible, you could put this all in one report by creating logic which is basically

(Valiant 11A AND Valiant 11B) OR (CTR 5A AND CTR 5B) OR (Sunbeam 4A AND Sunbeam 4B).

But the AND/OR capability of custom reports doesn't allow that. Hopefully you don't have too many duplicate classes, so the first approach isn't too bad.

Another more general approach is to generate a list of everyone in the ward with their organization classes (which generates a single field that contains a comma separated list of all classes). You could then parse that using a spreadsheet with formulas, or some sort of scripting language to help you identify all duplicates. But that seems like more work.

Alan_Brown wrote:If custom reports were a bit more flexible, you could put this all in one report by creating logic which is basically

(Valiant 11A AND Valiant 11B) OR (CTR 5A AND CTR 5B) OR (Sunbeam 4A AND Sunbeam 4B).

But the AND/OR capability of custom reports doesn't allow that.

Interestingly, MLS custom reports do allow you to create logic that looks like
Class = 11A
and Class = 11B
or Class = 5A
and Class = 5B
or Class = 4A
and Class = 4B

I'm not sure exactly what MLS is doing in this case, but in my testing it seems to be ignoring the first 4 criteria and just showing me those who are in 4A and 4B. That seems like a bug. If MLS custom reports would simply adopt a standard boolean operator precedence scheme with AND binding more tightly than OR, then

11A and 11B or 5A and 5B or 4A and 4B

would be exactly the same as

(11A and 11B) or (5A and 5B) or (4A and 4B)

which (at least in this case) is what is desired. Fixing this would require no change to the MLS user interface -- just the logic used for multiple boolean operators.

Alan_Brown wrote:Interestingly, MLS custom reports do allow you to create logic that looks like
Class = 11A
and Class = 11B
or Class = 5A
and Class = 5B
or Class = 4A
and Class = 4B

I'm not sure exactly what MLS is doing in this case, but in my testing it seems to be ignoring the first 4 criteria and just showing me those who are in 4A and 4B. That seems like a bug. If MLS custom reports would simply adopt a standard boolean operator precedence scheme with AND binding more tightly than OR, then

11A and 11B or 5A and 5B or 4A and 4B

would be exactly the same as

(11A and 11B) or (5A and 5B) or (4A and 4B)

which (at least in this case) is what is desired. Fixing this would require no change to the MLS user interface -- just the logic used for multiple boolean operators.

I tried that, and it doesn't work. It outputs zero children. The idea that 11A and 11B or 5A and 5B or 4A and 4B equals (11A and 11B) or (5A and 5B) or (4A and 4B) doesn't apply to MLS 2.8.1.


I finally gave up and just wrote 7 reports (4A/4B up to 10A/10B).

I admit this is a problem we created for ourselves by splitting our classes ... but there *are* parts of the Church where this happens.

I will say that I'm grateful for Custom Reports. More flexibility would be nice, though.

geek wrote:I tried that, and it doesn't work.

I know. I apologize if I wasn't clear. The point of my first post was saying how MLS can be used right now. The point of the second post was proposing how MLS should be fixed to handle AND/OR logic in a reasonable way.

Thanks, Alan.

I'm finding that even the basic notion of a being able to write custom reports is enough to thrill the auxiliaries, so I won't lose too much sleep over it.

I believe that the logic MLS is using is something like (((((4A and 4B) or 5A) and 5B) or 6A) and 6B), which pretty much ensures that nothing will ever work.

Thanks for the sanity check. It's the strange 1% of my calling that keeps me busy on a Saturday...

Ever grateful...

geek

I see that this thread is a couple of years old but the issue still remains.

MLS tracks mission language and I have created a custom field where we track other foreign languages spoken by our ward members. This field is named, "Foreign Language(s) (List)."

I want to run a report that checks both attributes. However, when I create the report:
[INDENT][/INDENT]Mission Language - Has a value -
[INDENT][/INDENT]And Mission Language - Does not contain - English -
[INDENT][/INDENT]Foreign Language(s) (List) - Has a value -

The second attribute, Foreign Language(s) (List), is treated as an "AND". I want to be able to run it with an "OR". Will future versions of MLS will allow us to better utilize Boolean logic in custom reports?

In lieu of the "OR" command, I have created two reports, one to track each attribute separately. Is there a better solution at this time?

rketcheson wrote:In lieu of the "OR" command, I have created two reports, one to track each attribute separately. Is there a better solution at this time?

If you are comfortable with SQL, you might consider the application described in the thread Announcing LDSql -- it gives you much more flexibility in query logic. But I don't think it supports custom fields, so it might not be helpful for you after all.

Alan_Brown wrote:If you are comfortable with SQL, you might consider the application described in the thread Announcing LDSql -- it gives you much more flexibility in query logic. But I don't think it supports custom fields, so it might not be helpful for you after all.

Mission language and your own custom fields are among several elements available in MLS but not included in the standard export files that populate LDSql. Someday it might be possibleto extend the LDSql database with some custom-report exports includling such data, but that is not a priority now.

However, if you are SQL-handy yourself, you could export as CSV the custom reports you have created, import them into the LDSql database using a generic tool such as the SQLite add-on for Firefox, and write the query there.

Thank you both. The LDSql project sounds promising for the future. However, I want other ward members (Bishopric, Sunday School, Ward Mission Leader, etc)to be able to access these reports in real time. For now, I think for now I'll go with the, two custom reports option and write directions in the "Description" field to print the companion report.