Issue: SQL Server: Invalid Mashup Expression

[BrendonBrooksP1]

My application is displaying the following error when a user runs it. It had be functioning fine until now. 

"The requested operation is invalid. Server Response: Invalid Mashup Expression using supplied values. Inner Exception: We cannot convert the value to null type Logical. ClientRequestId: 43449a7b-0081-e108-9179-0aa88aca09a0"

Ive confirmed that the Azure SQL server is up and running. I have not made any changes to any of my formulas for the past few weeks. What causes this error?

v/r

Brendon

[v-micsh-msft]

Hi @BrendonBrooksP1,

Just from the error message, it seems there are one or more fields not providing available value.

Would you please provide more details about the function you used here?

Currently it is not available for PowerApps to patch 'NULL' value, check the following document for more details:

Blank, Coalesce, IsBlank, and IsEmpty functions in PowerApps

Regards,

Michael

[BrendonBrooksP1]

@v-micsh-msft

Sure. The function that I'm using filters a gallery based on a selected item from a different gallery. I've used this function many times in the past and now it is no longer working:

SortByColumns((Filter('[dbo].[dboTable]', SerialNum = TextGall.Selected.SerialNum)),"maintAccomplishDate",Descending)

The same comes up in the adjacent gallery that is on the screen. Please the image below as a reference:

[diannottismh]

I just started getting this same error.  

I have a similar query "SortByColumns(Filter('[dbo].[CaseTable]',ClientID=ThisItem.ClientID)"

I get the error on a third of my entries.

David

[BrendonBrooksP1]

The error is hit and miss for me. I've already the previously given formula to: 

Filter('[dbo].[table]', SerialNum = TextGall.Selected.SerialNum)

Formula does return values, but still provides an error. I just find it to be bizarre because I've used this forumla many times in the past and have not had any issues. 

[BrendonBrooksP1]

Another odd thing:

When I use this formula, it doesnt' return datetime when I select it as an option in the Layout menu

SortByColumns(Filter('[dbo].[table]', serialNum = TextGall.Selected.serialNum),"maintAccomplishDate",Descending)

Whereas this formula, will return datetime when I select it in the Layout menu.

Filter('[dbo].[table]', serialNumb= TextGall.Selected.serialNum

For the record, I know PA can get a little screwy when it comes to DateTime in SQL, but neither one of these formulas are complex. 

[diannottismh]

Looks like either Azure or PA fixed the issue.  I am not having the issue now.  Hopefully it will stay that way.

[Anonymous]

Hi @BrendonBrooksP1,

This is a new bug in PowerApps. If you use SortByColumn() and select datetime column, no datetime values will be returned. Started to happen few days ago, all apps using this are affected.

To fix remove SortByColumn() funtion where it sorts by datetime value (I know it sucks because now you can not order by date, hopefully PowerApps team will fix this oen soon)

[Sienna]

Hi

Sorry to get into this conversation. I have never use sql in powerapps but I I will have to do few apps with connection to sql. I have noticed that there is a lot of problems with dates in sql. Have you tried to save the date in sql as value and then in your app when displaying format the value back into the date.. for example. Saving the date as  

Value(Today()) 

And than when you want to display the date simply format it back to date like this

Date(Year(ThisItem!DateValueColumn),Month(ThisItem!DateValueColumn),Day(ThisItem!DateValueColumn))

Wouldn't this solve the issues with the date in sql for good?

[BrendonBrooksP1]

I removed the SortByColumns() function from the gallery for now and used Filter() instead. Not ideal ,but at least a date is showing.