Ok. This is one place where you'll need to be a little creative then. It may not be a good idea to use a valuelist at all for this since a valuelist is loaded on each page request where the valuelist is used (all values in the valuelist). If there are tens of thousands of values then this could be problematic for performance.
The best strategy (I would say) is to create a calculated field for the value in your table (for details, list, find, etc..), and then make a custom widget for the edit form to edit the id.
E.g.
In the fields.ini file:
- Code: Select all
__sql__ = "select t1.*, c.name as category_name from mytable t1 left join categories c on t1.category_id = c.id"
Then hide the category_id field form the list and details view:
- Code: Select all
[category_id]
visibility:list=hidden
visibility:browse=hidden
This will effectively replace the category_id field in the list and details view with your category_name field to show the name of the category rather than the id.
The hard part is now allowing the user to edit the category id. This is best done with javascript. You can use JSON to do this quite easily as Xataface provides an action named export_json which exports the found set as in JSON format.
E.g. if the categories table has fields (id,name, projectID) we could obtain a JSON array of all categories with projectID=10 by entering the URL:
index.php?-table=categories&-action=export_json&projectID=10
You can then use AJAX to extend your javascript strategy to update your category_id select list to only load the category ids for the current project.
e.g.
- Code: Select all
function block__category_id_widget(){
echo <<<END
<script language="javascript"><!--
require(DATAFACE_URL+'/js/ajaxgold.js');
// Import ajax gold library for ajax functions
function projectChanged(select){
var selectedProjectID = select.options[select.selectedIndex].value;
var url = DATAFACE_SITE_HREF+'?-table=categories&-action=export_json&projectID='+selectedProjectID;
getDataReturnText(url, function(text){
var categoryIDSelect = document.getElementById('category_id');
categoryIDSelect.options.length = 0;
// clear the select list.
eval('var options='+text+';'));
// Get all the categories from the JSON return
for ( var i=0; i<options.length; i++){
categoryIDSelect.options[categoryIDSelect.options.length] = new Option(options[i].name, options[i].id);
}
});
}
var projectIDWidget = document.getElementById('project_id');
var projectID.onchange=projectChanged;
//--></script>
<select name="category_id" id="category_id"></select>
END;
}
Or something along these lines. This example needs refining to show the correct currently selected category id and possibly some other things (it may also contain errors as I haven't tested it --- but I have done the same thing before so the strategy works.
-Steve