PowerApps Data Source Changed to Stored Procedure

Question

The data source in PowerApps gallery was a SQL View.

Search('[dbo].[vwCandidate]', textSearchCandidate.Text, "NameLast", "NameFirst", "MiscellaneousTags", "EmailAddress", "PhoneNumber")

The selected record populated a global variable for the form item.

Set(varCandidate, gallerySearchResults.Selected)

Everything works as expected. Then, I changed the data source to use a stored procedure to move the search from PowerApps to SQL server. After doing so I received the error message

"Incompatible Type. We can't evaluate your formula because the context variable types are incompatible with the types of the values in other places in your app"

I cannot revert back to the view that was working without getting the same error. I'm hoping my only option is NOT to use a new variable and change every occurrence in the form/App. I'd like to avoid this if possible.

I cannot view the form so I'm not sure how to debug properly. My hunch is the date fields being returned via Flow are causing the problem. They are 'smalldatetime' types and the Flow is returning a string 'yyyy-mm-ddThh:mm:ss.000' even though 'date' is being requested.

"PhoneNumber": {
  "type": "string"
},
"CandidateStatus": {
  "type": "string"
},
"DateApplied": {
  "type": "string",
  "format": "date"
},

Flow JSON here does not seem to like any of the other 'date' format types.

Are there any workarounds from Flow? Should I reformat the date values when I am setting the global variable? Advice?

EitzenRob EitzenRob · Accepted Answer · 2019-03-14T00:49:13

Turns out, I was on the right track thinking that the DATE data type coming from Flow as a string. Here's why:

A new record was created using a Patch function while setting the global variable:

Set(varCandidate, Patch('[dbo].[candidate]', Defaults('[dbo].[candidate]'), {DateApplied: DateTimeValue(Text(Now())), CreatedDate:DateTimeValue(Text(Now())), CreatedBy:varUser.Email}))

The "DateApplied" field was a "DATE" type in the SQL table and it was coming from Flow as a string "2019-03-13T17:40:52.000". The recordset from Flow was being set to the same global variable when I wanted to edit the record

Set(varCandidate, gallerySearchResults.Selected)

The error "Incompatible Type" (see question for full error message) was due to this field being a "Date Value" in a new record and a "string" in an edit record.

My fix is to remove this "Date" type fields from the patch and modify the Flow to retrieve the newly created record record by ID.

PowerApps Data Source Changed to Stored Procedure

2 Answers