How to Use Multiple Attachments Controls in Power App List Form

Introduction:

In this blog we will learn how to use multiple attachment controls and how to manage attached files with multiple attachment controls in Power Apps custom list form.

Technical Approach:

We will store files of all attachment controls in collection and then we will store files available in collection in document library using Power Automate.

Pre-requisites:

1. List should be ready for which you want to create Power Apps form.
2. Create Document Library with fields shown in below screenshot. Here in this example, Document Library name is Project Documents.

Create Power Automate

Step 1: Go to the https://make.powerautomate.com/ and select Instant cloud flow from Create menu of left navigation.

Step 2: Now, type the flow name and select the PowerApps (V2) trigger and click on Create button.

Step 3: Now, expand the Power Apps (V2) trigger and add below input fields:

In above inputs "ItemID", "UploadedByName" and "DocumentType" will be text type field and "File Content" will be file type field.

Step 4: Now, click on New step and new action for Create file in SharePoint as shown in below screenshot:

Step 5: Now, select the Site Address and your document library in Folder Path fields. In the File Name, copy-paste the below expression:

 @{triggerBody()['file']['name']}

And, in File Content field, select File Content which we defined in Power App input as shown in below screenshot:

Step 6: Now, add new step for Update File Properties as shown in below screenshot:

Step 7: Now select the Site Address and Library Name. In Id field, select the ItemId from Create file action as shown in below screenshot:

Step 8: For the Document Type, Uploaded By and Project ID columns, select the Power App inputs as shown in below screenshot:

Step 9: Now, all attachments are uploaded to document library, so we can delete documents form list item attachment. So to delete attachment, add a new step for Get Attachments.

Step 10: Select the Site Address and List name and in Id fields, select the ItemId from Power App input.

Step 11: Now, add a new step for Apply to each.

Step 12: Now in Select an output from previous steps field select the body of Get Attachments action as shown in below screenshot:

Step 13: Now, click on Add an action in Apply to each and select the Delete Attachments action.

Step 14: Now, in Delete Attachments action select the Site Address and List Name. In Id field, select the ItemId from Power App inputs and in File Identifier add the expression as shown in below screenshot:

Step 15: Now our flow is ready, so save the flow.

Use multiple attachment controls:

Follow below steps to use multiple attachment controls in Power App list form:

Step 1: Open your SharePoint list and select Customize form option from integrate menu.

Step 2: Once your Power App form open, one attachment field will already available. Now select attachment data card value and copy it.

Step 3: Now, paste it on the same data card, so Attachments data card will now show 2 file upload controls. You can add label above both of the file upload controls as shown below:

Step 4: Now we will need to add the document library in data source.

Step 5: Now select the first attachment control and add below code in its OnAddFile action.

 ClearCollect(SOWDocs,Self.Attachments);  

Above code will add all current selected files in SOWDocs collection.

Step 6: Now, we will use the same code for second attachment control. But with different collection name.

Step 7: Now, in both attachment controls, write the same code for OnRemoveFile action. So when user add or remove any file in attachment controls, we will have all currently selected files in collection.

Step 8: Now, click on Power Automate from left menu and click on Add flow.

Step 9: Now search for your flow name and click on the flow to add in the Power Apps form.

Step 10: Now, go back to the tree view of form and click on sharepointform1. And in OnSuccess method replace the below code.

 ForAll(  
   SOWDocs As Document,  
   UploadProjectManagementDocuments.Run(  
     SharePointForm1.LastSubmit.ID,  
     User().FullName,  
     "File to Upload",  
     {  
       file: {  
         contentBytes: Document.Value,  
         name: Document.Name  
       }  
     }  
   )  
 );  
 ForAll(  
   ProjectPlanDocs As Document,    
   UploadProjectManagementDocuments.Run(  
     SharePointForm1.LastSubmit.ID,  
     User().FullName,  
     "Additional Attachment",  
     {  
       file: {  
         contentBytes: Document.Value,  
         name: Document.Name  
       }  
     }  
   )  
 );  
 ResetForm(Self); RequestHide();  

Step 11: Now save and publish the power app form and selected document of both attachment controls will be stored in document library when we save the list item from Power App form.

Step 12: To show documents which are already uploaded in document library, you can use gallery control and show documents by filtering using Project ID and Document Type.

Conclusion:

This is how we can use multiple attachment controls in Power App list form. Hope this blog will be helpful for you!

If you have any questions you can reach out our SharePoint Consulting team here.

Azure | Copy Data from CSV to SQL Server using Azure Data Factory, Azure Logic Apps and SharePoint Document Library

 

Introduction:

We created a process to copy data from CSV file uploaded in SharePoint Document Library to SQL Server Database for a Drug Dispensary based out of Pennsylvania, USA.

For this, we created a Document Library in SharePoint Site where the user uploads the CSV file, so when a file is uploaded in the SharePoint Document Library, the Logic App captures the File that has been uploaded, and the content is passed from the Logic App to the Azure Data Factory for further processing. The Azure Data Factory has a copy data pipeline, which copies the Data of CSV to the relevant SQL Table.

