CiviCRM Community Forums (archive)

Our needs are actually more complex now than the previous post indicates, and the developers on the IRC channel suggested making a contribution to CiviCRM core, so I'm going for a more general solution.

Upon event creation, the creator will be able to define an arbitrary number of radio-button independent and dependent fields that affect event pricing.  In creating dependent fields, the creator will define dependencies by specifying which, if any, independent fields affect each dependent variable's pricing.  The creator will then specify, in extreme long-hand, every possible pricing attached to each dependent field's possible values, given the selected values for the independent fields it depends on.  The ui for this will be similar to the Discount Sets interface - it would present the creator with a series of tables to fill out based on the variable specifications.  It would be a somewhat lengthy creation process, but the promise of a robust signup process would make it worth the time.

The event creator would also specify a series of "attributes" for each event, for which each signup will be recorded with a boolean value.  They will have names like "Monday lunch," "Monday dorm stay," "Monday private room stay," "Monday workshop A," "Monday dinner."  The creator will specify which attributes will be selected by each possible field combination.  For example, for a combination of "Weekend only" selected for the "Attendence Length" independent field, and "Private dorm" selected for the "Housing" dependent field, the creator will specify that the "Friday private room stay" and "Saturday private room stay" attributes should be selected.  The creator will specify this for every possible combination.

The entire set of selected attributes gets recorded along with the event signup; this is to avoid confusion in case the event creator later edits the default set of selected attributes.

Administrators will later have the opportunity to override almost anything about an individual stored signup - including options, attributes, and pricing.  This will make for an extremely flexible system - for example, if someone signs up for the whole week but we later find out they can't make it as early Monday lunch, an admin can uncheck the Monday lunch box in their signup, so that the chef can get an up-to-date and thorough list of head counts for meals.  It will allow for extremely easy handling of special requests of any kind, pricing, meal or housing options, or otherwise.

I've attached a draft ERD.  I am still familiarizing myself with CiviCRM's architecure, so I welcome suggestions.  I included the id fields from civicrm_event and civicrm_contact as placeholders so I could show the relationships between them and the new tables.  The BLOB values are meant to store serialized PHP arrays containing the complicated n-dimensional pricing information.  This seems to make more sense than creating a table with enough foreign key fields to handle some finite number of dependencies.  For the pricing field in event_adv_field_options, it would be in the form $price['independent field 1']['i.f. 2']['i.f. 3'] = $xx .  For the components field, it would be $defaultComponents['i.f. 1']['i.f. 2']['i.f. 3'] = array([list of component id's]) .  Note also that the tables with signup in their names store information about individual contacts signing up for events.

Thanks for your time.[/list]

Ah.  Thanks for the response.  Here's another piece of the puzzle: We give people the option of paying either a $75 deposit or the full fee upon registration.  In the case of someone paying a deposit only, changing options later wouldn't be an issue.  Still doesn't take care of the full fee people.  And we really do need to record changes that people make to their registration and account for our refunding them or getting more money from them.  Sometimes people show up to an event wanting to switch something and we want to document that in the system, including any refund or additional payment involved.

A question about your first proposal: it seems really nice and elegant; is there a way to address the "attribute" (just realize it's called "component" in the ERD) requirement in that scenario?

I also think it's important to store pricing and attribute information as they appeared at signup with each participant's record, plus have a way to override for each participant without destroying the original information - hence, in the ERD, the total fee and attributes plus override values stored in ...adv_signup, plus the breakdown of the total registration cost, plus overrides, in ...signup_opts.  Your first solution of filtering price set fields would eliminate the need to store serialized arrays since we'd be dealing with single lines instead of n-dimensional arrays.

Maybe a combination of the two approaches would work?  We could use filtered price sets but store the attribute and price/override information in new tables.

Partial payments - interesting.  Added a section to that wiki page about the possibility of more rigid deposit-or-full (nowhere in between) payment choices upon signup.

Attributes:  Definitely needs to be in the CRM - they've specifically requested this so they can do things like have the chef easily pull up the meal counts and have the housing coordinator pull up room needs.  They want to be able to edit individual attributes per registrant at any time, working from a default set based on the line item choices, in order to handle and record special cases without being snagged by the software's limitations.  This sort of flexibility is important to the place, so, to me, it's worth building in attributes as well as all the necessary reporting output.

A question about the Copy/Insert + Cancel operation: If the registration is already paid, what would happen to the record of that payment, and how would it integrate with the Finance and Accounting changes and the deposit-only case?

Summary of best changes thus far: Price sets with filtered fields.  Recording and handling of attributes as described.  Ability to change initial signup options, implemented as Copy/Insert + Cancel, and override the accompanying attributes selected.  I'm not entirely convinced of this last one; does the method seem like a hack that would confuse users by conflating registration changes and real cancellations?

Jake - You might want to do a blog post on CiviCRM.org with a distillation of requirements and your thoughts so far in order to hopefully get input from a larger set of folks. I do think the Cancel / Insert mechanism is a bit of a hack - could be improved a bit by creating another status - "Replaced" or ?? for less confusion - but then code which "counts" and "averages" completed contributions might need to be updated.

If your timeframe is short, integrating w/ UberCart might make sense. There are some integration module(s) around which might help.

Here's the blog post:
http://civicrm.org/blogs/jakecivi/advanced-price-sets-event-attributes-deposit-payment-and-paymentinvoice-tracking

Thanks again.

Okay.  I included some of the introduction from here and wrote a bit more description to introduce the blog post...

Haven't heard much in a few days, so I'm thinking it's okay to start work on at least the filtered fields part since it seems like a pretty solid idea.