cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
DTreg1
Level 8

Creating App from Site Inspection but with SQL Server/Flow for Photos

Hello,

 

I am new to the PowerApps app and was wondering if there is someone that can help me walkthrough what steps I need to take to create an app with the same photo taking design from the site inspection template, but connect the data source to SQL Server. I believe in order for me to do this, I would need to create a folder to store the photos as well correct?

 

A few questions that popped into my head while writing this:

  1. How do I recall the photos for review?
  2. How do I setup the SQL Server for the photo column? varBinary(max)?
  3. Do I need to setup two SQL tables like the example has for one drive excel tables?
  4. What is the process that happens when someone takes a photo? I would like it to save to a collection first like the template, then they would hit save later, once they have filled out all the information and it would push to SQL Server/Flow. Do I need separate collections to do this?
  5. When I select a Flow that starts when I select the PowerApps "when a record is created" trigger, what is that looking at to identify when a new record is created? The Collection? The way I understand it, the App would be saving directly to SQL Server and then Flow would start the process of taking the photo to a folder and creating the string for SQL Server correct?

Any help would be greatly appreciated, I will probably have more questions as this goes on.

 

Thank you

2 REPLIES 2
DTreg1
Level 8

Re: Creating App from Site Inspection but with SQL Server/Flow for Photos

Update:

 

In an effort to keep things moving, I tried something else but still need some help. Basically I created an app with the same setup and Site Inspection template but instead of "SiteInspector" tab in the excel file being stored in onedrive, I used a SQL table. The problem I'm having is that when I save an inspection, I'm unable to view the thumbnail image I took in the browse gallery.

 

What could be the issue?

Super User
Super User

Re: Creating App from Site Inspection but with SQL Server/Flow for Photos


@DTreg1 wrote:

Hello,

 

I am new to the PowerApps app and was wondering if there is someone that can help me walkthrough what steps I need to take to create an app with the same photo taking design from the site inspection template, but connect the data source to SQL Server. I believe in order for me to do this, I would need to create a folder to store the photos as well correct?

 

A few questions that popped into my head while writing this:

  1. How do I recall the photos for review?
  2. How do I setup the SQL Server for the photo column? varBinary(max)?
  3. Do I need to setup two SQL tables like the example has for one drive excel tables?
  4. What is the process that happens when someone takes a photo? I would like it to save to a collection first like the template, then they would hit save later, once they have filled out all the information and it would push to SQL Server/Flow. Do I need separate collections to do this?
  5. When I select a Flow that starts when I select the PowerApps "when a record is created" trigger, what is that looking at to identify when a new record is created? The Collection? The way I understand it, the App would be saving directly to SQL Server and then Flow would start the process of taking the photo to a folder and creating the string for SQL Server correct?

Any help would be greatly appreciated, I will probably have more questions as this goes on.

 

Thank you


 

Because you are asking for a lot of information on how to create a full app I'll only touch on a few questions you've asked that I'm familiar with. 

 

 

2. There are a few ways to do this that depend on which image capture tool you are using.

Method 1: If you are using the built in camera you can use a collection to gather the image(s) and then paste the image URL value to a varchar(MAX) field in SQL which should show up as Base64. You can also use a Flow at ths same time to Patch each of these images to a Sharepoint storage location as a .jpg

 

See this post for more info on how to use flow to save from the camera in the app to sharepoint:

http://johnliu.net/blog/2017/5/taking-a-picture-with-powerapps-and-sending-to-sharepoint-with-just-f...

 

 

Method 2:If you are using the Add Picture control and capturing either from a saved image on your device or from the devices camera I'd still suggest putting the image into a collection. Then you can Patch that image value into SQL as varbinary(MAX) and use SQL to convert it to a base 64 which can be viewed in PowerApps. Similar to the default camera app, you can Patch the images to Sharepoint as a jpg but it takes a bit of workaround to do so.

 

See the video from @paul_culmsee here for how to use Method 2, it's a bit more complex but is infinitely more adaptable once you've gotten the basics down: 

Video: https://www.youtube.com/watch?v=mp-8B1fLrqs

Link to instructions: http://www.cleverworkarounds.com/2017/11/13/a-sample-openapiswagger-file-for-powerapps/

 

The SQL command to convert the Method 2 binary to Base64 is located here:

http://kbase.icbconsulting.com/dynamics-crm/sql-functions-for-binary-to-base64-conversion

 

Short version is you do an UPDATE/SET and cast the varchar(MAX) field from the varbinary(MAX) using:

UPDATE ImageTable

SET ImageFieldToUpdateToBase64 =

'data:image/jpeg;base64,' + CAST('' as xml).value('xs:base64Binary(sql:column("ImageTable.ImageBinaryField"))', 'varchar(max)')

 

etc. etc.

 

4. Again depending on which method in number 2 I've mentioned your best bet is to always patch a Collection from the app to a SQL table and use a Flow to create a file in Sharepoint or wherever.

 

The process we use is the user takes/uploads images (we use method 2) and then when they "Save" from within the app the collection is patched to On-Premise SQL, a Flow is run for each collected image to create a sharepoint file, and then a SQL procedure is kicked off (again using flow) to do the binary to base64 conversion. The images are then available immediately in Sharepoint in the job folder and they are available to the App through the newly updated base64 image column.

 

 

Overall a combination of what I've written above and the information in the thread I've linked below should help you get to where you need:

 

https://powerusers.microsoft.com/t5/Mobile-App-iOS/Truly-No-Code-Solution-for-Saving-Photos-from-Pow...

 

Good luck!

 

edit: to add method 2 bullet point