cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
james_hathaway
Advocate III
Advocate III

Filter Dataverse Table on isBlank(lookupColumn) is Inconsistent

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.

2 REPLIES 2
dpoggemann
Super User
Super User

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?  

Hope this helps. Please accept if answers your question or Like if helps in any way.
Thanks,
Drew
james_hathaway
Advocate III
Advocate III

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.

Helpful resources

Announcements
Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Power Platform Call June 2022 768x460.png

Power Platform Community Call

Join us for the next call on June 15, 2022 at 8am PDT.

PA Virtual Workshop Carousel 768x460.png

Register for a Free Workshop

This training provides practical hands-on experience in creating Power Apps solutions in a full-day of instructor-led App creation workshop.

PA.JPG

New Release Planning Portal (Preview)

Check out our new release planning portal, an interactive way to plan and prepare for upcoming features in Power Platform.

Users online (2,799)