Solved: Re: Filter two forms using one Gallery

crjohnson82 · ‎07-01-2019

I am making a repeating table form. When an item is entered, it creates and entry in the master list and multiple entrying in the detail list.

I would like to make another screen that shows a gallery ( MasterGallery) with two edit forms, that displays the editable items when MasterID = ID

I was able to display Gallery to Form1 using MasterGallery.Selected, but I can't figure out a way to connect Form1 to Form2.

Please Help....

v-xida-msft · ‎07-02-2019

Hi @crjohnson82 ,

Do you want to display multiple Items from your second table within the Form2 (Edit form)?

If you want to display multiple Items from your second table within the Form2 (Edit form), I afraid that there is no way to achieve your needs in PowerApps currently.

The Edit form control in PowerApps could only be used to display single one record within your data source, it could not be used to display multiple items.

As an alternative solution, you could consider use a Gallery control to achieve your needs instead of the Form2. On your side, you could add a another Gallery within your screen, and configure it as Editable table.

I have made a test on my side, please take a try with the following workaround:

Add another Gallery within your app (instead of the Form2), then configure it as a Editable table. The screenshot as below:

Set the Items property of the another Gallery to following:

Filter(secondDataTable, ID = Gallery.Selected.ID)

Add appropriate controls (e.g. Text Input controls, DatePicker, Toggle, ...) within above Gallery to list the column values in your second Data Table.

Add a "Check" icon within your Gallery, set the OnSelect property to following:

Patch(
       secondDataTable, 
       ThisItem, 
       {
          Column1: TextInput1.Text, 
          Column2: TextInput2.Text, 
          Column3: TextInput3.Text,
          ...
       }
)

Add a "Remove" icon within your Gallery, set the OnSelect property to following:

Remove(secondDataTable, ThisItem)

The sample app, please check the attached .msapp file.

More details about configuring a Gallery as a Editable table, please check the following video:

https://www.youtube.com/watch?v=O2WqhRTUhyw

Please consider take a try with above solution, then check if the issue is solved.

Best regards,

Community Support Team _ Kris Dai
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

jhall · ‎07-01-2019

I'm slightly confused on what you're doing because you stated you're creating a MasterItem record and then multiple DetailItem records. So 3 records in 2 tables? One Master and two Detail?

The easy answer is that your first Form is linked because it is directly tied to your .Selected property (which I'm assuming is actually your MasterItem record).

What I'm assuming you're looking for is a way to retrieve the record you want to edit from the Detail table(s).

In that case, you need to probably take as your Form's DataSource similar to First(Filter(yourDetailTable, yourID=MasterGallery.Selected.ID)). You want to get the first record from your Detail table and link that to your Form and open it as an EditForm(). Similar scenario for your 2nd (assuming I was correct in my first paragraph) by doing a Last() vs. a First().

Does that fit your scenario or am I confused? 😄

crjohnson82 · ‎07-01-2019

@jhall

On Screen:

MasterGallery

MasterList (Form1) -

DetailList (Form2) -

1) MasterGallery to Form1

2) Form1 to Form2 ( show items when MasterID = ID)

When I click Item in MasterGallery, I want Form1 and Form 2 to shows items when MasterID = ID

Does that make sense?

jhall · ‎07-01-2019

I think I get it, but just to be 100% clear. The MasterList is showing the fields/record from the same source as your Gallery, correct? So that one of course works fine if you link the Form1 source to be the .Selected property of the Gallery. The second one is the one where you actually need to query/filter to get the record you want from a different DataSource/Table, correct?

If that's correct, then the method I explained above will work for Form2. You just need to set the DataSource to point at your secondary Table and the Item to be similar to: First(Filter(secondDataTable,ID=Gallery.Selected.ID)).

From innermost function to outer, the ID is just whatever your key matching ID is that I assume you are writing to your Master/Detail tables at creation. The secondDataTable is just the name of your other list (e.g. SharePoint) where you created the item Detail record. The reason we need to do a First() function is because PowerApps returns a Collection from the Filter() function. If there's always (at most) one Detail record, then this will work fine.

If you're ever going to have a scenario where the Details record wasn't created before we get to this screen, then you'll have to do an if() statement somewhere along the line (e.g. OnVisible) to create this record first or to change the settings on your form to create a record vs. editing one (e.g. NewForm() vs. EditForm()).

crjohnson82 · ‎07-01-2019

First(Filter(secondDataTable,ID=Gallery.Selected.ID)). WORKED.. Thanks alot..

But it only shows one item...

In picture, MASTERID:36 has 2 items. When I click on the Gallery Item, ID: 36, I want to see both entries in Form 2.
(if its 10 items, I need to display all of them)....

Hope this makes sence.

jhall · ‎07-01-2019

Yes it does. So, you have some UX choices to make here.

Are you thinking you want to edit the table of Detail items like you have them (e. g. like an Excel table)? So that the person kind of goes along the row/column to edit both simultaneously? Or do you want them to select which detail item they want to view/edit (e.g. Dropdown) which would then be opened in the Form alone?

crjohnson82 · ‎07-01-2019

yes. I would like to for them to edit the table of Detail items like I have them (e. g. like an Excel table

jhall · ‎07-01-2019

The issue will be that there isn't a default control that does this (to my knowledge). A DataTable control will give you what you're looking for out of the box per "display" but you cannot edit anything. You'd need to select one and then hit "edit" on a button or other object to then open a form (if you used a DataTable control).

You could create Forms for each entry, but you'd need to have these defined and listed up the max # of Forms you think you'd need (X entries). Then you'd align the Forms to look like an Excel sheet (e.g. remove labels/errors/required, shrink fields, line them up in a single row, etc.).

Alternatively, you could create a "pseudo-form" completely from scratch that looks like an Excel sheet and is infinitely scrollable (doesn't matter how many rows exist) using a Gallery Control. To do this:

Create a new blank Gallery and link it to the same Filter I had above but w/o the First() function.
Select your Gallery
Insert a Text Input field.
Link the field to the first value in your List (e.g. Title)
Lather/rinse/repeat for each field using steps 2 & 3
Line up your fields all next to each other and resize your Gallery so that it looks similar to an Excel Sheet.

At that point, you again have some UX decisions to make. You can have the Gallery Text Input items update via the OnChange event (not recommended) or set a Boolean via the OnChange that warns the user they need to Save their changes and asks them to hit a save/cancel.

If they Save, then you'd take the values in each field and use the Patch() function to update them to your SharePoint list.

This is much more manual than using a Form of course, but you can control formatting more easily.

jhall · ‎07-01-2019

As you have me thinking about it, you could even do a pretty slick UI that "appeared" to be an Excel style list using a DataTable, but it would be a combo of disabling/enabling (e.g. DataTable.DisplayMode.Disabled) the DataTable and Hiding/showing a Form on top of the DataTable. Then when they're done having them click a save/cancel icon and it would submit the changes, hide the form and re-display the DataTable. You'd need to do some work on figuring out the placement of the form based upon the Row and some math on the Y location value of the Form, but it definitely could be done and would look like a truly editable DataTable control.