[Spencers]

In an upcoming event, we would like to track event particpants and their guests. We would also like to track the number of guests for each participant, assuming a participant has registered a guest.

I have tried the running total field but this duplicates the particiapnt rows in the details section. Our events manager would like the information to appear in one row and would like to sort by partipant last name. A second thought was to group by the participant who registered, in RE it's called the "Guest of" field. This solution won't work because the report is then first sorted by the group instead of the participant last name.

Any ideas on how to count the number of guests and keep the information in the details section?

Thanks for any ideas or advice.

[nak]

Spencer,

I use a group for the ParticipantID in ascending order (it puts everyone in alpha order by last name) and all of the participant information is shown in that group's section. To count the number of guests, there is a Running Total (sorry Drew & Brian, but it does the trick) .

The total is a Distinct Count of the Guest ID field, Evaluate by formula (Registration = "Registered" because we only count participants and guest marked "Registered") and Resets On Change of Group for the ParticipantID group.

To get the guests names to show up in the row beside the participant's name (and for multiple guests, lined up in the following rows), the next group is based on the GuestID and the ParticipantID group is set to underlay the following section.

Hope this helps,
nak.

[Spencers]

Spencer,
 
I use a group for the ParticipantID in ascending order (it puts everyone in alpha order by last name) and all of the participant information is shown in that group's section. To count the number of guests, there is a Running Total (sorry Drew & Brian, but it does the trick) . 
 
The total is a Distinct Count of the Guest ID field, Evaluate by formula (Registration = "Registered" because we only count participants and guest marked "Registered") and Resets On Change of Group for the ParticipantID group. 
 
To get the guests names to show up in the row beside the participant's name (and for multiple guests, lined up in the following rows), the next group is based on the GuestID and the ParticipantID group is set to underlay the following section.
 
Hope this helps,
nak.

Thanks for the reply. I'm assuming that by grouping in this way, it would sort by the grouped name first and then any guest of that group. This may work. Thanks again for your reply.

[stancliff]

Spencers,

I've been laboring on a similar report for quite a while now. Here are some things to look out for.

In my system, response is a required field for a participant but is not required for participant guests. Therefore, if any of your guest responses are NULL, but they are displaying on your report, you're not getting an accurate count unless you are accounting for nulls. I made the decision to count NULL responses from guests as affirmative responses, because I reasoned that they wouldn't have been entered in the first place if they weren't attending, and the user must have forgotten to set the response.

Another pitfall is displaying guests when the participant invitee has declined. A rare case indeed, but could theoretically happen, if say, a week before your event, invitee Bill Jones calls up and says he can't make it, but his guest Peggy Sue, is still going to go.

When I have my report in front of me tomorrow I'm going to see if there is any useful code to share on how I calculated total # attending and # of guests for each participant.

[stancliff]

Quote:

Originally Posted by Spencers

Any ideas on how to count the number of guests and keep the information in the details section?

Thanks for any ideas or advice.

After putting more thought to this, I don't know how you can have the # of guests attending in each row. Only the Participant has a guest count. The guests don't have a guest count.

[Spencers]

Quote:

After putting more thought to this, I don't know how you can have the # of guests attending in each row. Only the Participant has a guest count. The guests don't have a guest count.

Thanks for your reply. It's much appreciated. In lieu of grouping the participants and their guests, I thought I could add a running total field that would count guests. First, I created a formula field that would display the last name of the participant who registered all guests for the event. This field was called RegistrantName and was based on the "Guest Of" field in RE. In the running total field setup, Guest Name was summarized by count, evaluated whenever RegistrantName did not equal Participant Last Name and reset on change of field RegistrantName. This logic does not work as the particpant rows are duplicated.

[nak]

Quote:

Originally Posted by stancliff

Spencers,

Another pitfall is displaying guests when the participant invitee has declined. A rare case indeed, but could theoretically happen, if say, a week before your event, invitee Bill Jones calls up and says he can't make it, but his guest Peggy Sue, is still going to go.

