Keep log history from SharePoint list attachments with Power apps

Many times, we come across a requirement of keeping log of record in data source like SharePoint, data verse, SQL server etc. We can maintain log of updates on record of list, library, or documents. Log history mostly contains updated by, updating time and activity for that specific record.  

In this blog, we are going to see how we can maintain log of SharePoint list records.

Required Components: 

• SharePoint 
• Power Apps Portal 

Let’s start with an example 

Here, let’s take simple example of SharePoint list “Product” with Title, Cost, Quantity, Category columns and “Product Log” list to maintain log history of each record with columns Title (display name Updated By), Updated On, Activity, Parent ID. 

Keeping log history of fields from list is straight forward. On update button of form screen, before submitting form (or on Success property of that form), check data card value with the selected record field value, if it’s different then we have to patch record to “Product Log” list.  

If( 

    ProductGallery.Selected.Cost <> Value(DataCardCost.Text), 

    Patch( 

        'Product Log', 

        Defaults('Product Log'), 

        { 

            Title: User().FullName, 

            'Updated On': Now(), 

            Activity: "Product cost changed from " & ProductGallery.Selected.Cost & " to " & DataCardCost.Text, 

            'Parent ID': ProductGallery.Selected.ID 

        } 

    ) 

); 

Where, DataCardCost is the name of DataCardValue of Cost_DataCard from form in my case. Form and gallery have “Product” list as data source.

Like this, we can patch record to log on updates of text fields, choice fields, yes/no fields. 

Log of attachments: 

The attachment field from list is table of columns Id, Value, Absolute Uri, DisplayName. Attachment DataCard from form control of power apps is table of two columns Name, Value. For attached files we are comparing each attachments file of DataCard with product record attachment table (here, selected product record from gallery, hence selected gallery records attachments) using excactin function. Collecting not found files in collection, here colFileName.  

If collection not empty, then patch record to log list with files available in collection. Use concat function because attached files may be more than one. 

Clear(colFileName); 

ForAll( 

    DataCardAttachment.Attachments, 

    If( 

        !(Name exactin ProductGallery.Selected.Attachments.DisplayName), 

        Collect( 

            colFileName, 

            Name 

        ) 

    ) 

); 

If( 

    !IsEmpty(colFileName), 

    Patch( 

        'Product Log', 

        Defaults('Product Log'), 

        { 

            Title: User().FullName, 

            'Updated On': Now(), 

            Activity: "Attached file(s)- " & Concat( 

                colFileName, 

                Value, 

                ", " 

            ) & ".", 

            'Parent ID': ProductGallery.Selected.ID 

        } 

    ) 

); 

For deleted files, do exactly reverse, compare each attachments file of product record with datacard attachment table using excactin function. Collect not found files in collection, here colFileName1.  

If collection not empty, then patch record to log list with files available in collection. Use concat function, because deleted files may be more than one. 

Clear(colFileName1); 

ForAll( 

    ProductGallery.Selected.Attachments, 

    If( 

        !(DisplayName exactin DataCardAttachment.Attachments.Name), 

        Collect( 

            colFileName1, 

            DisplayName 

        ) 

    ) 

); 

If( 

    !IsEmpty(colFileName1), 

    Patch( 

        'Product Log', 

        { 

            Title: User().FullName, 

            'Updated On': Now(), 

            Activity: "Deleted file(s)- " & Concat( 

                colFileName1, 

                Value, 

                ", " 

            ) & ".", 

            'Parent ID': ProductGallery.Selected.ID 

        } 

    ) 

); 

Where, DataCardAttachments is the name of the DataCardValue of Attachments_DataCard from form in my case. 

Here, we have “Parent ID” column in “Product Log” list, so we can display log history of each record in gallery or data table if required. 

In this example gallery control and form control are on the same screen, hence used Gallery. Selected directly, if your gallery and form are on different screens then set selected record in some variable and selected record attachments in some collection to improve the performance of app. 

In below screenshot, we are updating cost value from 4000 to 4050 and attaching two files.

After clicking on update button records get patched to log list and record will get updated in gallery. Below is output.

In below screenshot, we are removing as well as attaching files from attachment card on the form. 

Below is the output, flashing activities log history in log list.

Conclusion:  

SharePoint list attachments with Power Apps without flow. By searching in attachments datacardvalues of edit form and attachments field of selected record from Gallery in power apps, we can track attached or deleted files of record using exactin function and collections. No need for power automate flow to look for versioning of record. Contact us to learn more.  

I hope you found this blog helpful. 

======

Follow us

LinkedIn