2

I'm currently setting up CiviCRM for a dance institution. They want to create an event for each lesson (weekly) and use CiviMobile on an iPad or something similar to manage attendance.

Now there's a need to group dance partners together. To this end I've set up an extra relationship called 'Dancepartner of' to tie them together.

An example:

  • Marie dances with Peter
  • Kate dances with Micheal
  • Emma dances with Adam

I would like the event participants view to look like:

  • Marie
  • Peter
  • Kate
  • Micheal
  • Emma
  • Adam

I think an SQL join for the relations and a GROUP_BY would do the trick but I have no idea where to begin coding this.

Can anyone point me in the right direction of am I going about this all wrong and should I perhaps use groups ?

Thanks in advance,

2

CiviMobile (new version) works nicely for checking people into an event, but I think it's going to do its own sorting regardless of anything you display in the normal Civi GUI.

If you're on Drupal then a view using the DancePartner relationship with a VBO might do what you need to display the partners and register attendance.

Do you want to register and check in individuals or couples?

If you are more interested in couples, then you could potentially repurpose/abuse 'household' as 'dance partnership' since you can register households as event participants. You could then use CiviMobile and check in the 'Marie & Peter partnership' household. You would need to check the rest of your workflow but it fits the objective of checking in a couple via CiviMobile.

| improve this answer | |
  • Thank you for your comment! I've been fiddling around with abusing the household for my purpose. It does work nicely for registering and checking in couples. It does create some additional problems with contact information and perhaps even payment issues. – M. van Son Nov 26 '16 at 21:12
2

Thank you all for helping me. I've decided to go a different route and started messing with reports and I was able to group dancepartners together using a SQL join.

I duplicated an existing report (attendee list) and modified the SQL WHERE clause to this:

  public function from() {
    $this->_from = "
        FROM civicrm_participant {$this->_aliases['civicrm_participant']}
             LEFT JOIN civicrm_relationship contact_a_relation
                        ON (participant_civireport.contact_id = contact_a_relation.contact_id_b )
             LEFT JOIN civicrm_relationship contact_b_relation
                        ON (participant_civireport.contact_id = contact_b_relation.contact_id_a )
             LEFT JOIN civicrm_event {$this->_aliases['civicrm_event']}
                    ON ({$this->_aliases['civicrm_event']}.id = {$this->_aliases['civicrm_participant']}.event_id ) AND
                        {$this->_aliases['civicrm_event']}.is_template = 0
             LEFT JOIN civicrm_contact {$this->_aliases['civicrm_contact']}
                    ON ({$this->_aliases['civicrm_participant']}.contact_id  = {$this->_aliases['civicrm_contact']}.id  )
             {$this->_aclFrom}
             LEFT JOIN civicrm_address {$this->_aliases['civicrm_address']}
                    ON {$this->_aliases['civicrm_contact']}.id = {$this->_aliases['civicrm_address']}.contact_id AND
                       {$this->_aliases['civicrm_address']}.is_primary = 1
             LEFT JOIN  civicrm_email {$this->_aliases['civicrm_email']}
                    ON ({$this->_aliases['civicrm_contact']}.id = {$this->_aliases['civicrm_email']}.contact_id AND
                       {$this->_aliases['civicrm_email']}.is_primary = 1)
             LEFT  JOIN civicrm_phone  {$this->_aliases['civicrm_phone']}
                     ON {$this->_aliases['civicrm_contact']}.id = {$this->_aliases['civicrm_phone']}.contact_id AND
                         {$this->_aliases['civicrm_phone']}.is_primary = 1
      ";

After that, I disabled to GROUP BY statement and that gave me the reports I want. I'm fairly confident that this will also work with the CiviMobile pages.

I've used this blogpost to get me started: https://alastaira.wordpress.com/2011/03/17/civicrm-creating-a-custom-report/

Thanks again!

| improve this answer | |
1

Does this have to be the actual event participants view, or just available somewhere? You could create a report (use the "Relationship Report" template under "Contact Reports") which would then show the names side by side.

You could filter it to just the event participants either by creating a smart group, or by setting up the event registration process to automatically put the attendees in a group.

(You might get duplication though, because both members of the couple are participants)

| improve this answer | |
0

You might consider a participant custom field for partner. It would be a contact reference, and you could fill it with the partner's name. The field isn't reciprocal, so you'll need to manually confirm that if Marie's partner is Peter, Peter's partner is Marie. However, you should be able to get this into a stock participant report without much difficulty.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.