Working with Dynamic Drop down fields
Often there is a business need to have a form based workflow with a dynamic dropdown list be this a list of customers, or cost center codes. The desire is to allow the business users to be able to update this list without any intervention from the Content Server Administrators. This can be done leveraging form templates, LiveReports, and WebReports.
The creation of SQL table is an important step and thought should be put into the columns that you’ll need before it is created. The initial column will be leveraged as your first or second dropdown depending on the total number of options, and each subsequent column will be used for the following dropdowns. Ensure that you talk to the business users and confirm that they will not need further dropdowns down the road, as if so this should be accounted for now. Once you have the form table created, you can manually populate it with information. If there are many many rows, you can leverage a CSV to import the data with Web Reports.
For each dropdown on your form you will need one WebReport, and one LiveReport to power it. The initial LiveReport is rather basic and is used to simply return a DISTINCT list of our first column. In this example we’ll call it ‘Company’.
select DISTINCT Company from WFSample
This should return a unique list of all of the items in our SQL table. Ensure that the LiveReport is configured to be an ‘Auto LiveReport’ and has the record limit set appropriately depending on the number of items you plan on returning. Next we will have to build a WebReport to handle the integration with our HTML Form. Create a new WebReport that is a ‘Blank Report’ and set the source to be the LiveReport we just created. The contents of the WebReport will be the following.
[LL_WEBREPORT_EXCLUDEHTML /]
[[LL_WEBREPORT_STARTROW /][LL_WEBREPORT_IF "[LL_REPTAG_ROWNUM /]" != "[LL_REPTAG_TOTALROWS /]" /]{"COMPANY":"[LL_REPTAG_1 /]"}, [LL_WEBREPORT_ELSE /]{"COMPANY":"[LL_REPTAG_1 /]"}[LL_WEBREPORT_ENDIF /][LL_WEBREPORT_ENDROW /]]
To break this down and explain it a bit. The first tag “ExcludeHTML” is used to exclude all the HTML, JavaScript and Style definitions that Content Server uses to wrap any given page. This is needed as we’ll be consuming this as a JSON response and we don’t need any of the visual elements added to the page.
Next we start the row section where we are checking to see if the current row, is the last row. This is important so that we’re able to build valid JSON objects. We create a JSON object that follows this pattern.
[{"COMPANY":"OpenText"},{"COMPANY":"Google"}]
Once you have saved the WebReport you will need to set the output destination to be Browser, and the mimetype to be “application/json”. Save the WebReport and click on it to give it a test. You should get returned a JSON object with all of the options for your first dropdown listed.
Now that we have our first WebReport created, we can work on connecting our HTML Form to the WebReport dynamically. For each dropdown attribute that we want to leverage we’ll need a JavaScript function. This JavaScript will perform an AJAX call to our WebReport and parse our JSON object and add those options into our empty drop down.
<!-- Build AJAX call to get options-->
<script>
function setCompany(selectFeild,reloadVal)
{
selectFeild.html('');
myURL = "/otcs/llisapi.dll?func=ll&objId=111111111&objAction=RunReport&nexturl=%2Fotcs%2Fllisapi%2Edll%3Ffunc%3Dll%26objid%3D11111111%26objAction%3Dbrowse%26sort%3Dname"
return $.ajax({
url: myURL,
dataType: 'json',
success: function (data) {
for(i=0; i < data.length; i++)
{
selectFeild.append("<option value=\""+data[i].COMPANY+"\">" + data[i].COMPANY + "</option>");
}
selectFeild.val(reloadVal)
selectFeild.prop("disabled", false);
},
error: function (data){
}
})
}
</script>
This function takes in two parameters, first the value is the ID of the dropdown list that we want to populate, and the second is the reload value. This is leveraged for when the page is reloaded. We will call the script in the document.ready function We’ll have to update the object ID’s to our WebReport we created:
setCompany($("#_1_1_2_1"),"[LL_FormTag_1_1_2_1 /]")
Now when we open our page, once it’s ready to be loaded it will fetch the JSON object which is driven by the database.
If we have a scenario where we need to build out more dropdowns based the previously selected dropdown, we can leverage a very similar solution. We will however need to create another LiveReport, WebReport, and JavaScript function with a few modifications. In our scenario lets imagine that our next field is “EmployeeName”. We’ll want to create a new LiveReport with the following contents.
select DISTINCT EmployeeName from WFSample where CompanyName = %1
This should return a DISTINCT list of Employee Names which correspond to a specific Company name passed in as an input parameter. Again ensure the LiveReport is configured as an Auto LiveReport and that the input parameter is configured as the user input. A good idea is to test your LiveReport manually. You should be able to simply click on your LiveReport and paste in a value from the first column. Ensure you are getting the results you are expecting for your second dropdown.
Our WebReport will be effectively the same, however you may want to update the key value of your WebReport to reflect what is being return.
[LL_WEBREPORT_EXCLUDEHTML /]
[[LL_WEBREPORT_STARTROW /][LL_WEBREPORT_IF "[LL_REPTAG_ROWNUM /]" != "[LL_REPTAG_TOTALROWS /]" /]{"EMPLOYEENAME":"[LL_REPTAG_1 /]"},
[LL_WEBREPORT_ELSE /]{"EMPLOYEENAME":"[LL_REPTAG_1 /]"}[LL_WEBREPORT_ENDIF /][LL_WEBREPORT_ENDROW /]]
With this implemented we can now create our second JavaScript function. This JavaScript function will have to incorporate the value from the first dropdown, and pass that back to the WebReport to return the correct dataset. This is passed in as param1, and is joined to the URL at the end. This will be passed to the WebReport/LiveReport combo to allow our SQL query to return as expected.
<!-- Get EmployeeName -->
<script>
function setEmployeeName(selectFeild,param1,reloadVal)
{
//reset the select option.
selectFeild.html('');
param1 = "&companyname=" + encodeURIComponent(param1);
myURL = "/otcs/llisapi.dll?func=ll&objId=2222&objAction=RunReport&nexturl=%2Fotcs%2Fllisapi%2Edll%3Ffunc%3Dll%26objid%3D122222222%26objAction%3Dbrowse%26sort%3Dname"
myURL = myURL + param1;
return $.ajax({
url: myURL,
dataType: 'json',
success: function (data) {
for(i=0; i < data.length; i++)
{
selectFeild.append("<option value=\""+data[i].EMPLOYEENAME+"\">" + data[i].EMPLOYEENAME + "</option>");
}
selectFeild.val(reloadVal)
selectFeild.prop("disabled", false);
},
error: function (data){
}
})
}
</script>
Now when we need to call this script we will need to expand the parameters to include the value of the previous dropdown box. This is passed in the second parameter.
setEmployeeName($("#_1_1_3_1"),$("#_1_1_2_1").val(),"[LL_FormTag_1_1_3_1 /]")
To make this more dynamic, we will need to check for changes to the first dropdown and fire a function to pull from the WebReport again. This is required as if the end user selects one value, then another value, we need to ensure that the list is updated and only showing valid information for their previous selections.
$("#_1_1_2_1").on('keyup paste click',updateText);
function updateText() {
if (($("#_1_1_25_1").val() !== null ) )
{}
else
{
setEmployeeName($("#_1_1_3_1"),$("#_1_1_2_1").val(),"[LL_FormTag_1_1_3_1 /]")
}
}
From here extending past two dropdowns is simply repeating the process, creating new LiveReports, WebReports, and JavaScript functions to populate dropdown information as required.
When implementing these calls there can be scenarios where the call to the WebReport takes a few minutes. This could be due to the size of the table (We have some WF’s leveraging more than 1,000 rows of dropdown data), or the complexity of the query. In these scenarios some user feedback that something is happening can be very useful. At the very bottom of your form you can implement the following to show a loading wheel when the request is pending.
<div class="modal"><!-- Place at bottom of page --></div>
<style>
.modal {
display: none;
position: fixed;
z-index: 1000;
top: 0;
left: 0;
height: 100%;
width: 100%;
background: rgba( 255, 255, 255, .8 )
url('https://jhewitt.ca/images/loading.gif')
50% 50%
no-repeat;
}
/* When the body has the loading class, we turn
the scrollbar off with overflow:hidden */
body.loading .modal {
overflow: hidden;
}
/* Anytime the body has the loading class, our
modal element will be visible */
body.loading .modal {
display: block;
}
</style>