This is a major problem of reporting for events! The CEO INSISTS on seeing guests with their host, but when the host declines later on or (even worse!) doesn't show up at the event, then the guest who did register/attend ends up in the declined/not attended section. Which means that I end up spending an hour finding the wrong data and another hour explaining why we can't manage to do anything right the first time. Now that guests can be converted to registrants in the participation record, it is easy to clean-up (and someday people will learn to double check before they walk into the lion's den, right?!)

[nak]

Quote:

Originally Posted by Spencers

Our events manager would like the information to appear in one row and would like to sort by participant last name.

Any ideas on how to count the number of guests and keep the information in the details section?

Just out of curiosity, does your event manager work in Crystal or does s/he just look at the final report? I (finally) learned (as in, really got it!) that none of my users (past, present and willing to take odds on the future!) care about how the report works on the underside, as long as it follows the layout they have in their head (and, hopefully, on the piece of paper they provided)!

But if you have to put it all in the details sections and you have a group based on participant name, then couldn't you just have a running total of Guest ID Key and evaluate with a formula where the guest ID does not equal 0 and reset on the participant name group and just drop into the details where you need it?

BTW, I only need to have guests in their own group because we can have up to 15 guests per person and we want them to line up in rows beneath.

[Spencers]

Quote:

Just out of curiosity, does your event manager work in Crystal or does s/he just look at the final report? I (finally) learned (as in, really got it!) that none of my users (past, present and willing to take odds on the future!) care about how the report works on the underside, as long as it follows the layout they have in their head (and, hopefully, on the piece of paper they provided)!

No, the event manager does not work in Crystal. Right now, I'm focussed on having she and her assistant move away from the Excel spreadsheet and enter this in Raiser's Edge. My part of the bargain is to create the needed reports.

Quote:

But if you have to put it all in the details sections and you have a group based on participant name, then couldn't you just have a running total of Guest ID Key and evaluate with a formula where the guest ID does not equal 0 and reset on the participant name group and just drop into the details where you need it?

I will give this a try.

Quote:

BTW, I only need to have guests in their own group because we can have up to 15 guests per person and we want them to line up in rows beneath.

It's the desire of the event manager to have an alpha listing of participants as it's easier for them to check registration if all the partiicpants are lined up in alpha order. Evidently, participants are asked to line up in groups with last names ranging from A-D, E-F, etc...This seems to have worked well in the past so for this first report, I've opted to not change their methodology. However, I could see this changing as more events are recorded outside the standard Excel spreadsheet.

Thanks again for all your comments and suggestion.

[nak]

Quote:

Originally Posted by Spencers

It's the desire of the event manager to have an alpha listing of participants as it's easier for them to check registration if all the partiicpants are lined up in alpha order. Evidently, participants are asked to line up in groups with last names ranging from A-D, E-F, etc...This seems to have worked well in the past so for this first report, I've opted to not change their methodology. However, I could see this changing as more events are recorded outside the standard Excel spreadsheet.

Spencer,

I think I was totally confused about the purpose of your report. Our registration list is a straight alpa. listing of all participants (registrants and guests). We have guests listed in their own line and then include the a column called "Guest Of", but if you group by participant name, then placing the guest name beside the host isn't a problem.

As for the number of guests, RE has the export field for Number of Guests. You need to change it from text to a number and to make sure it is only counting participants that are guests (It is a weird field. If you are a guest, then it counts you as a guest of yourself).

If {PrtGst_1.PrtGst_1_ID} <> " "
Then If NumericText ({Prt.Prt_No_of_Guests}) = True
Then ToNumber ({Prt.Prt_No_of_Guests})
Else 0

I'm sorry if this has become confusing. Event reports are the ban of my existence every other month, and this month they are working well!

[Spencers]

Quote:

As for the number of guests, RE has the export field for Number of Guests. You need to change it from text to a number and to make sure it is only counting participants that are guests (It is a weird field. If you are a guest, then it counts you as a guest of yourself).

If {PrtGst_1.PrtGst_1_ID} <> " "
Then If NumericText ({Prt.Prt_No_of_Guests}) = True
Then ToNumber ({Prt.Prt_No_of_Guests})
Else 0

That worked perfectly. Don't know how I missed that field.

Thank you...