CA Service Management

Expand all | Collapse all

ReportQuery to populate textfields

Jump to Best Answer
  • 1.  ReportQuery to populate textfields

    Posted 12-29-2016 05:06 AM

    Hi, 

     

    I am trying to populate several textfields based on the results of a reportQuery that uses input from a select field.

     

    The form:

    form id = form_1

     

    select id = userids            |         displays all available userids. The user selects one. 

          onChange = ca_fd.js.onChangeUserid();

          Report/Plug-in Variabel = $({'user_ids':ca_fdGetSelectedOptionValues(ca_fd.formId,'userids')[0]})

          Report/Plug-in Id = 9446bcf476c836e9439a91592ae373a1-70e31482916835192

     

    textfield id = email_addr   |         Autopopulate the email address of the selected user.

    textfield 2 id = pnumber    |         Autopopulate the phone number of the selected user.

     

    Data object:

    A data object populates the selectbox with all available userids with the following query: 

     

    id: 9446bcf476c836e9439a91592ae373a1-70e31482916835192

    table: ca_contact

    Query: select userid as id, userid, email_address, pri_phone_number from ca_contact where userid like '%user_ids%%%'

     

    a runtime variable user_ids has been created, containing a string value. 

     

    The script created locally for the form:

    {
    onChangeUserid : function() {
    ca_fd.js.setInfo();
    },
    setInfo : function() {
    var user_ids = {value:ca_fdGetSelectedOptionValues(ca_fd.formId, 'userids')};
    console.log(user_ids.value); // returns an array containing the one userid specified in the selectbox.
    ca_reportQuery('9446bcf476c836e9439a91592ae373a1-70e31482916835192', {userids:user_ids.value}, ca_fd.js.onSuccess, ca_fd.js.onFailure);

    },

    onSuccess : function(result) {
    if (result.length == 1) {
    console.log('success');
    console.log(result[0]); // by setting (result > 1) above, I can see that this line of code prints an array containing all available objects, not just the one selected. 
    ca_fdSetTextFieldValue(ca_fd.formId, 'email_addr', result[0].email_address.value);
    }
    else {
    ca_fdSetTextFieldValue(ca_fd.formId, 'email_addr', 'Cannot find user details');
    console.log(result);

    }
    },
    onFailure : function(){
    ca_fdSetTextFieldValue(ca_fd.formId, 'email_address', 'Failed');
    },
    }

     

    The onSuccess functions runs, as the ReportQuery returns every possible userid. It jumps too the else{, part of the function. 

     

    However, if I set the (result > 1), it will try to set the email_addr field with the email address of the first user the Query returns, but the field in the form gets the value "undefined".

     

    Any help is much appreciated!! 

     

    #ca_reportquery #catalogforms #select #variables #javascript



  • 2.  Re: ReportQuery to populate textfields

    Posted 12-29-2016 06:18 AM

    Btw, I have not added any code for the second textfield yet.

     

    My questions are how to get the reportQuery to return a single object in the Array. And how to set the email address field. 

     

    :-)) 



  • 3.  Re: ReportQuery to populate textfields

    Broadcom Employee
    Posted 12-29-2016 07:46 AM

    Good Afternoon Sondre.
     
    The following is an example and a possible other way, to get this working.
     
    In sc/admin/report builder, create a data object.
    type     = query
    id       = catalog_contact_list
    database = mdb
    table    = ca_contact
    fields   = userid,first_name,last_name
    query    = SELECT userid,first_name,last_name
               FROM ca_contact
               WHERE upper(userid) like  upper('%STRING%%%')
      
    In SC/Catalog/Forms, create a form with:
    A: The Lookup component on the form:
    _id = userid
    onLookup= ca_fdDoFieldLookup('userid','catalog_contact_list')
     
    NOTE: userid is the first field of the data object fields.
          id-value is the id of the data object
     
    B: The 'text field' component on the form is the field to be filled with data from lookup:
    _id = first_name
    _id = last_name
    The value for '_id' must be equal to the field in the data object.
    And will then be automatically populated when the query executes.
    E.G. for another text field on the form, to be populated with the contact's first_name (as in the query-text),
    the _id of that field must/should be first_name too.
     
    Does this answer your question? And help you further on this?
     
    PS.
    When you change the query into:
    query =
    SELECT userid,first_name as firstname,last_name as lastname
    FROM ca_contact
    WHERE upper(userid) like  upper('%STRING%%%')
     
    Then the _id of th fields on the form should be firstname and lastname.
    Equal to the ones that youuse in the 'as' clause in the query-text.
     
    Thanks and kind regards, Louis van Amelsfort.



  • 4.  Re: ReportQuery to populate textfields

    Posted 12-29-2016 08:12 AM

    Thank you for answering Louis, 

     

    Your answer, and the example you provided does indeed work, I tested it, and will probably use it in another case. 

     

    However I would like to make the custome javascript work. I have to get a hold on the custom scripts, variables and data objects, to be able to create what I want later. 

     

    Still hoping for some corrections and comments to the code i provided in my question at the top!!! 

     

    Thanks, Sondre. 



  • 5.  Re: ReportQuery to populate textfields

    Posted 12-29-2016 08:51 AM

    Hi, 

     

    I managed to get the ca_fdSetTextFieldValue to actually set the an email adress returned by the Report Query. 

     

    Changed the follwing: 

    ca_fdSetTextFieldValue(ca_fd.formId, 'email_addr', result[0].email_address.value);

    removed .value from the code. 

     

    However,reportQuery still returns every possible user to the array. So I am still not able to set the right email adresss. 

    Seems like the query runs with a "blank" value, then returning every possible answer.

     

    The value of the var user_ids is in fact "the_username_provided" in the select. 

     

    In the reportQuery the variable is passed with the following code: {userids:user_ids.value}

     

    The error might be in that line... . ??



  • 6.  Re: ReportQuery to populate textfields
    Best Answer

    Posted 12-29-2016 09:48 AM

    Solved, 

     

    The variable defined within the script for the ca_fdGetSelectedOptionValue had the same name as the report runtime variable. 

     

    Creating a new variable with a unique name and passing it to the report query like this: 

     

    ca_reportQuery('9446bcf476c836e9439a91592ae373a1-70e31482916835192', {'user_ids_report_var':user_ids.value}, ca_fd.js.onSuccess, ca_fd.js.onFailure);

     

    : -) 

     

     

    Sondre.