Skip to main content
steffena0001
steffena0001
January 5, 2022
Solved

query conversion to text

  • January 5, 2022
  • 3 replies
  • 0 views

When I convert the ff. to text, the column name gets included. How will I be able to get the content only?

I get this [projectName:Project Nature], when it should be Project Nature only

Code used:

a!save(
ri!projectName,
rule!CRP_ProjectName(ri!request.projectId)
),

ri!projectName is a text variable

See below for the expression rule CRP_ProjectName that I made:

Best answer by harshas2775

You need to index the column from the output so that just the value is returned. Try this in your rule 

a!save(
ri!projectName,
index(rule!CRP_ProjectName(ri!request.projectId),"projectName",{})
)

Alternatively, you can add index in the CRP_ProjectName rule as well. Depends on usability, where do you want to index. E.g.

index(a!queryEntity(/*Lines of Code*/).data,"projectName",{})

3 replies

harshas2775
harshas2775Answer
Brainy
January 5, 2022

You need to index the column from the output so that just the value is returned. Try this in your rule 

a!save(
ri!projectName,
index(rule!CRP_ProjectName(ri!request.projectId),"projectName",{})
)

Alternatively, you can add index in the CRP_ProjectName rule as well. Depends on usability, where do you want to index. E.g.

index(a!queryEntity(/*Lines of Code*/).data,"projectName",{})

    steffena0001
    steffena0001Author
    January 5, 2022

    This worked wonderfully! Thank you very much!

    mikes0011
    mikes0011
    Brainy
    January 5, 2022

    Harsha's earlier answer is correct, though to be more clear and specific, we need to remember that the result of a!queryEntity is a DataSubset comprised of ".data" parameter as well as others like ".totalCount", paging information, etc.  Since you directly reference the ".data" property from your code above, then the only thing your output will show is the ".data" part.

    The second thing to remember is that ".data" will be an array of dictionary roughly matching the data type (i.e. CDT) being queried against by your DSE.  It will always assume this to be an array of dictionaries since any given query could return one or many results.  Then each item in that array will be an individual dictionary (even when you've told the queryEntity to only return one column of data), meaning you would need to directly reference the data property you want. 

    If you want your rule to return a single string (as opposed to an array of any type containing a single string entry), you'd actually need to index the first returned item of ".data", then get the name property from that. 

    You should also consider what you want to have happen when the rule is called with no project ID passed in - at the moment it would simply query the first item in the DB and return that, which doesn't seem correct.  The simplest way to handle this is usually to wrap the rule in if() logic to check for a null rule input, and if it's null, return null (this will prevent subsequent code from being executed which is a plus).

    The below example expands slightly on the steps involved in such a way as to illustrate (if you look at the local variable values after execution) what each step of the data looks like.

    if(
  a!isNullOrEmpty(ri!projectId),
  null(),
  
  a!localVariables(
    local!rawQueryResult: a!queryEntity(), /* this should be replaced with your existing a!queryEntity() call, minus the ".data" part at the end, which we're now doing on the next line */
    local!queryResultData: local!rawQueryResult.data,
    local!queryResultSingleRow: index(local!queryResultData, 1, null()),
    
    property(local!queryResultSingleRow, "projectName", null())
  )
)

    The Expression Guru
    Terms & ConditionsAccessibility statement