SQL HierarchyID and PowerApps

(0) Share

Report

Posted on by LaelH

195

Hi,

I have a traditional SQL hierarchy table (id points to parentid in the same table). I am trying to make use of the SQL HierarchyID to see if I can get any improved performance but PowerApps wants nothing to do with this table as a data source. I get this error :

"Service call was successful. However, there was a problem processing the server response. Please refresh your data source and republish your app."

I get it if I try and add the table to "items" on a gallery, or if I try and collect the records into a collection , or patch any record or remove any record.

Any ideas, other than removing HierarchyID from the table and giving up on the performance benefits?

Thanks

Regards

Lael

Categories:

Building Power Apps

I have the same question (0)

All responses (2)

Answers (1)

RandyHayes 76,297 Super User 2024 Season 1 on at

Like (0)

Report

@LaelH

You'll need to abandon the HierarchyID - it is not supported in the SQL connector.

Take a look at the docs on datatypes supported - you'll see a note about types not supported, it includes the HierarchyID.

Sorry to say...but that will be what you need to do.

I hope this is helpful for you.

Was this reply helpful? Yes No
Verified answer

LaelH 195 on at

Like (1)

Report

Hi @RandyHayes

Thanks for the feedback. It sent me down another thought path which resulted in a nifty workaround which means I don't have to abandon my HierarchyID and its associated efficiencies.

1) I created a SQL view of the table, but without the Hierarchy ID in it, so that any PowerApps front-end work can be done off that view.
2) Then for the required back-end table action (insert, update, delete) I created a very simple generic Flow called SQLQuery, which allows me to construct any query I want in PowerApps and send that to flow for execution.

eg: SQLQuery.Run("Update MyTable set MyColumn = 1 where ID = 1")

So I have got the best of both worlds now and my issued is resolved.
Thanks again for pointing out that documentation.

Was this reply helpful? Yes No