So I wanted to allow certain customers to add their own unlimited number of custom fields without having to add new columns to database.
This example is focused on implementing this in add popup form that allows us to enter employee info (first name, last name, etc and also custom fields such as favorite color, favorite singer, etc).
It uses a master/child relation with inline add/edit turned on.
Step 1:
Create a custom fields table to hold the custom field names and which customer created them. So a simple table as follows (calling it "organization_customfields_tbl"):
- CustomFieldID (primary key) - int
- LinkOrganizationID (links to organization ) - int
- Custom_Field_Name (holds the name of the field) - varchar
Step 2:
Create a child table to associate the custom field values with a given employee (calling it "employee_custom_data_tbl"):
- AutoID (primary key) - int
- LinkEmployeeID (this links to master employee) - int
- LinkCustomFieldID (links to the table in step 1) - int
- Custom_Field_Value (hold user input for custom field value that needs to be captured) - varchar
Step 3:
Create a master / child relationship by using ClientID -> LinkClientID (master -> child). Add child table to view, edit, and add forms (select that in phpr relationship options)
Step 4:
In child table list page before SQL Event grab all the custom field names from table in step 1 (organization_customfields_tbl):
Global $conn;
$sql = "SELECT * FROM organization_customfields_tbl WHERE LinkOrganizationID = ". $_SESSION['OrganizationID'];
$rs = db_query($sql, $conn);
$entity_meta = array();
while( $fetch = db_fetch_array($rs)){
$entity_meta[] = $fetch;
}
$pageObject->setProxyValue("entity_meta", $entity_meta);
Step 5:
In the child table "employees_custom_data_tbl" list page javascript event:
window.entity_meta = proxy['entity_meta'];//grabs the data form before sql event
window.entity_counter = 0;//sets our counter to be used in add page JSevent
window.entity_rows = proxy['entity_meta'].length;//set limits for counter conditional in add JSevent page
Also make sure you select the LinkCustomFieldID field a lookup type in PHPR visual editor to grab values from organization_customfields_tbl and set LinkField to CustomFieldID and Display field to Custom_Field_Name. Then choose lookup type as "Edit box with AJAX popup" so we have a plain input field instead of traditional dropdown select.
Step 6:
In the add page for employee_custom_data_tbl JSEvent add the following:
window.entity_counter++; // we increment right away because phpr add new users__tbl popup
if( window.entity_counter < window.entity_rows ){
//console.dir(window.entity_meta[window.entity_counter]);
pageObj.inlineAdd.inlineAdd(); //Trigger adding new records inline
//var entity_item = window.entity_meta[window.entity_counter];
//$('#value_LinkEntityMetaID_' + pageid).val(entity_item.EntityMetaID);
}
//Once we've added all new rows needed (based on number of custom fields we have AND also accounting for fact tha add new page in PHPR automatically triggers inline add on child tables when it first opens), then we populate them with custom field names and values!
if( window.entity_counter == window.entity_rows ){
$(pageObj.controlsMap.gridRows).each(function(index){
var items = this;
console.log(items);
var entity_item = window.entity_meta[index];
$('#saveLink' + items.contextRowId).remove();//get rid of inline save
$('#revertLink' + items.contextRowId).remove();//get rid of inline cancel
$('#value_LinkCustomFieldID_' + items.contextRowId).val( entity_item.CustomFieldID );
$('#display_value_LinkCustomFieldID_' + items.contextRowId).val( entity_item.Custom_Field_Value);
//Lock the newly added inline field and add styling to make it look like the rest of the fields present in the master employee_tbl form (so it does not look like an inline control)
$('#display_value_LinkCustomFieldID_' + items.contextRowId).prop("readonly", true).css({"border":"none", "font-weight":"bold", "background-color":"transparent", "box-shadow":"none","text-align":"right"});
});//end each
};//end if
Step 7:
Now all that is left is for you to decide whether you want to remove the child table column names, headers, and add or cancel buttons. You can do this with jquery and / or with before display event of child list via "$xt->assign('grid_header', false);" etc. I won't detail how to do this here because it's fairly simple to hide or remove elements with jquery.
One quick observation about this whole tutorial is the fact that searching for the custom fields will not be easy. I am working on a few different potential solutions, but I'm quickly realizing that this EAV (entity-attribute-value) design approach has serious limitations and becomes very clear doing search.
I hope this confusing tutorial is of use to someone looking to add unlimited number of dynamic fields to their project.
Cheers,
Marcelo