Filter Dataverse Table on isBlank(lookupColumn) is Inconsistent

[james_hathaway]

I have 2 galleries that look at Dataverse Tables.

In both, I am doing a filter on IsBlank(lookupField).

For one Table (Customers) - the filter works and returns the expected dataset.

On the other Table (Quotes) - the filter does not return any results. (And there should be 3).

I created a Dataverse View for the Quotes Table with the filter criteria "lookupField does not contain data", and sure enough, I see the 3 records. But in PowerApps, if I use Filter(Quotes,IsBlank(lookupField)), I get no results.

Again, doing Filter(Customers,IsBlank(lookupField)) work correctly and returns the results I require.

Does anyone know why the filter would work for one table, but not the other?

James.

[dpoggemann]

Hi @james_hathaway ,

Just verified this works fine with the actual Quotes table.   Did you try refreshing your datasource in your Canvas App?  Could it just be looking at old data?  

[james_hathaway]

Hi @dpoggemann ,

Yes, I tried refreshing the datasources - it's not old data. (btw - It;s actually a custom Quotes table, not the one OTB)

It's definitely the correct source, and that the data I want is definitely there, as I can retrieve it in other ways within the app, like a LookUp by GUID)

actually, when I try Filter(Quotes,IsBlank(Project)) - where Project is the lookup field - I actually get an error: "Error when trying to retrieve data from the network".

I ran the App Monitor, and found the following:

"request": {
      "url": "https://orgb9d8f5fe.crm11.dynamics.com/api/data/v9.0/sdx_quotes?%24filter=sdx_Project%2Fsdx_Project+eq+null&%24expand=sdx_Customer%2Csdx_Project",
      "method": "GET",
      "headers": ........
    },
    "body": {
        "error": {
          "code": "0x0",
          "message": "Could not find a property named 'sdx_Project' on type 'Microsoft.Dynamics.CRM.sdx_project'."
        }
      }

So it looks like there's actually a bug in the Dataverse connector - it's not correctly translating the Column DisplayName into the LogicalFieldName for the API call...

It's turned my filter parameter "IsBlank(Project)",

and turned it into the API filter string: "sdx_Project.sdx_Project eq null"

whereas it should be: "sdx_project.sdx_projectid eq null"

The Unique Identifier column for a Table always ends with "id", so I really don't know why it's trying to filter on the wrong column name... And I also have a feeling that the API is case sensitive, so that's wrong too!

I've tried using the logical names in the Filter Formula in PowerApps, but no luck - just doesn't recognise the column...

The Project Table has an Alternate Key (for syncing from a SQL database)...

Do you think that is upsetting things?

James.