Power Apps Excel-Style Editable Table - Part 1

Imported reference from Matthew Devaney's blog for learning purposes. Original: https://www.matthewdevaney.com/power-apps-excel-style-editable-table-part-1/. Author: Matthew Devaney.

Power Apps Excel-Style Editable Table – Part 1 Posted by - Matthew Devaney on - August 30, 2020 40 Comments

Excel is the most popular business app in the entire world. Everyone who works in an office understands what the app is and knows the basics of how to use it. Including an editable table that is designed similar to an Excel spreadsheet is an excellent addition to many Power Apps. It provides users an efficient way of doing data entry and makes them feel comfortable with a concept they already know. Unfortunately, Power Apps does not have this feature built-in.

In this two-part series I will show you how to build an Excel-style editable table in PowerApps. Part 1 will show you how to setup the table, edit values and save changes back to a datasource. Part 2 will focus on how to add new records and delete existing records. Inventory Count App

The Inventory Count App is used by employees of a home improvement store to record the actual amount of each product the business owns. An employee clicks on the New button to record information about several items into a table and clicks Save once finished. If a correction is needed the item can edited or deleted from the table.

Create a new SharePoint list called ‘ Inventory Count’ with 4 columns: ItemNumber (text), Description (text), Quantity (number) and Location (text). ItemNumber Description Quantity Location ELEC-1000 Light Switch 25 Storefront ELEC-1001 Circuit Breaker 20 Amp 17 Storefront PLUM-1002 Copper Pipe 10-1/4 20 Storefront PLUM-1023 Kitchen Faucet Silver 5 Storefront LUMB-2001 Plywood 8×4 43 Lumber Yard LUMB-2094 Fence Post 8ft 100 Lumber Yard

Open Power Apps and create a new Canvas App From Blank called Inventory Count App. Insert a gallery called gal_EditableTable onto the canvas with the ‘Inventory Count’ SharePoint List as the datasource.

Then place 4 text input controls inside the gallery named txt_ItemNumber, txt_Description, txt_Quantity and txt_Location and use this code in each of their Default properties respectively: ThisItem.ItemNumber , ThisItem.Description , ThisItem.Quantity , ThisItem.Location.

The app will now look like the image shown below.

Now we will work on styling the gallery to make it appear like an editable table. Change gal _ EditableTable to have these properties TemplatePadding : 0 TemplateSize : 40 Code language: HTTP ( http )

Then change txt_ItemNumber, txt_Description, txt_Quantity and txt_Location to reflect these properties. BorderColor : DarkGray BorderThickness : 1 Color : Black Fill : White FocusedBorderColor : Self.BorderColor

FocusedBorderThickness : Self.BorderThickness HoverBorderColor : Self.BorderColor HoverColor : Self.Color HoverFill : RGBA(186, 202, 226, 1) PressedBorderColor : Self.BorderColor PressedColor : Self.Color PressedFill : Self.Fill RadiusBottomLeft : 0 RadiusBottomRight : 0 RadiusTopLeft : 0 RadiusTopRight : 0 Code language: HTTP ( http )

Once the style changes are completed the gallery now looks like an editable table.

As a final touch we will add a label above the gallery with the column header names. Set the label’s Fill property to a color that matches your app’s theme and make the FontWeight bold. Our gallery now looks like an Excel spreadsheet. Detecting Edited Rows

Employees require the ability to edit values in the table and save them back to the SharePoint list. To ensure the best app-performance possible we only want to update records that were changed. We will track this by inserting a toggle called tog_isChanged on the right-side of the gallery.

Put this code in the Default property of the toggle to control its behaviour. ThisItem .ItemNumber <> txt_ItemNumber .Text Or ThisItem .Description <> txt_Description .Text Or ThisItem .Quantity <> Value ( txt_Quantity .Text ) Or ThisItem .Location <> txt_Location .Text Code language: CSS ( css )

