PowerApps Attachments - Saved to Azure Blob Storage with SharePoint and SQL

[@jonica5566]

I often watch tutorial videos at 1.5x or 2.0x speed but not this one. Helpful, to the point and with an engaging presentation. Thanks!

[@ShanesCows]

Very kind of you Jonica. 🙏

[@js-ict2855]

Really love these videos. Is there going to be a video about SaveData() & LoadData() for offline use of your apps. Or is there already one and did I miss it?

[@ShanesCows]

One day, one day. 😀

[@shyamksukumaran]

Excellent Shane! Again I am saying you are a life saver! Thank you.

[@ShanesCows]

Glad to help. 😀

[@UB-qc8yk]

Great Video, very timely. I like the way you put together all the pieces, with great explanations along the way.

[@ShanesCows]

Thanks

[@uiterlex]

Thanks Shane, nice video with AzureBlobStorage+AzureSQL+PowerApps. And no Flow needed, but just direct load and storage. And super affordable for business use. Still like to see an example of showing tiny image versions (loads) in the gallery for speed reasons with large galleries. Gr. Lex

[@ShanesCows]

Yeah. We have talked about it. To do that we would need to use Azure cognitive services to make the thumbnails.

[@uiterlex]

@@ShanesCows But is this fast enough for lots of images? Or schould we produce a thumbnail at storage time, by a small C# program?

[@ruifernandes2021]

Hello @@uiterlex has Shane said you can use the Azure cognitive services to create the Thumbnail, and you can do this at storage time without the need to create a C# program. you might need to create a web app in azure that calls the cognitive services and that you store the thumbnail to azure. Have a look at this post: powerapps.microsoft.com/en-us/blog/cognitive-services-with-powerapps-using-custom-connectors/

[@uiterlex]

@@ruifernandes2021 Thanks Ruis for your reply. I will need time to figure this out. Hope to find a simple way to show many images in a fast way depending on different prefixed sizes.

[@ruifernandes2021]

Hello Shane Just want to point that there is other way to delete files from the Azure Blob even if you didn't store the FileID in SQL. First of all congratulations on the videos, and Thank you they have been very helpful. Complete agree with you that for new developments the best practice should be storing the AzureFileID in the SQL. But in the cases of an App that is already in production where we were only storing the file link or File path and we need to add a delete capability or any other caase where we are not able to add the Azure File ID in SQL there is still a way to do it. This is done via a combination of the DeleteFile() Function and the GetFileMetadataByPath() Function, please see below: AzureBlobStorage_1.DeleteFile( AzureBlobStorage_1.GetFileMetadataByPath( "FilePath" ) .Id ) Hope this is understandable and helpful.

[@ShanesCows]

Awesome Rui. Thanks for the assist. 😻

[@thanasispapadopoulos6291]

Hey Shane, Amazing video , would it be possible instead of blob storage and SQL have the images saved in a SharePoint list ?

[@abhilashkumar6541]

Great video 👍 Shane just wanted to know can we show pictures from SharePoint list in url form in power apps image ? Like item 1 url 2 url 3 url So can we show the this url in the image of powerapps

[@ShanesCows]

Instead of using a picture control use a label and it should show the full URL

[@HienTran-mi6jp]

These are awesome videos. It already giving me ideas in how to implement a few things. Instead of using azureblogstorage, could it be done with onedrive storage?

[@ShanesCows]

Writing files to OneDrive isn’t at easy.

[@mappingman]

Hi Shane. Is this model convertible to SP Libraries? We don't have access to Azure Blob Objects, or a lot of the Azure ecosystem for that matter. One of the downsides of working for an underfunded government organization LOL

[@ShanesCows]

Mark use this kzbin.info/www/bejne/aYLEmoBuiMqKnK8

[@sanazp5347]

Loved your video and it was really helpful as I am new to PowerApps. Can i write the FL in a text field in my SharePoint list, without the SQL piece?

[@ShanesCows]

Yes you can patch it.

[@elierh442]

Hi Shane, great work. I love your videos. You're always explaining exactly the stuff I need for my work, I'm basically thought by you Lol. I'm actually stating out but I love it so far. I recently got stuck on a simple project where I need to send a pdf on an email but the receiver receives a corrupted file. Do you have any idea why is this happening or how it can be fixed? I haven't found any helpful information about it online. Thanks again!

