PowerApps Data Entry Grid - Tab Key Issues

[ryang]

I created a data entry grid in powerapps using a gallery and within the gallery using text boxes with its On Change property set to  Patch(Products, ThisItem, { <fieldName>: TextInput.Text }). Similar to https://www.youtube.com/watch?v=O2WqhRTUhyw

This grid is 50 rows and 4 columns, and has tab index set from 1 to 200 for each "cell". If i use the tab key, i can effortlessly move from cell to cell in the correct order. However, if i enter some data in a cell and use tab after that, the tab key takes me back to the column 1 of that same row. 

I have been trouble shooting this but to no avail. I disabled the On Change property to each cell to false, and my issue was fixed, however, i could not get back the data to my collection and eventually my data source.

Anyone has experienced issues as such? Thanks in advance.

[RossB]

Hello,

Take a look at this entire post.  Let me know if it helps you out at all: https://powerusers.microsoft.com/t5/Building-Power-Apps/Tab-Order-on-Text-Input/td-p/41371

Thanks!

[ryang]

Thank you but it did not.

The 200 "Cells" each have tab index from 1 to 200 using a formula.

I can not refresh the data source because the gallery's data source is a Collection and not a managed connection.

Using Tab or Shift + Tab works fine; i can navigate my 200 cells in correct order. My problem lies when I change a value within a cell then use tab. The tab key would take me back to the start of that row.

If set the On Change property of the Cells to false, everything works fine. However, without the On Change = Patch(Collection, ThisItem, { <fieldName>: TextInput.Text }), updating the Gallery does not update the Collection.

[Mr-Dang-MSFT]

Hi @ryang ,

If I'm understanding your scenario, you have mimicked a spreadsheet experience in a gallery in Power Apps.

• Making a change to a field causes a Patch() OnChange.
• When you tab through the fields, the tab index jumps to the start.

What's happening here is that when you Patch() a value to a table that the gallery depends on, the gallery gets updated and the focus is reset to the start of the row. 

As a general principle, I would not recommend this design pattern as there are others that work more effectively and efficiently for apps. Here's some common patterns for example:

• Generic form experience: use the 3-screen experience to browse all records, select one record to view, and edit it.
• Edit button in-line: insert an edit button into the gallery. Upon clicking edit, show a save button in-line so saving changes of one record is done at the end rather than having multiple writes for each field that is changed.
• Collection: collect each record that has changed. Click a button to commit all changes at once.

Addendum: It is not necessary to change the TabIndex unless you want to override the default top-left to bottom-right order. 

[ryang]

Thank you for your response and I think you understood my scenario for the most part.

I initially had override the tab index as part of my troubleshooting.

The Patch() in the On Change, i used a Collection as my data source.

My main issue would be that "one record" is 200 data points. I had used the gallery and the Patch() in the On Change as a simplification of the form.

Then there is a button at the end of the form, that Patches the entire Collection to Azure SQL. How would you go about this?

[Mr-Dang-MSFT]

---

@ryang wrote:

My main issue would be that "one record" is 200 data points. I had used the gallery and the Patch() in the On Change as a simplification of the form.

---

I don't understand this part. Can you describe what you set up here? A diagram or screenshot will help.

[ryang]

Users need to fill out one of these every day. It goes for about 50ish rows. Ultimately I ended up putting 200+ textboxes on the screen, but there has to be a better way.

Ideally, updating a cell will update a collection, then when a submit button is used at the end of the screen, the Collection will Patch to my database.

[Mr-Dang-MSFT · Accepted solution]

Let's say you have a collection called Collection1 that populates blank rows in Gallery1 to be written and saved each day. Think of this as a placeholder.

// Create 50 empty rows in Collection1 which will pre-populate a gallery.
Clear(Collection1);
ForAll(
    [1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31,32,33,34,35,36,37,38,39,40,41,42,43,44,45,46,47,48,49,50],
    Collect(Collection1,
        {
            Group: "G" & Value,
            Value1: 0,
            Value2: 0,
            Value3: 0,
            Comment:""
        }
    )
)

For this example, the Items property of Gallery1 is set to Collection1.

Inside Gallery1 we include text input boxes whose Default property is set to Group, Value1, 2, 3, and Comment. In the OnChange property of those 5 input boxes, we can write changes to a new collection, Collection2. This is what we'll ultimately save back to SQL.

If(
    // Check if there is already a change for the given record.
    ThisItem.Group exactin Collection2.Group,

    // CASE: Update an existing change.
    Patch(Collection2,LookUp(Collection2,Group=ThisItem.Group),
        {
            Group: TextInput1.Text,
            Value1: Value(TextInput2.Text),
            Value2: Value(TextInput3.Text),
            Value3: Value(TextInput4.Text),
            Comment: TextInput5.Text
        }
    ),

    // CASE: Create a new change,
    Collect(Collection2,
        {
            Group: TextInput1.Text,
            Value1: Value(TextInput2.Text),
            Value2: Value(TextInput3.Text),
            Value3: Value(TextInput4.Text),
            Comment: TextInput5.Text
        }
    )
)

Collection1 remains untouched so that the gallery doesn't get updated and reload. Patches made to Collection2 are immediate and do not have any performance impact because you're not writing back to the data source after each revision.

Once all changes are made, you can set the OnSelect property of a submit button to one of these options:

// Option 1: If the column names of Collection2 are identical to the SQL table, you can simply use Collect.
// Sort is used since changes might not be created in order. Use an ID column otherwise.
Collect(dbo.Datasource1,Sort(Collection2,Group))

// Option 2: If the column names of Collection2 differ from the SQL table, you can rename and drop any columns first.
Collect(dbo.Datasource1,
    Sort(
        DropColumns(
            RenameColumns(Collection2,"Group","Column1"),
            "ColumnX"
        ),
        Group
    )
)

// Option 3: You can manually save each column of each record.
ForAll(
    Sort(Collection2,Group),
    Patch(dbo.Datasource1,
        Defaults(dbo.Datasource1),
        {
            Group: Group,
            Value1: Value1,
            Value2: Value2,
            Value3: Value3,
            Comment: Comment
        }
    )
)

Let me know if you're able to implement this.

[ryang]

Thank you so very much!

[Mr-Dang-MSFT]

Oh, one caveat is if a row had not been updated, it would not be in Collection2 and would not be written to the data source. So the solution I wrote assumes all rows would be updated.

Some additional logic would be needed in the event that a row would remain at 0s.