How to pass a field value into a query

William C

March 18, 2022 13:56

Here is my setup:

1. SQL Query A - Pulls a list of branches

2. SQL Query B - Pulls a list of employees, at the branch selected in Query A.

3. Dropdown control A - options source = web service, pointing at Query A

4. Dropdown control B - populated by JS*

 

In the past I've used a rule similar to Example #2 on this page - DB Connector Tutorial - frevvo v10.0 Documentation - frevvo Documentation, but the piece I'm missing is how to pass in my value from dropdown A.

This is how the WHERE clause is set up in my query:

WHERE branch_no = '{ddlBranchDept}'

0

• Official comment

  

  Megan Ellis

  March 18, 2022 17:30

  Hi William,

  In the http.get URL in your business rule to populate Dropdown B, you'll need to pass a value to the variable ddlBranchDept. So your URL will looks something like "https://<server>:<port>/database/queryset/query?ddlBranchDept=" + DropdownA.value 

  Let me know if that makes sense! The business rule in this example shows this syntax.

  if (sc.value.length > 0) {
    eval ('x=' + http.get('http://localhost:8082/database/BIRT/ordersByCustomer?cnum=' + sc.value));

   
• 

  William C

  March 18, 2022 19:10

  Hi Megan,

  I'm getting this error when my rule tries to run:

  ** Error: cannot find dependency named [resultSet] in rule [New Submission.SQL Query]

  Here is my rule code:

  /*member resultSet*/
  var event = form.load;
  var x;

  if (Boolean(ddlBranchDept.value)) 
    {
    eval ('x=' + http.get('http://localhost:8081/database/queryset/query?ddlBranchDept=' + ddlBranchDept.value));
    var opts= [];
    for (var i=0; i < x.resultSet.length; i++) {
      if (x.resultSet[i]) {
        opts[i] = x.resultSet[i].value + '=' + x.resultSet[i].label;
      }
    }
    ddlCostCenter.options = opts;
  }

  This doesn't have anything to do with the data being returned in JSON vs XML format, does it?

  0
• 

  Megan Ellis

  March 18, 2022 21:01

  Hi William,

  Are the database columns you're pulling in the resultSet actually called 'value' and 'label'? If not you'll want to make sure you're referencing the actual column name. In this example the db column names are customerNumber and customerName:

   if (x.resultSet[i]) {       

              opts[i] = x.resultSet[i].customerNumber + '=' + x.resultSet[i].customerName;    

          }  

   

  If 'value' and 'label' are the db column names, these are reserved words and frevvo won't be able to parse them from the json as is. The workaround is to encase the word in square brackets. See this documentation, which is for Google Connector but the same thing would apply here.

   

   

  0

Please sign in to leave a comment.