SQL HierarchyID and PowerApps

[LaelH]

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

[RandyHayes]

@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.

[LaelH · Accepted solution]

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.