CiviCRM Community Forums (archive)

Hello,

my assumption was that I am able to filter participants by the price set fee options name. Turns out, I'm not, or at least not in our scenario which has old events not using price sets and new ones which do. The price label filter only shows the options from the days when we did not use the price set.

Before I go down and hunt things in the code base, maybe someone could point me where and how the price set option name should be entered so i can filter by it?

Why do we need this? We use the fee levels to distinguish participant tracks as some tracks have their own pricing. Each track though has only a limited capacity and we need to make a role distinction (each participant can have one of two roles in a track, which we solved by adding a custom field) and make sure that the roles are evened out (it is for partner dancing). Hence our need to filter by tracks to see the total amount of participants and the ratio between lead & follow (the two roles).

This used to work perfectly up until the time we figured price sets are the way to go because they actually support I18n.

Well, I think the easiest way to achieve this is to save the civicrm_price_field_value.id in civicrm_participant. Then it is considerably easier to filter by this due to the fact that there is no connection between the price set option and the participant stored anywhere in the system yet.

Someone could argue, we store the label. BUT... the label is the label in the language of the participant at the point of registration. So if you have like 5 different languages, then you have the same price option, but different labels depending on the language.

Yes, I could deal with this the quick way and query the string in all locales of civicrm_price_field_value. Yet... what happens if we have multiple price fields with the same label e.g. in English but not in German. You get stuck. So you need to figure out the associated price set for the event to limit your search to only this price set. Yeah.... no

So I will go down the road and figure out how to set the price_field_id in civicrm_participant. I will post the changes (SQL & PHP) here, so you can make use of it (hopefully)

alter table civicrm_participant add column price_field_value_id integer references civicrm_price_field_value.id
update civicrm_participant p, civicrm_price_field_value pfv set p.price_field_value_id = pfv.id where p.fee_level = pfv.label_en_US
update civicrm_participant p, civicrm_price_field_value pfv set p.price_field_value_id = pfv.id where p.fee_level = pfv.label_de_DE

If anyone knows how to make an update statement out of this which takes care of all possible installed labels, I'd highly appreciate it, then I can provide a proper patch.

Thanks a lot... Is there a documentation about the civicrm tables for developers so I can quickly see what they are used for. I would have never guessed from "line_item" that it would contain this information. But it is great and thanks a bunch for telling me.