[@ShanesCows]

Powerapps is most likely not corrupting the pdf I’d your formula is correct. I would double check your code for attaching. Have you tried other files, like an image, to see if you can get it to work?

[@priyamadi3464]

Hi Shane, your videos have helped me a lot in learning about this app. I have one question if I use the layout as the "Image, body, subtitle and title" then how to I add in different images because when I tried it only picks up the first image that I add in to the list. Please advise :)

[@ShanesCows]

That is how a gallery works. You need to be able to draw the images from a data source or with a function if you want them to be different.

[@terrisepulveda627]

Love the Dang Cameo! Thanks again Shane......Always A1 +

[@ShanesCows]

Thank you, thank you.

[@biglilb0y]

Really helpful video, Hi Shane, how can I email all the expense details together with the attachment in just 1 email?

[@ShanesCows]

Look for my video on Concat

[@avongonzales4736]

Hi Shane , During the Patch for OnSucess, When I checked the Value of AzureFileID: Value(FID) for SQL Server Table "ReceiptsAgain" Table the Value is Not an Integer, The FID I Get looks Like a Varchar for "JTJmYWt0Z2NvbnRyJTJmVmF4LUNhcmQtMi5KUEc=" , The Column Data Type "AzureFileID" is an INT ,

[@mavuripavan3243]

Hi, Shane... This video is very helpful. thank you. how can we do similar exercise using onedrive instead of Azure blobstorage?

[@ShanesCows]

Try this PowerApps upload file to SharePoint document library kzbin.info/www/bejne/aYLEmoBuiMqKnK8

[@SandeepSingh-lg7bq]

Hi Shane, Thanks for another wonderful video. I have a question, is it possible can we refresh Blob connection like other connections. i.e Referesh(AzureBlob)

[@ShanesCows]

It should always be a live connection?

[@Siddharthrockin]

Thanks a ton Shane! For a similar requirement, I added the functionality of adding multiple files to blob storage and storing the metadata in SQL table and the save works perfectly. Also, I can access files via storage explorer from respective folder. But, When I use Launch(FileUrl) it doesn't open the file. Is it the blob storage settings? I am not sure or is there a key / file ID I need to append to the string or What is the secure way of getting this done?

[@ShanesCows]

Check AZure Blob security. I need to do a video on this. 😑

[@Siddharthrockin]

@@ShanesCows Did you do a video master? Still not able to figure out these two puzzles and I need your wisdom Sensei _/\_ 1. How to display files from a specific folder in the blob (container/fol1/fol2) 2. When clicking on these files, how to open the file using the respective MS word app or MS Excel app using Launch?

[@leobrsp]

Hi, Shane! I have been trying to make this code for a sharepoint database (list), unfortunately I just can't go on. What should I do? Tks

[@hughvelez4565]

hi Shane. I have a question. Is it possible to have two attachment controls in the same PowerApps?

[@ShanesCows]

Yes. Each control will have different names but you can have as many as you want.

[@brindarajani7513]

Hello Shane , This is an excellent video . I was wondering if there is any way to attach files for every expense item I add and not just adding attachments for the master item.So similar to the Line Item and Line Cost , can we initially set Line attachment to blank and then on the "on success" can we patch the attachment corresponding to every expense item to the list ?

[@ShanesCows]

Yes. Just have to put the pieces together. I did It for a customer app.

[@raphaelbonillo2192]

Is possible to upload two images in diffrente columns in Lists or Doc Libraries?

[@ShanesCows]

Not that I have ever done.

[@bloopersbehindthescene4855]

Thanks for another amazing video Shane. Have you made the video on how to get the azure blob storage to work with private setting instead of public? if yes plz put a link on the reply.

[@ShanesCows]

I haven't. I should, we do it for customers but I just haven't gotten to that topic for a video. Sorry.

[@spook097097]

@@ShanesCows Hey Shane, any clues on how to do this? im stuck on this road block right now

[@spook097097]

Ended up using a PCF component to download the file in the browser github.com/rwilson504/PCFControls. no more SAS key required

[@nandakumarpa268]

Hi Shane .. I am working on powerapps custom page which allows the user to add and delete images to azure blob.But I am not able to find add picture contol as an input option to add files.Is there any alternative way to add files similar to add picture control.

