Agent Desktop

Get Involved. Join the Conversation.

Topic

    Shaheela D
    Retrieve Metadata values from ROQL
    Topic posted December 6, 2018 by Shaheela DRed Ribbon: 250+ Points, tagged Browser UI, Service Console 
    54 Views, 2 Comments
    Title:
    Retrieve Metadata values from ROQL
    Summary:
    Query to get incident custom menu field items
    Content:

    Hi All,

    I want to display a custom menu field values into a multi select drop down field.So I would like to retrieve a custom menu field value under the incident object. The difficulty I am facing is I am not able to get the values of menu field unless it is not stored against the incident object.

    Please refer the attached image of the menu field,from that I can't get the highlighted values.

    "Select customFields.c.x_trial_type.LookupName FROM Incident WHERE customFields.c.x_trial_type IS NOT NULL GROUP BY customFields.c.x_trial_type.LookupName"

    If the value is stored against the incident,then the above query is fine. But I want the values before saving the incident.

    If anyone aware of it please suggest here to retrieve the data from ROQL.

    Thanks,

    Shaheela

     

    Image:

    Comment

     

    • Ron Van Aken

      Hi Shaheela,

      I understand you have custom fields associated to incident records, and that the values get saved with proper association to the incident record.

      This seems to be your question: "But I want the values before saving the incident."

      You haven't said so, but it sounds like you want access to the custom fields for a New incident that hasn't been saved yet.  If this is not your question, please clarify.

      The values for the incident and custom fields get inserted into the database on the first save.  The incident record and custom fields do not exist in the database until the workspace is saved the first time.  Consequently, it's not possible to query this data before the SAVE without the incident ID or reference number in the WHERE clause of the query.

      All of the data you see in a New incident workspace are either in other tables (for example Contacts), or default values set in the workspace form.

      A high-level issue I see with your ROQL:  Grouping is used to summarize data.  Functions like count, min, max, sum, avg, and stddev would be included in the SELECT list.  The ROQL documentation says only functions are allowed in the SELECT column list.  Hopefully that's not correct.  In SQL the only non-summarized column in your SELECT list would be the column you are grouping by.

      For example if the documentation is correct:

      Select COUNT(*)
      FROM Incident 
      WHERE customFields.c.x_trial_type IS NOT NULL 
      GROUP BY customFields.c.x_trial_type.LookupName

      This would return a record for each LookupName value with one columns: <number of occurrences in the database>
      You would have no way of know which count goes with which count.  This seems useless.

      If the documentation is wrong:

      Select customFields.c.x_trial_type.LookupName, COUNT(*)
      FROM Incident 
      WHERE customFields.c.x_trial_type IS NOT NULL 
      GROUP BY customFields.c.x_trial_type.LookupName

      This would return a record for each LookupName value with two columns: <LookupName value>, <number of occurrences in the database>

      Maybe you're actually looking for a distinct feature like SQL, which does not seem to be available in ROQL.  There are no GROUP BY examples in the documentation.  Potentially it behaves like the following SQL:

      Select distinct LookupName
      FROM Incident 
      WHERE x_trial_type IS NOT NULL 

      You say your query works fine, so that' my assumption.

      Please let me know what you think.  Thanks.

      -R/

    • Shaheela D

      Hi Ron Van Aken,

      Thanks for your response.

      From your response the below query is fine but that record is saved in database.

      "Select customFields.c.x_trial_type.LookupName, COUNT(*)
      FROM Incident 
      WHERE customFields.c.x_trial_type IS NOT NULL 
      GROUP BY customFields.c.x_trial_type.LookupName".

      We can able to retrieve through Rest API and we achieved by the following method

      RNCPHP\ConnectAPI::getNamedValues("RightNow\\Connect\\v1_3\\Incident.CustomFields.c.x_trial_type")

      Thanks,

      Shaheela