Blogs

Performing CRUD Operations in Power Apps using any Data Source – Part IV

Inkey, September 5, 2023 855 Views

In the previous 3 blog parts, we have shown what data source we are using, Data Model View, how to connect to any data source in Power Apps, how to read data from any Data source, how to create records using the Patch method for column of any data type to perform CRUD Operations.

In this blog, we will be showing:

• Initial Setup for Updating via Patch Function
• How to Update the existing records of any Data source in Power Apps using Patch function.
• How to Delete a record of any Data source from Power Apps.

Initial Setup for Updating via Patch Function

• Creating a variable on the ‘Add More Records’ button & Edit button: “var_IsNewRecord”

• Default values of the controls when we are:
  • Creating a new record
  • Editing an existing record.

• Creating a Reset variable to configure the reset property of the controls:
  • When clicking Close (Navigating back from ‘scr_Add/Edit’ to ‘scr_View’)
    We want the controls (text-inputs, date-picker, dropdowns, checkboxes, radio btns) to be reset to BLANK when the user presses Close button.
  • When clicking Add More Records (Navigation from scr_View to scr_Add/Edit)
  • When Edit button is clicked. (Navigation from ‘scr_View’ to ‘scr_Add/Edit’). When Edit icon is clicked, we want the user to be navigated to ‘scr_Add/Edit’ plus we want all the controls to be pre-filled with its respective values. So, where we are not resetting the controls to BLANK, while entered in Editing Mode.

• Check the navigation using Close Button, Add New Records, Edit button, and check whether or not the Reset property of the controls is working expectedly.

Updating Existing Records in any Data Source from Canvas Apps:

Create a new button, UPDATE Button, and on its OnSelect Property, add this code mentioned a few lines below. Here, I would like the reader to Compare this below piece of code with the Compiled Save Code.
Create a new button in the screen “scr_Add/Edit” and rename it to “btn_Update” .

Copy-Paste the below code in the “btn_Update”

//------------------ Logged In User (mail, displayName) ---------------------------
Set(gv_loggedInUserEmail,Office365Users.MyProfileV2().userPrincipalName); //storing Email of the user in this variable.
Set(gv_loggedInUserDisplayName,Office365Users.MyProfileV2().displayName); //storing DisplayName of the user in this variable.
 
ForAll(
    SortByColumns('cmb_LikesToPlayWhichGames?'.SelectedItems,"Sport"),
    Collect(
        col_SelectedGames,
         {
                    '@odata.type': "#Microsoft.Azure.Connectors.SharePoint.SPListExpandedReference",
                    Id: ThisRecord.ID,
                    Value: ThisRecord.Name
        }
    )
);//we are making this collection to save the "Like To Play Which Games?" combo box of LookUp type. And, first we pass this collection in Patch code below and then we are clearing this collection at the end of this code.
If(
    var_IsNewRecord= true,
    Patch(
    list_PatchingFromPowerApps, 
    {
        Name: txtInp_Name.Text, //single line of text
        Age: Value(txtInp_Age.Text), //number
        Description: txtInp_Description.Text, //multiple lines of text
        IsPass: If(radio_IsPass.Selected.Value = "Yes" , true, false), //boolean
        'Date of Birth':datepkr_DateOfBirth.SelectedDate, //date
        Teacher:
         {
                Claims: "i:0#.f|membership|" & Lower(gv_loggedInUserEmail),
                Department: "",
                DisplayName: gv_loggedInUserDisplayName,
                Email: gv_loggedInUserEmail,
                JobTitle: "",
                Picture: ""
        }, //person type column
        Grade: dd_Grade.Selected, //Choice column (dropdown/radiobtn)
        'Favourite Book Genre':cmb_FavouriteBookGenre.SelectedItems, //Choice column (checkbox multiselect)
        'Favourite Subject': {
                '@odata.type': "#Microsoft.Azure.Connectors.SharePoint.SPListExpandedReference",
                Id: dd_Subject.Selected.ID,
                Value: dd_Subject.Selected.Name
            },//Lookup column from a different list from same SharePoint Site. Can hold single value.
         'Likes To PLay Which Games?':col_SelectedGames, //Lookup column from same SharePoint Site. Can hold multiple values.
         Fees:Value(txtInp_Fees.Text), //Currency
         'Favourite Website':txtInp_FavouriteWebsite.Text //HyperLink
    }
),
   Patch(
        list_PatchingFromPowerApps,
        LookUp(
                list_PatchingFromPowerApps,
                ID = gal_ViewOnlyRecords.Selected.ID
            ),    
        {
        Name: txtInp_Name.Text, //single line of text
        Age: Value(txtInp_Age.Text), //number
        Description: txtInp_Description.Text, //multiple lines of text
        IsPass: If(radio_IsPass.Selected.Value = "Yes" , true, false), //boolean
        'Date of Birth':datepkr_DateOfBirth.SelectedDate, //date
        Teacher:
         {
                Claims: "i:0#.f|membership|" & Lower(gv_loggedInUserEmail),
                Department: "",
                DisplayName: gv_loggedInUserDisplayName,
                Email: gv_loggedInUserEmail,
                JobTitle: "",
                Picture: ""
        }, //person type column
        Grade: dd_Grade.Selected, //Choice column (dropdown/radiobtn)
        'Favourite Book Genre':cmb_FavouriteBookGenre.SelectedItems, //Choice column (checkbox multiselect)
        'Favourite Subject': {
                '@odata.type': "#Microsoft.Azure.Connectors.SharePoint.SPListExpandedReference",
                Id: dd_Subject.Selected.ID,
                Value: dd_Subject.Selected.Name
            },//Lookup column from a different list from same SharePoint Site. Can hold single value.
         'Likes To PLay Which Games?':col_SelectedGames, //Lookup column from same SharePoint Site. Can hold multiple values.
         Fees:Value(txtInp_Fees.Text), //Currency
         'Favourite Website':txtInp_FavouriteWebsite.Text //HyperLink
    }
   )   
);
 
