Power Apps Excel-Style Editable Table – Part 2

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");

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

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)

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}))
);

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"]

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());

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());

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

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

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)

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)

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()));

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()));

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

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.

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.