How to set custom field using lookupObject automation

[James Armstrong]

I'm trying to get around the 200 limit cap on the lookup automation by using the lookupObject function and pulling the data from attributes in Assets but receiving the following error which I can't seem to resolve.

Unable to retrieve the query from the custom smart value query:

AQL "objectTypeId = 5 AND "Client URL" = Test (test.domain.com) and objectSchemaId == 2" has invalid syntax at position 41 with error message "mismatched input '(' expecting {<EOF>, ' '}"

The idea is that the end user chooses a URL from a custom drop down field in the JSM customer portal. When the issue gets raised it then looks up the object and adds the database name to a different custom drop down field.

The flow I've got so far is:

And how the object looks in Assets

Can anyone help please?

[Trudy Claspill]

Hello @James Armstrong 

Welcome to the Atlassian community.

Can you show us the actual text that you have in the Lookup Objects step in the AQL Query?

I believe the correction you need is to put double quotes around your smart value in the AQL; i.e.

"Client URL" = "{{smart value}}"

[James Armstrong]

Hi @Trudy Claspill  Thank you.

Ah yes adding the double quotes has resolved that issue many thanks! 

But the next challenge is that its not actually returning any data.

I've now added a dynamic lookup table which looks like this.

And log action 

{{infrastructure.get(lookupObjects.Client Database)}}

It doesn't fail but it doesn't actually return any value.

Do you have any ideas what I'm doing wrong please?

[Bill Sheboy]

Hi @James Armstrong -- Welcome to the Atlassian Community!

FYI for that line you show as:

{{infrastructure.get(lookupObjects.Client Database)}}

What you are passing to the get() function is a list of things (i.e., zero-to-many Client Database values) and the get() cannot accept a list.

You could instead iterate over the lookupObjects and pass the value one at a time?

Kind regards,
Bill

[James Armstrong]

Hi @Bill Sheboy  Thank you,

The idea is to be able to lookup the object using the client url then return the database name. There will only ever be 1 item returned as the url will be unique and will only ever return 1 database associated with it. Perhaps I don't need to use the dynamic lookup but struggling to get the databasse name returned

[Bill Sheboy]

@James Armstrong -- When there is only one value, perhaps try the what you show and experiment...

Or, add a safety check with a Smart Values Condition to confirm {{lookupObjects.size|0}} equals exactly 1, and then use the first record in the get function to convert the list item to a single value:

{{infrastructure.get(lookupObjects.first.Client Database)}}