'PowerApps Data Source Changed to Stored Procedure

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?

sqlpowerappspower-automate


Solution 1:[1]

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.

Solution 2:[2]

  1. Reset everything back, including the data source, then save and close the app completely, re-test.

  2. Remove any flow connections then save and close the app completely , re-test, then re-add the flow connections.

I don't why but PowerApps some times persist data connection errors until you have close the app down.

And just to confirm PowerApps doesn't support stored procedures as data sources, only as write using the patch function etc.

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 EitzenRob
Solution 2 RickWeb

Related Questions