CA Service Management

Hi Community,

I want to load user data (requester_user) when user create a new ticket in catalog.

I already have a data object with the query:

SELECT c.userid, CONVERT(varchar(32), l.location_uuid, 2) AS location_id,
l.location_name AS loc_name
FROM ca_location l INNER JOIN ca_contact c on l.location_uuid=c.location_uuid
WHERE upper(userid) like upper('%STRING%%%')

And a lookup field in my form that fetch this query. In lookup field value property I got $(_.user.id) but when user create the ticket the lookup field still want to search the userid to fetch data from query.

Any form to fetch data from the query when form load?

Regards,

Pedro

Good Morning Pedro.
 
The following is an example 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 you use in the 'as' clause in the query-text.
 
Thanks and kind regards, Louis van Amelsfort.

Hi LOUIS,

Thanks for your time. I already try this, but in your example the lookup field need to be choosen by the user and select a userid from the query, then the text fields are populated. I need that the text fields already have data from the query when user open the form without promt the user by a userid. The userid is the user creating the ticket.

I try using the variable $(_.user.id) as value for the lookup filed with the query, but the query dont take this value automatically.

Regards,

Pedro

First of all, a lookup field is probably not the correct control in your use case.

What do you want to achieve? Display the location of the connected user?

If yes, I would use ca_reportQuery in a function called onLoad of your form, then use the return to populate a textbox.

Louis_van_Amelsfort answers will help you a lot.

Finally, to answer your question,  in Report /plug-in variables of a control that support it, set : $({'STRING':_.user.id}). (This is not feasible in a lookup btw, so this is why i said that you do not use the correct control)

To complete the answer i will use Louis_van_Amelsfort code snippet and modify it a lil'bit to suit your needs :

{
onLoad : function() {
 ca_fd.js.setInfo();
 },
setInfo : function() { 
 ca_reportQuery('yourReportObjectID', {'STRING':_.user.id}, ca_fd.js.onSuccess, ca_fd.js.onFailure); 
},
 
onSuccess : function(result) {
 if (result.length == 1) {
 ca_fdSetTextFieldValue(ca_fd.formId, 'email_addr', result[0].email_address);
 }
 else {
 ca_fdSetTextFieldValue(ca_fd.formId, 'email_addr', 'Cannot find user details'); 
}
 },
 onFailure : function(){
 ca_fdSetTextFieldValue(ca_fd.formId, 'email_address', 'Failed');
 },
}

Simply add : ca_fd.js.onLoad(); into the onLoad attribute of your form.

Hope this helps.

Good Morning Pedro.
 
Please find below, another example on how 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', {'user_ids_report_var':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);
 }
 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');
 },
}
 
Thanks and kind regards, Louis.