Clear(col_SelectedGames);
Navigate(scr_View);
 
/*
If(
    Creating New Record,
    Do as shown in the Save Code, 
    LookUp the existing record on Primary Key, here "ID" which is managed by SharePoint and UPDATE (same syntax as Save Code) that Record.
)
*/

Explanation of the UPDATE code:
When we are Editing a record, that means on the On-Select Event of the Record in “scr_View”, firstly we are setting the value of the Variable “var_IsNewRecord” to false, and then we are navigated to the “scr_Add/Edit”.

Let us take an example. Suppose we want to Edit the first record as shown in the image below.

Now, press Update button on this screen.

On pressing Update button, the following things happens:

• The global variables gv_XX gets sets to its respective values and collection ’col_SelectedGames’ is created.
• Inside the IF condition, “var_IsNewRecord” is validated, whose value is ”false” and it goes to the ELSE parameter and runs the code mentioned in the ELSE part.
• In the ELSE part:
  • We’re comparing the selected record of Gallery of whom you clicked Edit icon of with all the records present in the “list_PatchingFromPowerApps” based upon ID column.
  • Updating that selected record values based upon the selected field values in their respective controls of “scr_Add/Edit”

Same explanation through code:

Delete a record of any Data Source from Power Apps.

Remove(list_PatchingFromPowerApps, ThisItem) //Deleting this record from the DataSource that is “list_PatchingFromPowerApps”.

CONCLUSION:

In this blog we learnt how to Update and Delete the records of any data source using Power Apps as a part of CRUD operations.
Firstly, at the time of updating/editing what all things should be kept in mind i.e., setting up the screens(regarding the event of navigating to and from View screen and Edit Screen, we must reset the controls appropriately, whether to be prefilled with existing values at the time of editing and to be reset to blank at the time of navigating back to View Screen).
Secondly, we learnt how to write the Update code to update the records and asked the learner to compare the piece of code with the Save Button Code, which we demonstrated in the Blog Part III of this 5-part Blog series.
Lastly, the easiest of all – how to delete a record from any data source using Power Apps.

ATM Inspection PowerApp to ease ATM inspection and report generation process.
https://www.inkeysolutions.com/microsoft-power-platform/power-app/atm-inspection

Insert data into Many-to-Many relationship in Dynamics CRM very easily & quickly, using the Drag and drop listbox.
http://www.inkeysolutions.com/what-we-do/dynamicscrmaddons/drag-and-drop-listbox

Comply your Lead, Contact, and User entities of D365 CRM with GDPR compliance using the GDPR add-on.
https://www.inkeysolutions.com/microsoft-dynamics-365/dynamicscrmaddons/gdpr

Create a personal / system view in Dynamics CRM with all the fields on the form/s which you select for a particular entity using the View Creator.
http://www.inkeysolutions.com/what-we-do/dynamicscrmaddons/view-creator