We often receive requests about working with custom fields in Eramba, so we’ve put together this guide. If you use an external reporting tool like Power BI, you may need to work with custom fields stored in Eramba.
All custom fields are stored in the custom_fields table. In this example, we’ll use the Project section, but the same approach applies to other sections.
-
In the Project section, we created eight custom fields.
-
To locate these fields in the database, check the custom_fields table where the model is set to “Projects”
select * from custom_fields where model = 'Projects';
-
To retrieve all custom field values for a specific project item, filter the custom_field_values table by model and the relevant item ID.
select * from custom_field_values where model = 'Projects' and foreign_key = 8;
-
If you need to fetch the value of a specific custom field, apply an additional filter for the custom_field_id. Multi-select fields will naturally contain multiple values.
select * from custom_field_values where model = 'Projects' and foreign_key = 8 and custom_field_id=26;
-
For custom role type fields, the field name follows the format CustomField_{custom_field_id}. If the custom field ID is 32, the corresponding field name will be CustomField_32.
The object_model column defines whether the value refers to a Group or User, while the object_id column stores the corresponding Group/User ID.
To retrieve the value, refer to the user_fields_objects table. The relevant data can be found by filtering this table where the model is set to “Projects,” the field matches CustomField_32, and the foreign key corresponds to the specific project item ID.
select * from user_fields_objects where model = 'Projects' and field = 'CustomField_32' and foreign_key = 8;
