No announcement yet.

Manipulate Select Lookup Values?

  • Filter
  • Time
  • Show
Clear All
new posts

  • Manipulate Select Lookup Values?


    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:

    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?

  • #2
    Set checkbox 'use title' in the lookup specs and set the title / internal value to None
    Albert Drent
    aducom software netherlands
    scriptcase partner, reseller, support and (turn-key) development /


    • #3
      Originally posted by aducom View Post
      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 :P


      • #4
        I have same situation and solved, thanks aducom for provide such good advice,
        anyone can show how to post new message


        • #5
          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?


          • #6
            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.
            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.


            • #7
              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.
              Albert Drent
              aducom software netherlands
              scriptcase partner, reseller, support and (turn-key) development


              • #8
                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

                - id
                - parent_id
                - name
                - icon
                - order

                The following code goes under:

                Form Settings / Events / onLoad

                PHP Code:
                // 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};
                    } else {
                $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)

                $tree as $row){
                $prefix "";
                $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']
                $row['children']) && (bool)$row['children']){

                $tree getTree(0);
                $_SESSION['records'] = array();

                $json json_encode($_SESSION['records']);

                    <script type="text/javascript" language="javascript">
                    var tree_json = \''
                    var tree = jQuery.parseJSON(tree_json);

                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)

                HTML Code:
                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",; 
                // Set the old value
                $("#" + select_field_id).val(current_value);
                Here is what you end up with:

                Screen Shot 2013-07-16 at 2.21.11 PM.jpg
                Attached Files
                Last edited by roosevelt; 07-17-2013, 12:14 AM.


                • #9
                  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.
                  Last edited by aducom; 07-16-2013, 05:48 AM.
                  Albert Drent
                  aducom software netherlands
                  scriptcase partner, reseller, support and (turn-key) development


                  • #10
                    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.



                    • #11
                      I got same issue. Any solution for this?. There is no value on field but value is correct in database