Subform or Sub-Grid in Canvas Apps

[ghdunn]

Firstly, thanks for all of the great support the community is offering in helping me up the curve of Power Apps!

I am trying to do the simplest of things and I am sorry if I am just not quite getting it.

I want a form and sub/form to View/Edit two lists in SharePoint, which have a 'relationship' of a lookup.

In simplest terms:

Account

a

b

Contact,Account (looked up from Account)

x,a

y,a

z,b 

In Microsoft Access I have a very simple way of including a Subform into a Form to access.  In Model Driven Apps this seems to be similarly easy...to add a Sub-grid.

However in Canvas Apps this seems not to be quite so straightforward. 

I have looked at this solution, and I am not sure it fully resolves explains how to do this, or not in a way I understand.

https://powerusers.microsoft.com/t5/Building-Power-Apps/PowerApp-Creating-a-subForm-in-a-form/m-p/36...

I have watched these videos which talks about Repeating Tables.  I did emulate Shane's solution, but it just seems to elaborate given how easier the other solutions are. Is it really this involved?

https://www.youtube.com/watch?v=xgznk4XlPCo&list=PLCGGtLsUjhm2bonhBZuEhZU72QkFjOpc6&index=36&t=0s

If I google 'sub-grids' then I get lots of references to Model Driven Apps, but nothing simple/elegant for Canvas.

Can anyone point me to a simple solution that would allow this very simple form/sub-form:

[PowerAddict]

Hi,

Let's say you have 2 galleries - one for accounts and one for contacts.

Items property of the Accounts gallery: Accounts

Items property of the Contacts gallery:
Filter(Contacts, Account.AccountID = Gallery_Accounts.Selected.AccountID)

This should give you what you need. If you dont want to have a gallery for accounts and want a dropdown or a combo box instead, you can have a dropdown or a combo box with the Itema proeprry set to Accounts (make sure you choose Account Name as the display value) and then set the Items property of the Contacts gallery to:
Filter(Contacts, Account.AccountID = Dropdown_Accounts.Selected.AccountID)

Let me know if this works.
---
If you like this reply, please give kudos. And if this solves your problem, please accept this reply as the solution. Thanks!

Hardit Bhatia
https://thepoweraddict.com

[Pstork1]

The easiest way I've found to do this in a canvas app is to not actually put the sub-grid in the form itself.  Just add a Form to the screen and then add a gallery.  Set the Items property of the form equal to the account that you have selected to display. Then set the gallery items property equal to the Contacts entity filtered by the selected account displayed in the form.  When you select a different account in the form it will automatically retrieve all the contact records that match that account.

[ghdunn]

@PowerAddict

Hardit..

This seems very simple but I can't quite get that working.

Firstly, are you sure that it should be 

Filter(Contact,Account….     and not Filter(Contact,Contact….

Surely I am trying to filter the Contact list based upon the selection in the Account.

Secondly could you just explain to me why you are using AccountID and not AccountName.  I don't have AccountID in my intellisense drop down.

[PowerAddict]

Yes pretty sir about that as you need to filter contacts based on account.

My bad though, AccountID was just an example.

Try this:

Filter(Contact,Account.Account = Gallery_Account.Selected.Account)

OR

Filter(Contact,Account = Gallery_Account.Selected)

You can use Account Name as well, idea is to use a column that will unique values for each entry. So as long as you are sure that no 2 accounts will have the same account name, you can use it. I am using Account column in this example as that is the GUID (unique ID assigned to each record).

Let me know if this helps.

---
If you like this reply, please give kudos. And if this solves your problem, please accept this reply as the solution. Thanks!

Hardit Bhatia
https://thepoweraddict.com

[ghdunn]

@PowerAddict 

I am still obviously missing something...very sorry.

Are you SURE the conditional should say:

Account.Account = the selected thing from the Accounts gallery

why not

Contact.AccountName=the selected thing from the Accounts gallery?

I would have expected something like Filter(Contact,Contact.AccountName=Gallery1.Account.AccountName)

[PowerAddict]

It cant be
Filter(Contact,Contact.AccountName=Gallery1.Account.AccountName)

Because AccountName is not a column or field on Contact. Contact has a lookup to Account which in turn has a field or column named AccountName.

What errors are you getting? If you create 2 independent galleries, one for contact and one for account, what data do you see?

---
If you like this reply, please give kudos. And if this solves your problem, please accept this reply as the solution. Thanks!

Hardit Bhatia
https://thepoweraddict.com

[ghdunn]

@PowerAddict 

many thanks...and recognise this is getting painful for you 🙂

OK...so I have renamed my objects as follows, to avoid any confusion. (I hope!)

I have an AccountList, with one field, called AccountField.  It has 3 records:  a and b

I have a ContactList with two fields, ContactField, and AccountField which looks up to the AccountField of the AccountList

This has three records: 

a,x

b,y

a,z

My question was not so much about the field name of the conditional, but about the list name:

Given the re-naming, I would now expect the filter to be:

Filter(ContactTable,ContactTable.AccountField=AccountGallery.Selected.AccountField)

are you telling me it should be:

Filter(ContactTable,AccountTable.AccountField=AccountGallery.Selected.AccountField)

if I bring in two raw galleries as you suggest I get this:

[PowerAddict]

Not at all, my apologies if I sounded frustrated! I am literally on the other side of the planet than where I typically am (US), so may be it's the jet lag 🙂

Thanks a lot for clarifying the setup (also my bad in assuming the data source was CDS).

Given that info, this is how your formula should be (very similar to yours with one small change):

Filter(ContactTable,AccountField=AccountGallery.Selected.AccountField)

The reason I changed ContactTable.AccountField to just AccountField is that when we are telling the formula or expression to filter ContactTable, we dont need to tell it again that the column AccountField that we want to filter on belongs to the ContactTable.

Let me know if this works.

---
If you like this reply, please give kudos. And if this solves your problem, please accept this reply as the solution. Thanks!

Hardit Bhatia
https://thepoweraddict.com

[ghdunn]

I wasn't suggesting you sounded frustrated...I just wouldn't be surprised if you were.

So firstly my bad...having renamed the ContactList I then refer to it in my last post as the ContactTable...but anyway...I now have:

and the errors: