Power Apps Excel-Style Editable Table - Part 2

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

Power Apps Excel-Style Editable Table – Part 2 Posted by - Matthew Devaney on - September 6, 2020 24 Comments

This is a tutorial series where you will learn how to build an Excel-style editable table in Power Apps. In Part 1 we learned how to display data in a table, modify the contents and save the changes back to a datasource. Now we are going to build a data-entry table to add new records and learn how to delete any unwanted records as well.

If you haven’t read Part 1 yet I would highly encourage you to read it first . Or if you’ve read it already and are ready for what comes next this is Part 2. Inventory Count App Continued

The Inventory Count App is used by employees of a home improvement store to record the actual amount of each product the business owns. Employees add new records for products when there is no existing product in the datasource. If a product is no longer sold at the store employees can delete the record entirely. Adding New Records

To add a new record the employee clicks the New icon and a data-entry table appears.

Place an ‘Add’ icon on the screen (ico_New) and a label with the text “New” (lbl_New). Then write this code in the OnSelect property of both the icon and label. It will create two blank records in the collection colUpdates with the ID numbers 1 and 2 and change the editable table to new mode .

ClearCollect ( colUpdates , { ID : 1 },{ ID : 2 }); Set ( varGalleryMode , " New "); Code language: CSS ( css )

Use this code in the Items property of the gallery to make it show records from colUpdates when in new mode.

If(varGalleryMode= "New" , colUpdates, 'Inventory Count' ) Code language: JavaScript ( javascript )

Allow the employee to write values in the text inputs: txt_ItemNumber, txt_Description, txt_Quantity and txt_Location by changing their DisplayMode property to this code. Now table will convert to new mode when the icon is clicked. If ( varGalleryMode in [ "New" , "Edit" ] , DisplayMode .Edit , DisplayMode .View ) Code language: CSS ( css )

Next, we want the employee to be able to add as many new items as they see fit. When a value is typed into any text input in the last row of the table another row will appear directly underneath it.

To add this feature put the code below into the OnChange property of all 4 text inputs: txt_ItemNumber, txt_Description, txt_Quantity and txt_Location .

With( { wLastID : Last(colUpdates).ID}, If(varGalleryMode= "New" And ThisItem.ID=wLastID, Collect(colUpdates, { ID : wLastID+ 1 })) ); Code language: JavaScript ( javascript ) Saving & Cancelling New Records

When the employee is done entering data they can click the ‘Save’ icon to write all of the new records back to the datasource at-once. Or they can click ‘Cancel’ button and not save anything at all.

To do this we will re-use the Save icon (ico_Save), the Cancel icon (ico_Cancel) and the labels beside them (lbl_Save, lbl_Cancel) from Part 1 of the tutorial.

Change the code in the Visible property of the Save/Cancel icons and labels to this instead. varGalleryMode in [ "New" , "Edit" ] Code language: CSS ( css )

The save icon has a different behaviour when the gallery is in new mode vs edit mode. Within the OnSelect property create an IF statement as shown in the code block below. Again, it will include all of the previously written code for edit mode.

// Create a collection to store updated values If (

// Do this when new entries are being created varGalleryMode= "New" ,

// Load all new entries into the collection colUpdates

ForAll( gal_EditableTable.AllItems As NewRows, Patch(colUpdates, LookUp(colUpdates, ID=NewRows.ID), { ItemNumber: NewRows.txt_ItemNumber.Text, Description: NewRows.txt_Description.Text, Quantity: Value(NewRows.txt_Quantity.Text), Location: NewRows.txt_Location.Text }) );

// Clear any ID numbers from colUpdates and delete the last row which holds no info

UpdateIf(colUpdates, true , {ID: Blank()}); Remove(colUpdates, Last(colUpdates)),

// Do this when entries are being edited varGalleryMode= "Edit" ,

// Load changed entries into the collection colUpdates

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 or changed values for existing rows

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

Clicking the save button will now insert new records into the datasource.