Try changing a few values in the table to understand how the toggle works. When the toggle is in the ‘On’ position it means the row was modified. The ‘Off’ position tells us that the row remains unedited.

Once we are satisfied the toggle is working correctly we can hide it by changing the Visible property to False . The employee does not need to see the toggle. Editing The Table and Saving Changes

Initially, the table should be in a view-only state. The employee will click the Edit button to make the gallery editable. Place an ‘Edit’ icon and a label with the word “Edit” above the gallery.

Put this code in the following properties of both ico_Edit and lbl_Edit OnSelect: Set (varGalleryMode, "Edit" ) Visible : varGalleryMode=Blank() Code language: JavaScript ( javascript )

Then use this code in the DisplayMode property of the Text Input controls: txt_ItemNumber, txt_Description, txt_Quantity and txt_Location .

If(varGalleryMode= "Edit" , DisplayMode.Edit,DisplayMode.View) Code language: JavaScript ( javascript )

Now when we click on the Edit button the gallery changes from “View” mode to “Edit” mode.

Next we’ll add 2 more pairs of icon and labels for “Save” and “Cancel”.

They should only appear when the gallery is in “Edit” mode. Put this code in the Visible property of the icons and labels. varGalleryMode = "Edit" Code language: JavaScript ( javascript )

When the employee clicks “Save” any changes made should be written back to the SharePoint List. We will create a collection called colUpdates to store the modified records. The first step is to define the collection schema by placing this code in the OnStart property of the app. ClearCollect ( colUpdates ,{

ID : 1 , ItemNumber: "A" , Description: "A" , Quantity: 1 , Location: "A" }); Clear ( colUpdates ); Code language: CSS ( css )

Then insert this code into the OnSelect property of the Save icon and its associated label. It will loop through the gallery looking for any changed records, store them in the colUpdates collection and then patch multiple rows to SharePoint simultaneously to achieve the best performance.

// Create a collection to store updated values

ForAll( Filter( gal_EditableTable.AllItems, tog_isChanged.Value ) As ChangedRows, Patch(colUpdates, Defaults(colUpdates), { ID : ChangedRows.ID,

ItemNumber : ChangedRows.txt_ItemNumber.Text,

Description : ChangedRows.txt_Description.Text,

Quantity : Value(ChangedRows.txt_Quantity.Text),

Location : ChangedRows.txt_Location.Text }) ); // Update SharePoint with new values

Patch( 'Inventory Count' , colUpdates); Clear(colUpdates); // Return gallery to view mode Set (varGalleryMode, Blank()); Code language: JavaScript ( javascript )

Once the SharePoint list is updated the gallery returns to “View Only” mode. Cancelling Changes

Finally, we want to allow employees to return to “View” mode without saving any of the changes they made. Put this code in the OnSelect property of the cancel icon and label.

// Reset text inputs to erase any edits made

Set (varResetTextInputs, true ); Set (varResetTextInputs, false ); // Clear any changes from colUpdates Clear(colUpdates); // Return gallery to view mode Set (varGalleryMode, Blank()); Code language: JavaScript ( javascript )

Then write this code in the Reset property of txt_ItemNumber, txt_Description, txt_Quantity and txt_Location . varResetTextInputs

When the cancel icon is clicked all records are reset and show their original values. Want To Read Part 2 Of This Series?

My next article will show you how to add new records and delete existing records using an editable table. Click here to read Power Apps Excel-Style Editable Table – Part 2 . Did You Enjoy This Article? 😺

Subscribe to get new Copilot Studio articles sent to your inbox each week for FREE Enter your email address Sign Me Up Questions?

If you have any questions or feedback about Power Apps Excel-Style Editable Table – Part 1 please leave a message in the comments section below. You can post using your email address and are not required to create an account to join the discussion. Data Entry Excel Gallery Control PATCH function UI UX Matthew Devaney M365 Copilot Power Apps

How To Create Copilot Custom UI Widgets In Power Apps