General Discussion

 View Only
  • 1.  Ian’s Insights Ep3: Lesson’s Learned - Microsoft Lists, PowerBI and Flow

    Posted 12-30-2021 10:01
    Edited by Pat Cooney 02-06-2024 11:40

    I wanted to start off by saying Happy Holidays and Happy New Year from my family to yours! What is better than a little light ProntoForms light reading over the holidays? 

    I am posting this one into General Discussion because I am hoping to hear what a few things that everyone else has learned this year?

    Here is something that I learned about only recently and really wanted to write about it. I am not going to go into how to 
    create this destination or anything in detail in this post just a few things to think about when working with ProntoForms.  

    Microsoft Lists act slightly differently than our other data sources because of the way that Microsoft handles the API requests, but it is one of the more powerful out-of-the-box destinations. Here a few “gotchas” that I have learned over time in no particular order.  

    1. By default, whether you specify or not, we will pass the submission number into a ProntoFormsID column, this is a required column. If you have a pre-existing List, this might throw an error if you do not have that column created
    2. When you are setting up your destination, do not create the List before sending over your first submission. One of the main reasons for me writing this out is because if you point your destination to a List that does not exist, ProntoForms will automatically create the List and all the columns that are specified in the destination. 
    3. If the List exists already then you will need to make sure that all the column headers in your List match up exactly with what is defined in the destination. 
    4. A single line of text field in a List has a limit of 255 characters, if you think you need more than that, use the Multiple Lines of text.  
    5. Did you know that PowerBI can hook directly into Microsoft Lists so that you can perform analytics on the data that you are collecting, and that PowerBI Desktop is a free application so you can start looking at your data in a different way immediately. 
    6. At the same time, did you know that you can trigger a Microsoft Flow based on a List entry? Triggering other actions to happen. You might have seen my other post about converting xlsx to pdf using Microsoft Flow, while you are in there try playing around with it.  

                Now comes the part where things get more fun. Using a Flow, it can watch each new entry check for certain conditions and then do something (anything) else. Try to parse out our standard JSON output and see if you can flatten the data to analyze the data even more.  

                The combination of ProntoForms (to collect the data), Microsoft Lists (to hold the data), Microsoft Flow (to manipulate the data) and PowerBI (to analyze the data) you can fully automate and control what is happening in your business with all the oversight required to make sure things are running smoothly. 

                Happy Holidays and a Happy New Year 


                #TechTalkImplementation
                ------------------------------
                Ian Chamberlain
                Implementation Specialist
                ProntoForms
                ------------------------------
                #SharePoint #PowerBI #PowerAutomate​​​​



              1. 2.  RE: Ian's Insights Ep3: Lesson's Learned - Microsoft Lists, PowerBI and Flow

                Adopter
                Posted 01-20-2022 11:53
                Interesting stuff.  As always, I learn a lot in this community.

                Thanks.

                ------------------------------
                Scott Gilleland
                Senior Field Service Engineer
                Messer Cutting Systems
                ------------------------------



              2. 3.  RE: Ian's Insights Ep3: Lesson's Learned - Microsoft Lists, PowerBI and Flow

                Adopter
                Posted 04-25-2022 09:05
                Hi Ian! Do you know if it is possible to map images to SharePoint Lists? I have all my data from a form mapping into a SP List except the images that the user has taken, which has a max of 10 images for the question. When I look at the List in SP, it seems as though you can have attachments per row, or a column type for Image, but I haven't been able to map this correctly yet.

                1. When trying to map to a new column with a column type of Image, I get an error in ProntoForms ( I tried just 1 image to start)
                2. When trying to map to a column called "Attachments", ProntoForms destination is successful, but it did not attach the image. (You cannot see this column unless you right click and select Edit on the row, scroll down and then you are able to upload attachments here for this row).
                Maybe this isn't possible through ProntoForms yet or maybe I'm doing it wrong? It would be very helpful to be able to have all data related to that form in the list row instead needing to go into a separate SharePoint folder to look at the PDF copy which contains photos.

                Thank you!

                ------------------------------
                Lisa Zido
                Technical Systems Analyst
                GOJO Industries Inc.
                ------------------------------



              3. 4.  RE: Ian's Insights Ep3: Lesson's Learned - Microsoft Lists, PowerBI and Flow

                Posted 04-25-2022 16:48

                Hi Lisa

                I have lots to say on this, it is something we looked at extensively when we were working on updating the SharePoint list destination to be able to support repeatable sections.

                The short answer is: you need to send your attachment type questions, such as images, to a SharePoint document library instead of a SharePoint list.

                We have a separate data destination for a document library: Microsoft SharePoint Document Library Data Destination and the good news is that this destination already supports images (& other attachments) from the entire form, regardless of if they are in a regular or a repeatable section.

                Know that you can have more than one document library in a SharePoint site: Create a document library in SharePoint this allows you to separate and organize your files as you need.

                In the past, ProntoForms did have the ability to send attachments to a SharePoint list, but a few years ago, Microsoft introduced a new API where they no longer gave us the ability to send attachments to a SharePoint list. At the time, we weren't sure if it just wasn't there YET and if it was coming, but that API endpoint never got created. Now I suspect it was intentional so that users would move towards using SharePoint Document Libraries instead, as they are much better suited for files.

                Here's a diagram explaining how to use
                • SharePoint list data destinations (both regular & repeatable), and
                • a SharePoint document library data destination
                to store all of the data from your form in SharePoint. The document library data destination is the one in yellow.

                We hope to create a recipe explaining how to set something like this up, but please ask if you need any help!



                ------------------------------
                Natalie Tallon
                Product Manager
                ProntoForms
                ntallon@prontoforms.com
                ------------------------------



              4. 5.  RE: Ian's Insights Ep3: Lesson's Learned - Microsoft Lists, PowerBI and Flow

                Adopter
                Posted 04-27-2022 08:18
                Thank you SO MUCH for the detailed explanation and infographic!! This is very helpful for me since I am just getting started with SP Lists. I will have the images route to a library in SP instead.

                Side Note: I am loving these lists haha. I have a form (non-repeatable at the moment) routing to a new SP site into a list, and I have the PDF of the form going into a folder in the site as well. I made and published a Power BI report in BI Desktop that is feeding off the list, and then embedded this dashboard report on home page of the SP site. So now that site is a one stop shop for the data, the documents, and the analytics! :D

                ------------------------------
                Lisa Zido
                Technical Systems Analyst
                GOJO Industries Inc.
                ------------------------------



              5. 6.  RE: Ian's Insights Ep3: Lesson's Learned - Microsoft Lists, PowerBI and Flow

                Posted 04-27-2022 13:45

                Hi Lisa

                Wow, you've got it going all the way back into SharePoint, as what, an embedded chart? That hadn't occurred to me, I didn't know you could do something like that - cool! Why did you choose to do it this way? I could guess it is because it makes it easier for non Power BI users to see only the data you want them to, in an environment they are already familiar with - but I don't want to assume I know you reasons.

                Now that you are starting to use this, there is an possible enhancement that I will need to ask customers about, to see if it is something they care about.

                You'll have noticed that when we send data to a SharePoint list, we create a column called 'ProntoForms ID'. This contains the submission ID from our system.

                We don't create this column when we send files to a SharePoint document library. By default, those files have the submission ID in the filename that is visible in the library, but it isn't separate in its own column.

                My question is, do you see a need for the ProntoForms ID to be created and populated as a column in the SharePoint document library as well? 

                Thanks!


                ------------------------------
                Natalie Tallon
                Product Manager
                ProntoForms
                ntallon@prontoforms.com
                ------------------------------



              6. 7.  RE: Ian's Insights Ep3: Lesson's Learned - Microsoft Lists, PowerBI and Flow

                Adopter
                Posted 04-27-2022 14:22
                Yes! I needed a way to visualize the data easily for the supervisors of this particular project, and bonus points for the information all staying together within one site. We typically use Tableau for all reporting but this requires downloading data from ProntoForms into Excel and manually updating and republishing Tableau when new data as been added. Then to grant access to Tableau reports we need our IT team to help with that, and to view reports it obviously is in a separate website.

                Once I saw that I could use SP lists as a data source and that it had Power BI integration capabilities I was super excited. I could either make it a page in the site or embed it within the home page, so now the home of that site is a dashboard view of the data, with tabs on the side to view the ProntoForms list or PDF files, and now the photo library.

                For your last question, I was also wondering about the ID in the library. I think I would like it to be a default column to help with locating the forms that the pictures belong to, because I changed the file name of the photos within the form to include identifying details instead of defaulting to the ID.

                ------------------------------
                Lisa Zido
                Technical Systems Analyst
                GOJO Industries Inc.
                ------------------------------



              7. 8.  RE: Ian's Insights Ep3: Lesson's Learned - Microsoft Lists, PowerBI and Flow

                Adopter
                Posted 04-29-2022 09:38
                You can also put a link to the PDF in the SP list, you need to set it up as a mix of dynamic / static content from your data destination and typing a few rows of coding on the SP list, this way you can open the PDF right from the sharepoint list itself
                {
                "$schema": "https://developer.microsoft.com/json-schemas/sp/v2/column-formatting.schema.json",
                "elmType": "a",
                "attributes": {
                "href": "@currentField",
                "target": "_blank"
                },
                "txtContent": "@currentField"
                }


                ------------------------------
                Cesar McCoy
                KONE
                ------------------------------



              8. 9.  RE: Ian's Insights Ep3: Lesson's Learned - Microsoft Lists, PowerBI and Flow

                Posted 04-29-2022 11:07

                Thank you Cesar, that is really useful. 

                As we were designing how to handle repeatable sections in SharePoint, we had looked into using SharePoint lookup columns to create relationships like this. However, when we polled some of our customers, none of them were really using lookup columns and implementing support for them was a fair amount of work - so we didn't see the need to spend development effort on it.

                I'm happy to learn about this option to do it in a different, but still automated, way.

                Do you have any ideas on how to do something similar but in a 1 to many relationship? In the image I shared above, the document library has multiple images all linked to one form - it would be great if I could have a link in my main list that would open up a list of the attachments that came from that form.



                ------------------------------
                Natalie Tallon
                Product Manager
                ProntoForms
                ntallon@prontoforms.com
                ------------------------------



              9. 10.  RE: Ian's Insights Ep3: Lesson's Learned - Microsoft Lists, PowerBI and Flow

                Adopter
                Posted 04-29-2022 13:04
                what you can do is to create individual folders to store all pictures from a single submission, it would be a mix of dynamic and static content on your data destination (form submission document folder expression) where you will include the individual id of the record, now, in your sharepoint list on the column where you want to have the link available you can enter the code below.....just an idea, it can be done differently I guess but this is the first thing that came to my mind, feel free to contact me if you want to have a meeting to discuss more details

                {
                   "$schema": "https://developer.microsoft.com/json-schemas/sp/v2/column-formatting.schema.json",
                   "elmType": "a",
                   "txtContent": "Name for HyperLink",
                   "attributes": {
                      "target": "_blank",
                      "href": "='https://sharepoint.com/sites/sitename/firstFolderName/' + [$name of the sharepoint list column matching the n ame of the folder containing the pictures using _x0020_ to substitute spaces in case you have them]"
                   }
                }




                ------------------------------
                Cesar McCoy
                KONE
                ------------------------------



              Reminder: Content posted to our Community is public content.  Please be careful not to post Intellectual Property that you do not have permission to share.  For more information please refer to our Terms Of Use