[@josephkasumba1266]

Hi Sean, is there a way to get files (of different types) from on premise shared folders? I thought File system can help, but it looks like nope!

[@ShanesCows]

Try this kzbin.info/www/bejne/hpKQoYx7oLF7Y7c

[@hokemspitter]

Hi. Great stuff. I followed your video and have succeeded in writing to SharePoint, Azure Blob Storage and to the Azure SQL DB I allocated. All is cool, but the FID seems to be off. I have it declared in the database as int, but the Azure value varAzureFile.Id seems to be some encrypted string like this: JTJmZXhwZW5zZXJlY2VpcHRzJTJmbGVmdC1wYW5lbC1tZWFzdXJlbWVudHMuanBn. Do I have an error in the PowerApp or is it that the SQL column should be a varchar and not an int. Thanks for the super videos!

[@ShanesCows]

That is the Id of the Azure Blob not Sql table.

[@hokemspitter]

@@ShanesCows Should these lines remove the picture from the collection and also Azure? Remove(collectReceipts,ThisItem); AzureBlobStorage.DeleteFile(varAzureFile.Id); I am seeing the images still in the Azure Blob Storage container after I click the trash icon to run those two statements.

[@grahamross4264]

I had the same issue too. I changed the SQL field to varchar(250) not null, refreshed the data source in PA, then changed the 'submit' button to remove Value(FID) to FID. Seems to work for me.

[@juulspriet801]

hey, love your videos, they've helped me a lot but i've got some more problems. This is for my thesis for uni btw. I have a powerapps form to collect data, stored in a sharepointlist and now the goal is to create a calendarpoint in outlook to then make a new .pdf file, starting from a .dotx file. Could you help me with this?

[@ShanesCows]

I would look at Flow to help you easily create a calendar entry.

[@eugeniagher7297]

Hi Shane!I want to do this flow to upload de image JPG to a sharepoint list. For the moment y does not save it, ¿this flow is suposed to work? In what type of column I may save this file?

[@ShanesCows]

Try this video kzbin.info/www/bejne/aYLEmoBuiMqKnK8

[@giampaolocarli4728]

Hello Shane But id Ive a PDF File to store on SQL VarBinary colums on a Table How can do this ? ?

[@nehachandani8244]

Hello Shane Can we create folder in Blob Storage to upload image using Powerapps?

[@ShanesCows]

Yes. If the path you pass has a folder it will be created

[@KaineVarley]

Hi Shane, it's a shame that the blob storage Url has to be hard-coded. Is there a way of getting this from the connector itself rather? I have multiple environments (dev/test/prod), each with its own storage account (mystoragedev/mystoragetest/mystorageprod), so it would be great to be able to get the base url on the fly.

[@ShanesCows]

