nFeed SQL query for field Customer Request Type

[Vidic Florjan]

I'd like to prepare SQL sentance for nFeed filed which contains "Customer Request Type".

Non working example:

SELECT ta.id, ta.name
FROM tablea ta
where $issue.get("Customer Request Type").stringList() = 'Critical incidents'

Customer request type is selected request type in Service Desk.

I want to filter values based on previously selected Request Type.

This example with another custom field field in Service Desk Form works as expected:

SELECT ta.id, ta.name, ta.parent_id 
FROM tablea ta
ta.parent_id in ($issue.get("Custom field x").stringList())

[Nicolas Simon]

Hello @Vidic Florjan,

I think the first example does not work because of the function ".stringList()". In fact, this function is used when you deal with multiple value field. Let us say "Field name" is a custom field which contains several values (val1, val2, val3, ...). Then nFeed will interpret $issue.get("Field name") as 'val1','val2','val3'.

With that in mind, you can understand why the first example does not work. But actually there is another reason. The variable $issue.get("Customer Request Type") does not return the Request type name, but some strange id. I am note sure you can use this Id to search deeper in the database for its corresponding name. For now nFeed does not support the $issue.get("Customer Request Type") variable neatly -> https://valiantys.atlassian.net/browse/NFEED-2852

As a workaround, you can try to identify what nFeed actually returns when you use the $issue.get("Customer Request Type") variable and replace 'Critical incidents' in your query by this id:

- Set the following Edit query:

SELECT '$issue.get("Customer Request Type")'

- Click on the "Test query" button
- Type an issue key in the "Key or Id" field where you know it has the 'Critical incidents' Request Type
- Execute the query and see what nFeed returns
- Use this value in your query instead of 'Critical incidents'

Regards,

Nicolas

[Vidic Florjan]

Hi Nikolas,

thank you for information.

I already tried to solve problem with this approach.  The result is message: The SQL request was either invalid or rejected by the remote database : ORA-00911: invalid character

Plugin is unable to resolve '$issue.get("Customer Request Type")'.

Do you get this information ("strange id") when you test query button?

>> I am note sure you can use this Id to search deeper in the database for its corresponding name

We can get this information from database.

So I just need to get information about exact syntax how to get data from this field ...

Regards,
Florjan

[Vidic Florjan]

I voted to https://valiantys.atlassian.net/browse/NFEED-2852 :-)