Filtering a Dataverse Lookup Column

[nickellis74]

Hi all,

I'm trying to lookup a rowset in the 'Opportunity Product' table. I know the Opportunity and have retrieved it earlier in the flow.

For those that are not familiar with Dynamics Sales Professional, the table has a field labelled Opportunity with a column name of opportunityid. That column is a Lookup of the Opportunity table. I assumed (foolishly, it seems) that this would work:

opportunityid eq @{outputs('Get_the_Quote')?['body/_opportunityid_value']} and productname eq '@{triggerOutputs()?['body/productname']}'

I've tried various forms, everything I can think of... So with the quotes:

opportunityid eq 'c3a8892d-etc' and productname eq 'Installation Services'

returns

'A binary operator with incompatible types was detected. Found operand types 'Microsoft.Dynamics.CRM.opportunity' and 'Edm.String' for operator kind 'Equal'.'

while without the quotes:
opportunityid eq c3a8892d-etc and productname eq 'Installation Services'

returns

A binary operator with incompatible types was detected. Found operand types 'Microsoft.Dynamics.CRM.opportunity' and 'Edm.Guid' for operator kind 'Equal'.

opportunityid eq opportunities(c3a8892d-9c6e-eb11-b1ab-000d3a86b23d) and productname eq 'Installation Services'

returns

An unknown function with name 'opportunities' was found. This may also be a function import or a key lookup on a navigation property, which is not allowed.

while

opportunityid eq https://clientdomain.dynamics.com/api/data/v9.1/opportunities(c3a8892d-etc) and productname eq 'Installation Services'

returns

Syntax error at position 23 in 'opportunityid eq https://clientdomain.dynamics.com/api/data/v9.1/opportunities(c3a8892d-etc) and productname eq 'Installation Services''.

I feel like I've tried everything, but I don't believe it's just impossible to filter on that column so obviously I haven't!

Any ideas?

Thanks

Nick

[DamoBird365]

Hi @nickellis74 

Are you missing the '' on the _opportunityid_value? You have it for the product name.  

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Cheers,
Damien

P.S. take a look at my new blog here

[nickellis74 · Accepted solution]

Thanks Damien,

As mentioned lower down the post, I tried with and without quotes.

Elsewhere, someone suggested just pulling the whole list, unfiltered, to see what gets returned, which I did and can now tell you that the answer to this problem is:

_opportunityid_value eq c3a8892d-etc

No quote marks. Which I think it's safe to say no amount of reading the documentation was ever going to get me to! Thanks to Pieter at www.sharepains.com for that!

[Red_Nug]

Thank you so much for that. I've spend half a day looking for this solution !