Not that I know of. :( Maybe set the URL in a variable during App OnStart so then you can just update the URL in one place.

[@KaineVarley]

Hi @@ShanesCows , I found a way of creating the link on the fly. Here's the code to do it in the OnSelect: Launch(AzureBlobStorage.CreateShareLinkByPath(BlobPath).WebUrl); Where BlobPath is the path property in the BlobMetadata that you get from your upload. (docs.microsoft.com/en-us/connectors/azureblob/#blobmetadata).

[@kmuralikrishna1582]

Really Thanks, Remove(Database, ThisItem) is not working for me, i just want to remove only attachment from the record

[@ShanesCows]

I haven't done that in a long time. Sorry, don't remember how it worked.

[@imran78k]

Is it possible to take photos from mobile device and add watermark to image like latitude and longitude

[@ShanesCows]

Probably but I have never done it. Sorry

[@balujims8647]

Hi Shane, I have a query on SharePoint. Is there anyway I can get the data from an email which I receive from a specific group to the SharePoint list.

[@DataSpinners]

Hi Balu - I think that you need Flow to make that happen, but you may find it challenging extracting attachments etc.

[@ShanesCows]

Yup. I have exactly this built for PowerApps911 contact emails.

[@balujims8647]

Shane Young thanks

[@balujims8647]

Rory Neary Thanks

[@stgiesbrecht]

Does this have the 10MB file limit that is currently on attachments when you use SharePoint Online as the repository?

[@ShanesCows]

Nope. Not sure what the limit is but I have never seen one.

[@dennisblotenburg7399]

Hi @@ShanesCows , I think there is still a 10MB limit. I just try it with a 110MB zip file and that's not working. Microsoft has announced that they will increase the limit to 100MB but that's not enough in some situations. (source: powerusers.microsoft.com/t5/PowerApps-Ideas/PowerApps-Attachments-to-SharePoint-lists-size-limit/idi-p/108672) If you have any work around for uploading big files by PowerApps, I'm interessed in that work around.

[@DataSpinners]

Hi Shane - are the settings for you files on azure blob storage 'Global Share' so that the path works? i.e. could anyone see these files?

[@ShanesCows]

Hey Rory. Yes, on this example they are. I have done it other ways, usually with the key. The url being useable makes life easier for emails and PBI.

[@grahamross4264]

I created a Shared-Access-Signature in the Azure Portal in the storage account section. I then added this code to the FL generation of the media button which enables access without changing the permission to 'public'. Whilst not ideal, it does give you more control to set an expiry time for access. The downside is having to regenerate the SAS, update the app and then run a script in SQL to correct the FL field.

[@DataSpinners]

@@grahamross4264 you're officially brilliant. Not sure if I fully understood, but well done!

[@sofyan471]

Hey, Can i read excel file and show it in power app from blob storage

[@ShanesCows]

Nope. Sorry

[@DanielFlores-cn1lc]

How do I change the file format? that indicates all the files

[@ShanesCows]

When you attach the file Azure Blob usually knows. I guess I don’t know what you are struggling with.

[@markdiamante2927]

Great tutorial! Anyone has a solution on how to avoid overwriting existing filename in AzureBlobStorage?

[@ShanesCows]

I don’t think I have a quick answer to this. When I fear the issue I have made all filenames were unique with a time stamp

[@markdiamante2927]

Thanks Shane! How about editing capability to filenames in AzureBlobStorage?

[@ShanesCows]

I got nothing there either. 😑

[@ahmedtoaima8002]

Hi Shane. anyone knows why Azurefield 24:29 can't be saved on my SQL server , it's just came as NULL value, and when i try to use Azurefield variable like Shane did 31:34 to delete the file from Azure storage its not working

[@blackmagic10000]

My code is as follows: Set(varAzureFile, AzureBlobStorage.CreateFile("receiptimages", AddMediaButton1.FileName, AddMediaButton1.Media));Collect(collectReceipts, {FN: AddMediaButton1.FileName, FL: "powerappsexpenses.blob.core.windows.net" & varAzureFile.Path, FID: varAzureFile.Id}) However, I am still getting the XML error upon clicking on my files. Why would this be?

[@ShanesCows]

That looks correct. 😑 If you use Azure Storage explorer can you see the file?

[@blackmagic10000]

@@ShanesCows Yes I can see the files and double-click to open them from within the GUI. However, in Powerapps I am getting that error you talked about despite my URL being perfectly correct. Any thoughts? It is odd to me that the file is uploaded correctly, but when trying to view it without having public access on I get the error.

[@blackmagic10000]

The only way I can get this to work is if I set my blob storage to public. Does anybody have any idea? And no my storage container parameter in the URL is NOT being duplicated like in the video.

[@ShanesCows]

I need to make a video that talks about Azure Blob Storage security. Soon I hope.

[@grahamross4264]

@@ShanesCows I look forward to hearing about this. As per my reply to Rory below, I created a Shared Access Signature, built this into the FileLink code to workaround which works without setting the access to global. However, you either have to set a really long expiry time for the SAS (not a great idea) or frequently create a new SAS, update the database and the PA code, which is high maintenance. Plus the only way to revoke an SAS is to rekey the storage account....

[@anthonyabieba9416]

Hello share nice video, thanks a lot. But please, can you help with a new video about the compose function, how do i subtract the value of a field that was added as part of the create an item list from the value of a field that is in the get item list and update the item in the get item using flow in the apply to each section, "@sub(item()?['Keyboard'], 1" This formula worked if I want to just subtract "1" from the child list, but how do I subtract a field from the create an item list that's the parent list. Please help!!!!

[@ShanesCows]

Yikes. I haven’t done that before. Sorry.