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
- id
- parent_id
- name
- icon
- order
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:

