Custom Report not picking up specific Activity records

[summitchurch]

We have created two custom reports. They are the same except one report only picks up personal profiles that have Activity code '30' attached to their record and the other report only picks up personal profiles that have Activity code '31' attached. The first report runs perfectly. The second one, which is a copy of the first with the Activity code parameter set to '31', pops up with a message that no records can be located for that Activity. Yet, we can run a standard attendance report for Activity '31' and everyone shows up. Any ideas why the custom report query cannot locate the records?

[NeilZ]

We have created two custom reports. They are the same except one report only picks up personal profiles that have Activity code '30' attached to their record and the other report only picks up personal profiles that have Activity code '31' attached. The first report runs perfectly. The second one, which is a copy of the first with the Activity code parameter set to '31', pops up with a message that no records can be located for that Activity. Yet, we can run a standard attendance report for Activity '31' and everyone shows up. Any ideas why the custom report query cannot locate the records?

Try running a report pulling all the activities, and see exactly what the parameter is .. what you think may be '31' may actually be something else.

[summitchurch]

I ran the Activity / Skill Descriptions Report and under the column for Activity are listed:

30 Summit Kids - 1st Service
31 Summit Kids - 2nd Service

Are the '30' and '31' the correct parameter for Activity when I set the 'Activity equal to ... ' in the Record Filter of the Custom Report? I have it set as 'Activity equal to 31' and it tells me there are no records that match.

[NeilZ]

I ran the Activity / Skill Descriptions Report and under the column for Activity are listed:

30 Summit Kids - 1st Service
31 Summit Kids - 2nd Service

Are the '30' and '31' the correct parameter for Activity when I set the 'Activity equal to ... ' in the Record Filter of the Custom Report? I have it set as 'Activity equal to 31' and it tells me there are no records that match.

You would think so .. but try running a quick report using Activity Number is between two numbersuse 29 & 32, then as part of the output select Activity Number and Activity Description along with the Personal profile name

See what pops up !!

[summitchurch]

I set the Record Filter to Activity Ranges from 29 to 32. Then added the Activity and Activity Description to the Output ... in front of all the other output field columns. I ran the query and ended up with all the Activity 30 - Summit Kids - 1st Service records ... none of the Activity 31 - Summit Kids - 2nd Service records. Strange?

[summitchurch]

Is it possible that when I defined the Activity code '31' for Summit Kids - 2nd Service that I may have gotten a space or special character in with the number? Or does the software force the 'code' to be numeric?

[summitchurch]

I just tried a couple of things:

1. Set up a new Activity (40) and assigned one of the kids to it. Then changed the custom report filter to list records with Activity Ranges 29 - 41 and it STILL only picked up the profiles assigned to Activity 30.

2. Re-ran the Report and changed the filter to Activity Department then selected Children (both the Summit Kids Activities are under the Children Activity Department) and, again, only Activity 30 records showed up.

Hmmmm ...

[NeilZ]

And that Activity Number is the ONLY filter being used ??

[summitchurch]

Yes. I am a troubleshooter by nature and am baffled because I can't see anything that might be causing it to not see Activity 31. I even copied the report that is for Activity 30 and only changed the Activity filter to 31 ... so everything is the same except the Activity number. I can go into the maintenance area for Activities ... locate Activity 31 and view all the members but I can't get the custom report to find them. I am not sure what to try next. My Children's Ministry Director needs an attendance form for each service. The one provided in the Reports area with the Full Details format would be perfect EXCEPT it doesn't have the birth date; that is why I am trying to build a couple of custom reports.

[NeilZ]

Quick check ... get a name of someone who is supposed to be in that activity 31.

Go to their personal profile, and click on the activity button to make sure that they areo assigned to that activity.

But before that, do a reindex on the database ... couldn't hurt ..

[summitchurch]

Ok. I found Astrid and checked her personal profile ... in the Activities tab she was listed under both Activity 30 and Activity 31. Then I tried the report ... no luck. Next I went through and re-indexed all files. Then went back to the report and it still didn't pick up activity 31.

Is it a problem if one child is set up with both Activities? In the custom report I DO NOT have the 'hide duplicate records' checked so, in theory, I should be seeing most of the kids listed twice in the report (where Activity ranges from 29 - 32) since most of them are assigned to both Activities, right?

[NeilZ]

Ok. I found Astrid and checked her personal profile ... in the Activities tab she was listed under both Activity 30 and Activity 31. Then I tried the report ... no luck. Next I went through and re-indexed all files. Then went back to the report and it still didn't pick up activity 31.

Is it a problem if one child is set up with both Activities? In the custom report I DO NOT have the 'hide duplicate records' checked so, in theory, I should be seeing most of the kids listed twice in the report (where Activity ranges from 29 - 32) since most of them are assigned to both Activities, right?

It should not be an issue if you have someone assigned to two activities. Time to bring in the techs, they may see something that I'm missing.

[Zorak]

There shouldn't be a problem reporting on people that are in multiple activities. I just ran a test for a range of two activities and people showed up in the results whether they were a member of one or the other, or showed up twice if they were in both activities.

What fields do you have being output in the report? Some of these things have a strange join relating the database tables behind the scenes and sometimes records can be excluded from the results because of null values in the data.

For example, you choose to output name, address, and activity role, and people would be excluded that don't have a role assigned. That isn't a real world example with those specific fields, but I have seen that kind of thing before.

[summitchurch]

The Output Fields are 'Last Name, First Name', Birth Date, Mailing Name, Family Phone 1, Family Email, Allergy, Medication.

The strange thing that seems to dispute the empty field theory is that I run the exact same report for Activity 30 and it brings up the list of children correctly. The SAME group of children are also assigned Activity 31 so you would think the report would bring up the same list of children instead of not finding them. (we have two services we like to keep attendance separately)

With the idea that maybe something went wrong when I set up Activity 31 (maybe an invisible special character), I set up Activity 40 and assigned one of the children to it then ran the custom report with the range 29 - 41 and it still didn't pick up the kid.

[Zorak]

Stumped. If you like, you can export the custom report from under Utilities > Import/Export Custom Reports and e-mail that file to us at data@powerchurch.com. A second step would be for us to get a backup of your Membership data to test with. We can look at the report first to see if there is anything that jumps out at us, but my guess is there is something data related, since it appears the report is working fine for the other activity.