Prerequisites:

·         Azure Subscription

·         Azure Storage Account

·         Azure SQL Database

·         SharePoint Site with Document Library

Implementation:

In order to execute the process, the following task needs to be carried out.

·         Create SharePoint Document Library.

·         Blob Storage Folders for individual Vendors.

·         SQL Database with Relevant tables and Stored Procedures.

·         Create Azure Data Factory (Datasets and Pipeline) for all three Vendors.

·         Logic App creation for the Vendors.

 

SharePoint Document Library

 The SharePoint Site Document Library is the initial trigger where the CSV file is uploaded and then the rest of the process is carried out. We created a Document Library, and the CSV file is uploaded into that particular folder.

Azure Blob Storage

·        Blob storage for all the vendors created, the storage is used to store the CSV while processing and then deleting after the process has been completed.

·        Each folder is dedicated to individual Vendors.

Create a Blob Storage

To create a container in the Azure portal, follow these steps:

1.       Go to your new storage account in the Azure portal.

2.       In the left menu for the storage account, scroll to the Blob service section, then select Containers.

3.       Select the + Container button.

4.       Type a name for your new container. The container name must be lowercase, must start with a letter or number, and can include only letters, numbers, and the dash (-) character.

5.       Set the level of public access to the container. The default level is Private (no anonymous access).

6.       Select OK to create the container.

SQL Server

Create a table to store the Data.

    

 

Create a user-defined data table type for the equivalent table, the UDT would be given reference in the Data factory for the processing

        

 

Create a Stored Procedure that inserts the data with the help of the UDT into the corresponding table.

      

 The above image shows SalesReportByPatient Parameter which would be passed in sink configuration in the Azure Data Factory.

Azure Data Factory

·         We need to create Data Factory for the Vendors.

 

 

·         Dataset which will be created would be for all the Tables in the Schema. Two datasets are created for each table.

o   CSV dataset

o   SQL dataset

1. Create a Dataset in a Data Factory

 

1.       Go to the Source tab. Select + New to create a source dataset. 

 

2.       In the New Dataset dialog box, select Azure Blob Storage and then select Continue. The source data is in Blob storage, so you select Azure Blob Storage for the source dataset.

 

 

3.       In the Select Format dialog box, choose the Delimited Text type of your data, and then select Continue.

 

4.       In the Set Properties dialog box, enter the source name. Select the checkbox for the first row as a header. And Select your source file. 

 

  

5.       New Page opens up. Select the Schema tab and click on the Import Schema it will import the schema.

                   

 

Note: Follow the same steps to Create Destination (only changes are select SQL Server database in step 2)

2. Create a Copy Data Pipeline

In the Activities toolbox, expand the Move and Transform category, and drag and drop the Copy Data activity from the toolbox to the pipeline designer surface. 

Configure Source

Go to the Source tab. Select + New to create a source dataset.

 

Source dataset: select dataset which we created for the source

Wildcard paths: in this field, add the Blob Storage's dynamic path. The dynamic filename comes from the Logic Apps as a parameter

3. Configure sink

Sink Dataset: Select the destination dataset

Stored procedure: select the stored procedure name which you created in the SQL server

Table type: select the table type which you created in SQL Server

Table type parameter name: add parameter name which you declare in the stored procedure

4. Configure Mapping

Select the source column and destination column for mapping data. 

Azure Logic App

 To Create Logic Apps follow the below steps

1.        Sign in to the Azure portal with your Azure account credentials.

2.       In the Azure portal search for Logic Apps.

 

 

3.       On the Logic Apps page, select Add.

 

4.       On the Logic App pane, provide basic details and settings for your logic app. Create a   new resource group for the purposes of this example logic app.

 

5.       When you're ready, select Review + Create. Confirm the details that you provided, and select Create.

 

6.       After Azure successfully deploys your app, select Go to the resource. Or, you can find and select your logic app by typing the name in the search box. 

 

7.       The Logic Apps Designer opens and shows a page Under Templates, select Blank Logic App. 

 

Logic App that triggers when a file is uploaded and calls Azure Data Factory to copy data. To create add the below actions.

1.       We have added trigger “when a file is created in a folder” in logic apps for getting the file from SharePoint.

                        

2.       To process file from SharePoint to Azure Data factory we need to save the file in Azure Blob storage so we did that in logic apps using a blob storage connector.

 

3.       To run the Data factory we have added “Azure Data Factory Connector”, We pass two parameters to the Data Pipeline File name and Filetype.

When the Logic Apps runs, it will get the file from the SharePoint Document Library and copy it in Blob Storage, followed by the Data factory pipeline.

Output: The Data is inserted into the SQL database after the Logic App is successfully created. The Logic App keeps on checking the file in the SharePoint list and runs at particular given time intervals, for example, every 15 minutes.

Conclusion:

 Azure Data Factory is a robust way to transfer data and makes it easier to get multiple CSV files from SharePoint, create pipelines process of those files and move it to the relevant Database. 

If you have any questions you can reach out our SharePoint Consulting team here.