Author Topic: Schema Relationships (Read 1034 times)

raashell · February 22, 2010, 10:54:00 am

I'm working on a Joomla component that I need to have programmatically add custom fields to profiles associated with specific registration and contribution forms. In the database, I can see were the entries need to be added to the civicrm_option_value table. I can also see how civicrm_uf_join appears to connect a profile with the events and contribution tables. What I cannot see is how those profiles are linked to civicrm_custom_field, civicrm_custom_group, civicrm_option_group, and finally civicrm_option_value. Can someone point out the missing link? (And hopefully it's not between my ears.

) Thanks, John

lcdweb · February 22, 2010, 11:14:49 am

The join between a profile and the fields is done with: civicrm_uf_field

Depending on how significant the programmatic options are, you may want to build your profile to include all potential fields and then use hooks or javascript in the .tpl file to control what's visible/not visible under different conditions. That's what I typically do if I need to have conditional fields and stuff.

xavier · February 22, 2010, 12:14:57 pm

Hi,

I'd suggest using the api civicrm_contact_get (or _search) to fetch the custom fields (return.custom_xxx in the param).

You can do it either from php or from smarty {crmAPI}

http://wiki.civicrm.org/confluence/display/CRMDOC/CiviCRM+Public+APIs

raashell · February 22, 2010, 12:33:17 pm

I'm still having a problem seeing how the uf tables are connected to the option_group and option_value tables. The keys I'm seeing don't appear to correspond directly between any uf tables and option_group or custom_group tables. However, I did notice for the first time, that the field_name in the uf_field table appeared to be a composite word id combo... e.g. custom_3 that *did* correspond to ids in the custom_group table. Is this the relationship? Is it the only connection? Am I going to have to extract ids from the strings in that column in order to connect the two via sql queries?

The API might be the way to go, though I'm hoping to avoid it, because I *think* I should be able to get exactly what I need off of one sql query.

Basically, all I'm trying to do is use an event id, or a contribution id to determine the option_group_id associated with it in the civicrm_option_value table and insert a new row in that table. My desired result is that I can programmatically plug new options into a select list appearing in a profile on the online registration and donation pages.

Thanks for your input, I'm quite new to Civi's architecture.

lcdweb · February 22, 2010, 02:51:28 pm

you have custom_group records, and in those group records you have the individual fields -- custom_fields. Some of those custom fields may have an option list associated with them, and so the fields connect to option_group/option_value.

The profile tables (uf_group > uf_field) pull fields and indicate they belong to a profile -- a frontend facing form. Profiles can pull from both core and custom fields. So the profile definitions don't determine what option list is available for a field -- that's defined by the field itself. The profile tables just say "this field" belongs to "this profile".

so working backward, you'd use the profile tables to determine what fields are in your profile, and then look at the custom field definition to find out what option list is associated with it.

there's an ERD online: http://wiki.civicrm.org/confluence/display/CRMDOC22/CiviCRM+ERD+2.0
It's a little old, but I don't think the structure of these elements has changed much since 2.0.

but Xavier is right -- you might want to use the API instead of figuring out the joins.

CiviCRM Community Forums (archive)

News:

Author Topic: Schema Relationships (Read 1034 times)

raashell

Schema Relationships

lcdweb

Re: Schema Relationships

xavier

Re: Schema Relationships

raashell

Re: Schema Relationships

lcdweb

Re: Schema Relationships