Extra Fields Schema

The extra fields follow a very similar schema to that for tags. 2 tables are used for this functionality: 'extrafields_fields' and 'extrafields_values'. Each record in extrafields_fields contains the name and type of the extra field and the linked table, the extrafields_values contain the linked entity, e.g. company_id and the actual value of that field applied to a single record. Hence:

This schema is required if you wish to generate reports or dashboards that use the data in extra fields. As an example, to get companies with extra fields and their associated values, you would write a query like

SELECT companies.title, xfields.title, xvalues.field_value 
FROM `companies` 
JOIN extrafields_fields AS xfields ON fk_model = 'Companies'
JOIN extrafields_values AS xvalues ON xfields.id = xvalues.extrafield_id AND xvalues.fk_id = companies.id
WHERE 1;

Note: the fk_model in extrafields_values is technically a duplicate field, and may be removed in the future, it’s there at present for testing purposes - use the fk_model in extrafields_fields for the joins