Blog

Data - How to make a copy of a record

A common requirement is to make a copy of a record, based on an existing record. This post describes some examples of how to carry out this task, including how to copy an existing record from a form, a gallery control, and how to write formula that copies the last created record.

With any data centric app, it can be very helpful to provide a feature that allows users to copy records. This post covers how to add this functionality on screens that contain forms and galleries, and how to write formula that copies the last created record.

1 - Making a copy of record from a form

Let's start by modifying an existing edit screen with an edit form. We'll add button that copies the contents of the form into a new record.

To carry out this task, we add a button and add the following formula to the OnSelect property.

Patch(Client, Defaults(Client), EditForm1.Updates)


The call to the patch function expects the following 3 arguments:

  • The data source (Client in this example).
  • A record to add or update. Here, we specify a new record by calling the Defaults function.
  • The fields to add or update. Here, we specify the Updates property of the edit form. This property returns the contents of all items on the form.

2 - Copying a record from a gallery control

Another place where we can add a 'copy record' feature is on a gallery screen.To build this feature, we can add a button to the item template of a gallery, and add the following formula to the OnSelect property.

Patch(Client, 
      Defaults(Client), 
      {
        Firstname:ThisItem.Firstname &  "(Copy)",
        Surname:ThisItem.Surname,
        Address1:ThisItem.Address1
      } 
)


Again, we call the patch function to create a new record. To specify the field values of the new record, we build a record that we pass as the third argument to the patch function. We use the ThisItem identifier to the reference the existing record in the gallery.

Notice how we add the suffix "(Copy)" in the Firstname field of the new record. This identifier can help the user identify the new record.

To extend this technique, we can improve this formula by adding some basic error checking, and success/error notifications for the user.

If(IsBlank(Patch(Client, 
                 Defaults(Client), 
                 {
                     Firstname:ThisItem.Firstname &  "(Copy)",
                     Surname:ThisItem.Surname,
                     Address1:ThisItem.Address1
                  } 
             ).ID), 
   Notify("Error",NotificationType.Error),
   Notify("Record copied successfully", NotificationType.Success)
)
Our example here uses a SharePoint data source. Each SharePoint record includes an automatically generated ID field value. We use the return value from patch to inspect the return ID value. If this is blank, the record was not created and we can alert the user to this error by calling the notify function. If the ID value is not blank, we display a success message to the user.

3 - Copying the last created record

To add a feature that enables a user to copy the last created record, we can add a button to a screen and add the following formula to the OnSelect property. 

With(
    {
        lastRec: First(
                       FirstN(
                              SortByColumns(Client, "ID", Descending), 
                              1
                            )
                 )
    },
    Patch(Client, 
                 Defaults(Client), 
                 {
                     Firstname:lastRec.Firstname &  "(Copy)",
                     Surname:lastRec.Surname,
                     Address1:lastRec.Address1
                  } 
             )
)

As with our previous examples, this method relies on the patch function. From inside the 'with' block, we look up the last record and expose it through the lastRec identifier. We build a record that we pass as the third argument to the patch function, and we use lastRec to reference the last created record.

To clarify the syntax to retrieve the last created record, we sort the list by the ID column in descending order. We call the FirstN function to return a table with one record only (for performance reasons), and we call the First function to retrieve this record. This is a formulaic patterns that enables us to retrieve records in a delegable way. As this screenshot beneath shows, this pattern enables us to retrieve the ID of the last created record in a list with more than 40,000 records.


4 - Copying the last created record by user, or some other criteria

To copy the record that was last created by specific user, we can adapt the formula as shown beneath.
 
With(
    {
        lastRec: First(
            FirstN(
                SortByColumns(
                    Filter(
                        Client,
                        'Created By'.Email = "timl@powerappsguide.com"
                    ),
                    "ID",
                    Descending
                ),
                1
            )
        )
    },
    Patch(Client, 
                 Defaults(Client), 
                 {
                     Firstname:lastRec.Firstname &  "(Copy)",
                     Surname:lastRec.Surname,
                     Address1:lastRec.Address1
                  } 
             )
)
This formula applies the filter function to retrieve the last record that was created by user that matches the email address "timl@powerappsguide.com".

We can further modify this formula to apply additional criteria to the filter function. We can also adapt the filter condition to return the last record that was created by the current user. My post here describes a technique we can use to filter records by current user in a delegable way.
http://powerappsguide.com/blog/post/sharepoint-how-to-filter-records-by-the-current-user

Conclusion

A common requirement is to make a copy of a record based on an existing record. This post described how to add this functionality to screens that contain forms and galleries, and how to write formula that copies the last created record.
Previous
Next
Related posts