The Cancel icon does not need any additional code in its OnSelect property. We can rely on the code that’s already there.

// 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 )

Just as in edit mode when Cancel is clicked the records do not get saved. Deleting Records

If a product is no longer stocked by the company or a data-entry error is made the employee can delete it. Place a Trash icon on the screen (ico_Delete) and a label beside it (lbl_Delete)

Put this code in the OnSelect property of the Delete icon then click to change the table to delete mode. Set (varGalleryMode, "Delete" ); Set (varIsDeleteMode, true ); Code language: JavaScript ( javascript )

Employees will select which records to delete by clicking the trash icon beside it. Place a trash icon inside the gallery (ico_DeleteLine) as shown in the image below.

When a record is selected for deletion it becomes highlighted in red. The trash icon changes to a cancel icon so the employee can deselect the record if needed.

Use this code in the OnSelect property of ico_DeleteLine to select the record and add it to the collection colDeleteRecords to be deleted.

If(Self.Icon=Icon.Trash, Collect(colDeleteRecords, ThisItem), Remove(colDeleteRecords, ThisItem)); If(CountRows(colDeleteRecords)> 0 Or varGalleryMode= "Delete" , Set (varIsDeleteMode, true ), Set (varIsDeleteMode, false )); Code language: JavaScript ( javascript )

This code goes in the Icon property of ico_DeleteLine and controls whether the Trash or Cancel icon is shown. If ( ThisItem in colDeleteRecords , Icon .Cancel , Icon .Trash ) Code language: CSS ( css )

Input this code in the Visible property of ico_DeleteLine to make it appear when the form is in edit mode. Additional code is included to allow deletions during new mode and edit mode.

varGalleryMode in [ "Delete" , "Edit" ] Or (varGalleryMode= "New" And ThisItem.ID<Max(colUpdates,ID) And CountRows(colUpdates)> 1 ) Code language: JavaScript ( javascript )

Once records are selected for deletion employees can click the Confirm icon to remove them or the Cancel icon to return exit delete mode. Place a Check icon on the screen (ico_ConfirmDelete), a Cancel icon (ico_CancelDelete) and labels beside each.

ico_ConfirmDelete and ico_CancelDelete should only appear when the gallery is in delete mode. Use this code in the Visible property. varIsDeleteMode

When ico_ConfirmDelete is clicked all selected records will be deleted and the gallery will return to view mode. Put this code in its OnSelect property

// Delete records from datasource or collection depending on mode

If( varGalleryMode in [ "Delete" , "Edit" ], Remove( 'Inventory Count' , colDeleteRecords), Remove(colUpdates, colDeleteRecords) ); Clear(colDeleteRecords);

Set (varIsDeleteMode, false ); If(varGalleryMode= "Delete" , Set (varGalleryMode, Blank())); Code language: JavaScript ( javascript )

Try to delete a few records. The result should look like this.

Finally, use this code in the OnSelect property of ico_CancelDelete . Clear(colDeleteRecords);

Set (varIsDeleteMode, false ); If(varGalleryMode= "Delete" , Set (varGalleryMode, Blank())); Code language: JavaScript ( javascript )

Cancelling the deletion of records will return the table to view mode.

Deleting Records While in New Mode Or Edit Mode

For ease-of-use employees should also be able to delete records while in new mode and edit mode as well as delete. One additional line of code is needed to get this feature working. Change the Visible property of ico_Save , lbl_Save , ico_Cancel and lbl_Cancel to the following code. varGalleryMode in [ "New" , "Edit" ] And ! varIsDeleteMode Code language: CSS ( css )

Deleting records while in new mode or edit mode is done as shown in the image below.

Our Power Apps Excel-Style Editable Table is now completed. Employees have the ability to add, edit and delete records. 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 2 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 REMOVE function UI UX Matthew Devaney M365 Copilot Power Apps

How To Create Copilot Custom UI Widgets In Power Apps