Unanswered

SQL Connection issue, not displaying field as intended

(0) Share

Report

Posted on by TechIT

Let me preface by saying that I am NO expert power apps designer, I have built 4 or 5 relatively simple apps for my company so far and am learning completely as I go.

We have an old (I mean VERY old, like Windows 95 era) application that is still floating around and is being used for tracking our IT assets, who they are assigned to, etc. The back end database is a Microsoft SQL server that lives on-premise, connecting to Power Apps using an On-Premise Data Gateway. The goal of my power app is to be able to pull data from a specific table within that database, tblISDeptAssets.

With it I created a gallery that searches that table based on asset tag number or employee assigned. So far, so good. Once an item is selected, you can view all the pertinent column values that I care about in that table, through the use of an auto-generated Power Apps form for the selected Gallery item. I can make changes and patch them in, all works well, except for one specific field: the description (basically the free form notes written down for that asset).

It is saved in the database as a hexadecimal encoded blob, where the plaintext typed into the old application tracking software is encoded into hexadecimal in the following pattern: 0x<4 characters of Hex encoded binary that represents the length of the following string><Plain text encoded into Hex>. So for example, an empty notes field is 0x0000, whereas a populated notes field would be something like 0x100044656C6C20313922204D6F6E69746F72. I have figured out how to decode and re-encode this properly, but my issue is that Power Apps does not actually report this value like it does all the other columns. Instead it reports a reference link, ex:
appres://datasources/tblISDeptAssets/table/%5Bdbo%5D.%5BtblISDeptAssets%5D/rows/870/inline/Description

Can anyone help me figure out how to get to the actual value, instead of the Database reference shown above?

Categories:

Building Power Apps

I have the same question (0)

All responses (9)

Answers (0)

v-yueyun-msft on at

Like (0)

Report

Hi , @TechIT

The appres://blobmanager address you’ve encountered in Power Apps refers to an encoded file location in Azure Blob Storage. Can you try to convert it to base64 string in your side if it can help.

For more information, you can refer to this case:
Solved: Is it possible to convert appres://blobmanager to ... - Power Platform Community (microsoft.com)

Best Regards,

Yueyun Zhang

Was this reply helpful? Yes No
TechIT 6 on at

Like (0)

Report

Hmmmmm, well this is not stored in Azure Blob storage, it is a blob field in a table in a MS SQL server on premise. Also, it is confirmed to be encoded in base 16 Hexadecimal, rather than base64. Do you have any ideas how to decode it to a string in Power Apps and make it display the value? Or at least get it to display the Hexadecimal value found in the table so I can try to manipulate it with some form of expression in Power Apps to convert it?

Was this reply helpful? Yes No
EddieE 4,641 Moderator on at

Like (1)

Report

@TechIT

Just chiming in here.

I think you maybe the first person with this problem on these forums - at least as far as I can tell - so there may not be a ready made solution for you. Having said that, if this was my issue, I'd investigate doing the conversion server side rather than inside PowerApps - eg run some SQL script to convert the hex to text and place that into a second field? No idea how to do that, or even know if it's possible ... surely it can be done!

You could look at the JSON() function - linked by @v-yueyun-msft - but I don't think there's a solution there. You could look at Power Automate - https://powerusers.microsoft.com/t5/Building-Flows/Converting-hexadecimal-values-to-strings/td-p/807301 - once again, this only appears to be part of the solution you need.

You could try displaying the blob in a gallery or putting it into a collection to see if you can unpick what PowerApps is doing with it but I think it won't be overly helpful.

A guy I know who dabbles in PowerApps + SQL a lot maybe able to offer some advice here @timl ?

Sorry I couldn't be anymore help - hope you get a solution.

Was this reply helpful? Yes No
timl 36,383 Super User 2025 Season 2 on at

Like (0)

Report
Hi @EddieE - thanks for tagging me - hope you're well.

@TechIT - - the best way to resolve this is to perform the conversion server side.

What I would do here is to carry out the conversion through a SQL view. On the basis that description is of data type varbinary, the following view will convert description to a string. You should then be able to connect to this view from Power Apps and view the Hex value. This approach will be delegable and preferable to alternatives such as calling JSON() or using a Flow.

CREATE VIEW vuISDeptAssets AS SELECT RecordUID, AssetType, CONVERT(varchar(max), Description, 1) AS [Description] FROM tblISDeptAssets

Was this reply helpful? Yes No
TechIT 6 on at

Like (0)

Report

Update:

What ended up inevitably working for me for that field was to call a flow to retrieve and translate it. I passed along the unique key of that row to the flow, then Power Automate did a Get Rows action and pulled that row's data, where it appeared that the Description was being pulled as a Base64 encoded string, rather than a Hexadecimal (as stored in the SQL server). Strangely enough, all I had to do to decode it into a plain text string was to save the output of the description field from that row (from the SQL Get Row action) as a Variable (string). Somehow, Power Automate automatically converted that variable's content to Plain Text / ASCII, no other expressions required. Then I had the flow respond to the Power App with the decoded description.

As far as implementing it into the app, I just setup the actions for the Gallery Item On Select to first set a global variable to "Loading...", then call the flow (which responds back with the description), then navigate to the next screen where "Loading..." displays in the description field until the flow responds back and finally changes that global variable from "Loading..." to the actual Description.

Not the most elegant solution, and adds a bit of latency for the flow to run, but overall it is quite serviceable, and simplifies the design of the app substantially I think. I think because Power Automate flows handle writing changes and doing conversions back to the server quite easily as well, I think I will utilize a separate flow for making changes to the data as well, rather than a patch function in Power Apps like I originally intended.

Was this reply helpful? Yes No
timl 36,383 Super User 2025 Season 2 on at

Like (0)

Report

Hi @TechIT

Thanks for the update. I'm glad you found something that works.

Like I mentioned, my inclination would be to do this with a View as you'd be able to see the Description text immediately without calling a Flow, and you could avoid the display of the 'Loading...' text whilst the app fetches the result from the Flow.

Was this reply helpful? Yes No
TechIT 6 on at

Like (0)

Report

@timl Unfortunately I have limited access (and knowledge with SQL) to modify the database much or create things within SQL. I have to request our DBA to do things like that, and he is SWAMPED right now.

I do like your idea better though overall. One question I would have, if I have a view that shows the converted description, would I be able to write plain text description back to that view, and would that change to the view then get converted back to Hex and stored properly in the table? This app will be used for making changes to these fields as well, so if creating the view is more of a read only kind of operation, then it won't 100% solve the issue.

But, that question is born more out of my own ignorance of SQL server and what you can do with it than anything.

Was this reply helpful? Yes No
EddieE 4,641 Moderator on at

Like (0)

Report

@timl

Yeah mate, doing fine. It looks as though you have things sorted here so I'll leave this one to you.

@TechIT

I'll leave you with @timl , he's more than capable with any SQL questions you may have. All the best!

Was this reply helpful? Yes No
timl 36,383 Super User 2025 Season 2 on at

Like (0)

Report

Hi @TechIT

The View would be read-only so yes, you'd need something else to write to the field. This could either be a Flow, or you could create a stored procedure, which you could call directly from Power Apps without going through a Flow.

Was this reply helpful? Yes No