What is JotForm?
JotForm is a free online form builder which helps you create online forms without writing a single line of code. No sign-up required.

At JotForm, we want to make sure that you’re getting the online form builder help that you need. Our friendly customer support team is available 24/7.

We believe that if one user has a question, there could be more users who may have the same question. This is why many of our support forum threads are public and available to be searched and viewed. If you’d like help immediately, feel free to search for a similar question, or submit your question or concern.


  • Profile Image

    How to use lookup query via database and pre-populate the form fields?

    Asked by GAPTAOnline on March 05, 2014 at 08:20 AM

    I would like to pre-populate fields based on the selection of the name of a local unit.

    If PTA name is = Morrow ES PTA

    Local Unit Number = 1698

    Council = Clayton

    District = 7

    So that when the name is selected three repective values are entered for Local Unit, Council, and District. 

    Page URL:
    http://www.jotform.com/?formID=40568378022961

    how to use and name Selection selected
  • Profile Image
    JotForm Support

    Answered by EltonCris on March 05, 2014 at 09:12 AM

    Hi,

    That's not possible in the form builder. However, you might be able to achieve that when you take your form source code and inject custom script.

    Looking at your form, you seem to have huge lists on your PTA Name field. I assumed this would require HUGE condition scripts so this is pretty complex to achieve. I'm afraid we can't provide you custom workaround for this unless it is applied only for the selected options but I'd be happy to provide you example so you can work on the rest of the scripts. Would you like to go to this route? If yes, let us know here. Thanks!

  • Profile Image

    Answered by GAPTAOnline on March 05, 2014 at 09:41 AM

    What was the purpose of this response on my original thread?

    Answered by jonathan on March 05, 2014 at 08:06 AM

    @GAPTAOnline

    Hi,

    It is possible to use lookup query via database and pre-populate the form.

    It will require outside the form coding using PHP. 

    But to further this different topic, I suggest you create a separate thread for it. Use this link to do this http://www.jotform.com/contact/

    Thanks.

  • Profile Image

    Answered by GAPTAOnline on March 05, 2014 at 09:45 AM

    Regardless of the number of names I am not asking for you all to create a script or code that covers all of the selections? If I remove all with the exception of three names, I still want to populate the three remaining fields for those three. Is this possible if you one selection or not?

  • Profile Image
    JotForm Support

    Answered by TitusN on March 05, 2014 at 11:14 AM

    My colleague has recieved your message and shall respond as soon as he is available.

    Thank you for your patience.

  • Profile Image

    Answered by GAPTAOnline on March 18, 2014 at 12:52 PM

    Has anyone been able to answer this issue or provide a solution. I am approaching a deadline and would like to continue usage of jotform in this capacity.  Please help!

  • Profile Image
    JotForm Support

    Answered by Welvin on March 18, 2014 at 02:48 PM

    Our apologies! Let me ask my colleague about this matter. He may have the example codes but forgot to update you here. 

    Thanks for your patience.

  • Profile Image
    JotForm Support

    Answered by abajan on March 19, 2014 at 12:45 AM

    @GAPTAOnline

    I could be wrong (and I suspect that I am) but my research on populating form fields from a database suggests that this isn't feasible in your case. Here's a short video on the procedure:

    Changing the value in each option element would likely prevent the entries submitted by the user from being stored in the form's own database.

    Nevertheless, I made a script that updates the values in the Local Unit Number, Council and PTA District fields in accordance with what is selected in the PTA Name field. The demo of how it works can be viewed at https://shots.jotform.com/wayne/form_40766648969981.html. Here's the script that's used there. It's far from complete and only works if "Morrow ES PTA", "Morrow H S PTSA" or "Morrow MS PTA" are selected in the PTA Name field. You'll notice some comments in the script. Should you decide to use this workaround, the comments can be used for guidance on how to complete the switch block. Of course, we're always here if you need help with that.

    We'll see if we can devise a better solution but I'm "drawing a blank" at the moment.

  • Profile Image

    Answered by GAPTAOnline on July 03, 2014 at 03:52 PM

    I know this thread was created by the previous admin who was not a developer, and I thought it might aid other people to reply to this as there seems to be a back and forth of yes you can no you can't responses.  I was able to do it very simply and using only a small amount of code.  I stored the related options in a MySQL database and wrote a quick function to pull them out and add them to a JavaScript object.  Then I populated the drop down with these options using jquery and attached data attributes with the related information.  A simple function was then used for the onchange event to read the data attributes and populate text fields that were set to read only.

    I also added a function to check that the read only fields had a value before allowing the user to continue on to the next part of the form, and I also added a check to see if the form was auto filling the fields from a saved form and doing a look up then to auto fill the read only fields as well.  This actually required a minor modification to the JotForm JavaScript file, but was pretty easy itself.  Just two if statements (4 lines of code total) for the auto fill, and a few more to allow for the database conditional check to prevent the next button from progressing and instead display a message to the user to select an option from the drop down.

    If anyone needs the modifications, I can explain them, but there is one catch.  It does require the form to be hosted on another server as it uses php, and it does require that you keep on top of changes to the JotForm code as some changes they make can break your form and would require you to download and reintegrate your custom code to get it working again.

  • Profile Image
    JotForm Support

    Answered by david on July 03, 2014 at 04:42 PM

    Hi,

    We really appreciate your update.  Anytime one of our users finds creative workarounds for things we are very grateful.  I would ask you to post your code snippets but it seems as though a lot of it would be form specific.  Thank you for posting the method, some of our more advanced users may find this very helpful.

  • Profile Image

    Answered by GAPTAOnline on July 03, 2014 at 06:14 PM

    Changes to jotform.js to prevent continuing to next page or submitting when needing special conditions that can't easily be handled by other means such as database information checking, etc.:

    Add this just after var JotForm = {

    /**
    * JotForm preventNext
    * Added to allow additional conditions to prevent form from progressing to the next step if the page has unfinished setup.
    * Set to true to prevent Next button from working.  Be sure to display an appropriate message to the user.
    * Also, be sure to set back to false when conditions are met to allow user to proceed to next step on the form.
    */
    preventNext: [],

    In the handlePages function definition add this after the line:
             section.select('.form-pagebreak-next').invoke('observe', 'click', function(){ // When next button is clicked

                 //alert("Current Page: "+JotForm.currentSection.pagesIndex); Debug used to find current section page.
                if(JotForm.preventNext.length && JotForm.preventNext[JotForm.currentSection.pagesIndex]){return;}

    In the validator function add this after the line:
                       JotForm.runAllCalculations(true);

               if(JotForm.preventNext.length && JotForm.preventNext[JotForm.currentSection.pagesIndex]) {
                             JotForm.enableButtons();
                            e.stop();
                return;
                }

    To Use add an item to the array for the page you are placing a check on (to get page number, uncomment the alert line):

    JotForm.preventNext[2] = true;

    When the condition is met, just set the array item to false:

    JotForm.preventNext[2] = false;

    Be sure to add an onclick event handler to the next button to perform the checks and either display a message to the user telling them how to fix it, or removing the condition.

  • Profile Image

    Answered by GAPTAOnline on July 03, 2014 at 06:28 PM

    It is also helpful to disable the button until the click handler is enabled.  To do this, add disabled="disabled" to the next button you are adding a special condition for.  Then enable it after you have set up an click event handler. For example:

    $j("#form-pagebreak-next_61").click(function (event) {
    if ($j("#input_80 option:selected").prop("id") == "selectOption") {
    alert('Please select an option above to continue.');
    }
    });//Add event handler to make sure option is selected.
    $j("#form-pagebreak-next_61").prop("disabled", false);//Make button active only after click handler is set.

    The $j is my jQuery noconflict object as I use a newer version than is included.

    Be sure to change the pagebreak-next_61 to match the one you are using.  Also, the selectOption id property I am checking is an id I added to the default option for the dropdown input_80 (which needs to match your dropdown also) to allow me to check that they have in fact selected an option.  This can be skipped if you choose one by default and don't have a "Choose an option" option element.

  • Profile Image

    Answered by GAPTAOnline on July 03, 2014 at 06:44 PM

    To handle the situation where JotForm is using auto saved information for your drop down, you can add this code to jotform.js:

    In the handleSavedForm function add this after the line:
           if(!('session' in document.get)){

    if (typeof afterJotFormLoadInit === "function") {//ADDED THIS TO HANDLE REENABLING BUTTONS IF PASSES CHECKS AFTER SAVED LOAD
    afterJotFormLoadInit();//function to handle initialization after autofill is done.
    }

    And add this after the line:
                           JotForm.openInitially = res.currentPage - 1;

    if (typeof afterJotFormLoadInit === "function") {//ADDED THIS TO HANDLE REENABLING BUTTONS IF PASSES CHECKS AFTER SAVED LOAD
    afterJotFormLoadInit();//function to handle initialization after autofill is done.
    }

     

    Then simply define your function to run after the form has received the auto saved information for example:

    function afterJotFormLoadInit(){
    if ($j("#input_80 option:selected").prop("id") == "selectOption") {
    JotForm.preventNext[2] = true;
    }
    else {
    JotForm.preventNext[2] = false;
    hasRemovedSelectOption = 1;
    $j("#selectOption").remove();
    }
    }

    The hasRemovedSelectOption variable is used to let the program know that the "Please Choose Option" option has been removed already preventing the user from selecting that option again and providing bad data.  It can be left out if selecting one by default and not using the please choose option message.  To see how I used it, here is some more code:

    var hasRemovedSelectOption = 0;
    function getOptionInfo() {
    if (!hasRemovedSelectOption) {
    hasRemovedSelectOption = 1;
    $j("#selectOption").remove();
    JotForm.preventNext[2] = false;
    }

    var oElem = $j("#input_80 option:selected");
    $j("#input_5").prop("value", oElem.data("specialDataOne"));
    $j("#input_82").prop("value", oElem.data("specialDataTwo"));
    $j("#input_81").prop("value", oElem.data("specialDataThree"));
    }

    The getOptionInfo function is set as an onchange handler for the drop down.  When the user changes options, this code first checks to see if the "Please Choose Option" option element is still in the drop down and if so, then it removes it and turns off the condition check because for my use the condition was to auto populate some fields based on the option chosen.  Then the function does just that.  It gets the selected option and reads the special data I stored earlier on the option tags.  To see that code, check my next answer.

  • Profile Image

    Answered by GAPTAOnline on July 03, 2014 at 07:21 PM

    This code is php and javascript as noted:

    <?PHP
    //Set up database connection.

    /*Example dsn strings:
    mysql:host=localhost;dbname=testdb
    mysql:host=localhost;port=3307;dbname=testdb
    mysql:unix_socket=/tmp/mysql.sock;dbname=testdb
    */
    $dsn = 'yourdsnstring';
    $dbuser = 'yourdbuser';
    $dbpass = 'yourdbpass';
    $app["dbopts"] = array(
    PDO::MYSQL_ATTR_INIT_COMMAND => 'SET NAMES utf8',
    );

    if (!$dbh = new PDO($dsn, $dbuser, $dbpass, $dbopts))
    {
    //We failed to connect to the database.
    echo "Failed to connect to the database.";
    }
    ?>

    And now to pull the info from the database:

              <select class="form-dropdown validate[required]" style="width:300px" id="input_80" name="q80_optionName" onChange="getOptionInfo();">
               <option id="selectOption" value="0" selected="selected">Please select your option</option>

    <?PHP

    try
    {
    $optionSELECT = $databaseHandle->prepare("SELECT
    A.specialDataOne,
    A.specialDataTwo,
    A.specialDataThree,
    B.optionValue,
    B.optionLabel,
    B.optionID

    FROM
    someTable A
    LEFT JOIN anotherTable B
    ON A.relatedField = B.relatedField
    WHERE
    filterColumn = filterValue
    ORDER BY
    orderColumn;");
    $optionSELECT->execute();

    $optionSELECT->bindColumn("specialDataOne", $sD1);
    $optionSELECT->bindColumn("specialDataTwo", $sD2);
    $optionSELECT->bindColumn("specialDataThree", $sD3);
    $optionSELECT->bindColumn("optionValue", $oVal);
    $optionSELECT->bindColumn("optionName", $oName);
    $optionSELECT->bindColumn("optionID", $oID);

    $options = Array();

    while ($row = $optionSELECT->fetch(PDO::FETCH_BOUND))
    {
    $options[$oID] = Array("specialDataOne" => addcslashes($sD1,"'"),
    "specialDataTwo" => addcslashes($sD2,"'"), "specialDataThree" => addcslashes($sD3,"'"), "optionValue" => addcslashes($oVal,"'"), "optionName" => addcslashes($oName,"'"));
    echo "<option id=\"option$oID\" value=\"$oVal\">$oName</option>\n";
    }
    }
    catch (PDOException $e)
    {
    ?>
    <script language="JavaScript">
    alert("We're sorry, but this form has encountered an error. Please try again later.\n\n<?PHP echo htmlentities($e->getMessage()); ?>");
    </script>
    <?PHP
    }
    ?>
    </select>

    I use the PHP PDO library to handle my database interaction.  Please see the PHP documentation for how to use this library. Be sure to change the query to match your needs.

    The code for adding the data to the options is set in the jQuery ready function.  You could do this above and do away with the PHP array, but as the data attribute isn't supported the same in all browsers (mostly just older ones) it is better to let jQuery abstract that away.  At least I believe this is the case.  Anyway, place this code in a javascript tag:

    $j(document).ready(function(){
    <?PHP
    foreach($options as $oID => $oData) {
    echo "\$j('#option$oID').data('specialDataOne', '{$oData['specialDataOne']}');\n";
    echo "\$j('#option$oID').data('specialDataTwo', '{$oData['specialDataTwo']}');\n";
    echo "\$j('#option$oID').data('specialDataThree', '{$oData['specialDataThree']}');\n";
    }
    ?>
    $j("#form-pagebreak-next_61").click(function (event) {
    if ($j("#input_80 option:selected").prop("id") == "selectOption") {
    alert('Please select your option above to continue.');
    }
    });//Add event handler to make sure option is selected.
    $j("#form-pagebreak-next_61").prop("disabled", false);//Make button active only after click handler is set.
    });

     

    And that is all the code you should need.

  • Profile Image
    JotForm Support

    Answered by david on July 03, 2014 at 07:26 PM

    That is some impressive stuff.  I will pass this along to our developers to have a look at also.  Thank you very much for the time you spent writing this up.  Hopefully it will help others looking for the same functionality.

    Thank you for using Jotform!