Hi,
I created a Form Application that has a Select Field. The select field gets the values from
another table. What I am trying to do is add a custom value to the select values.
For example:
Categories
Test 1, Test 2, Test 3
The Category_ID select field displays Test 1, Test 2, Test 3 as its options. What
I am trying to do is manipulate the Select field so it has the options None, Test 1, Test 2, Test 3.
How can I do that?
Set checkbox ‘use title’ in the lookup specs and set the title / internal value to None
Ohh lol, thank you so much. Too many cool ways to customize, probably overlooked 
I have same situation and solved, thanks aducom for provide such good advice,
anyone can show how to post new message
aducom you have been awesome. By the way I was wondering… is it possible to dynamically manipulate those select values. For example, I have a table that has records in a parent-child relationship. So, I am trying to make the options generate like below in one of the forms:
Cat 1
Cat 2
---- Cat 2 Child 1
---- Chat 2 Child 2
Is it possible?
Hi. It seems to me that anything you can generate from a SQL select from your mysql DB, should work in the automatic setting. The select it builds for you at first is very very basic (e.g. looking in a single table). However, you can do all sorts of SQL logic and other conditional code, as well as joins and such, andf even generate aliases - so I believe if I were you, I would get some really good SQL examples from google and play with SQL in phpmyadmin or whatever you like to use. Build a select until you get the results that minimic what you want, then paste and modify (with global variables) back into the select lookup code.
I have done some simpler things than you want, but am 97.3% sure you can get it to work 
Good luck.
Jamie
PS If you get something to work, youy might want to post it back as a reply for future users. It is a good complex question.
You cannot create a select field over two tables. The useual approach would be to create two dropdowns and let the second dropdown depend on the first. you can do that by setting the ajax properties. As soon as you change the first field, the second is filled with the child. Unnecessary to say that you need the ‘null’ approach too. IN the ajax event you select the fields which needs to be refreshend (needs to be of type select). It’s in the field properties somewhere near the select properties.
onmountain I looked into MySQL queries and it seems like they don’t have what I am looking for. And also the procedures are not that useful either. If someone comes up with a procedure that does what I am after, that will be great. But in the meantime here is my solution 
Here is the structure of my table
Folders
The following code goes under:
Form Settings / Events / onLoad
// Get the Records
function getTree($parent_id=0){
sc_lookup(records, "SELECT id, parent_id, name, icon, `order` FROM folders WHERE parent_id=$parent_id ORDER BY `order`");
$rows = {records};
if(!(bool)$rows){
return false;
} else {
foreach($rows as $row){
$id = $row[0];
$parent_id = $row[1];
$name = $row[2];
$icon = $row[3];
$order = $row[4];
$results[$id] = array(
"id" => $id,
"parent_id" => $parent_id,
"name" => $name,
"icon" => $icon,
"order" => $order,
"children" => getTree($id)
);
}
return $results;
}
}
function convertTreeToArray($tree, $level){
foreach($tree as $row){
$prefix = "";
for($i=0;$i<$level;$i++){
$prefix .= "--";
}
$row['name'] = $prefix . " " . $row['name'];
$_SESSION['records'][] = array(
"id" => $row['id'],
"parent_id" => $row['parent_id'],
"name" => $row['name'],
"icon" => $row['icon'],
"order" => $row['order']
);
if(isset($row['children']) && (bool)$row['children']){
convertTreeToArray($row['children'],$level+1);
}
}
}
$tree = getTree(0);
$_SESSION['records'] = array();
convertTreeToArray($tree,0);
$json = json_encode($_SESSION['records']);
echo '
<script type="text/javascript" language="javascript">
var tree_json = \''.$json.'\';
var tree = jQuery.parseJSON(tree_json);
</script>';
And the following code goes under:
Form Settings / JavaScript / General [Form] / onLoad
And make sure you replace the field_name with the actual field name (e.g. folder_id)
var select_field_id = "id_sc_field_field_name";
// Get the current selected value
var current_value = $("#" + select_field_id + " option:selected").val();
// Reset the Select
$("#" + select_field_id).html('');
// Insert Each record
$.each(tree, function(node_index, node) {
$("#" + select_field_id).append($("<option></option>").attr("value",node.id).text(node.name));
});
// Set the old value
$("#" + select_field_id).val(current_value);
Here is what you end up with:
That’s a great advanced solution using jquery and javascript. For people who this sample is too complex I still would recommend using two select fields. Requires no coding and achieves the same. But I like solutions like the above as it shows you the flexibility of SC.
Hi Aducom
I have one related question on this. I have two dropdowns. For e.g. Country and State. Country drop down is coming from Country Table and State Dropdown is coming from Statetable which has a countryid as foreignkey to Country Table.
I have created a from application and made these two fields as Select type
Also sent the Ajax property of country dropdown and selected the state filed to load whenever country dropdown is changed.
Also included the where clause in the state dropdown and used country id to filter.
Now the question is during startup the country dropdown loads the data but State dropdown does not load anyvalue until you select someother country and then select the country you want.
How can I also make state to load when the form is loaded? Hope my question is clear. Any help would be highly appreciated.
Regards
Narayanan
I got same issue. Any solution for this?. There is no value on field but value is correct in database