Show records based on combo box with multiple tables

[jamesp24]

Hi

I have very limited knowledge of Power apps and I am not a developer and trying to create an app that shows account balances. I have built a database in sql through azure and have linked to that. I would like to have ann app where on teh first screen the user selects a company and the second page displays the balances for that company.

It has four tables:

CurrencyName, field - CurrencyName (primary key)

CustomerName, fields - CustomerID (primary key), CustomerName

Account, fields- AccountID (primary key), AccountName, CurrencyName (foreign key), CustomerID (foreign key)

Account Balance, fields, BalanceID (primary key), Balance Amount, AccountID (foreign key)

The first page of the app I have created and it selects the CustomerID (from selecting CustomerNamer)

The second page of the app I have created so it lists all Account Balances, I now wish to restrict this to only display the balances for the CompanyID selected on screen one. Clearly the CompanyID is not in the AccountBalance table but is indirectly there through the AccountID.

How can I do this please?

Many thanks for help

[mdevaney]

@jamesp24 
I find it kind of strange that a customer can have more than one account...

Anyways, I assume the 1st page showing customers has a gallery control where you select the customer from.  Put this code in the OnSelect property of the gallery

// store the Accounts IDs for the selected customer in a variable
Set(varAccountIDs, Filter(Account, CustomerID = ThisItem.CustomerID);

// change to screen 2
Navigate(Screen2);

Then, I also assume your 2nd screen has a gallery showing transactions.  Put this code in the Items property of the Transactions gallery

Filter(Transactions, AccountID in varAccountIDs);

Note: you seem like you already have good coding skills even though you are new at PowerApps

---
Please click "Accept as Solution" if my post answered your question so that others may find it more quickly. If you found this post helpful consider giving it a "Thumbs Up."

[jamesp24]

Thank you very much for the quick reply @mdevaney . I have started trying this but getting an invalid argument type error.

I am using a Dropdown rather than gallery if that makes a difference and that dropdown is pointed to the Customer Table. I also don't see ThisItem as an option pop up when creating the formula, although ThisRecord does, but not sure if thats correct (I did try and still an error)?

[jamesp24]

Hi, I got this to work without an error but its still not working overall:

Set(TestVar1, Filter('[dbo].[tblAccounts]',CustomerID=Dropdown1.Selected.CustomerID))

However I don't think its setting a correct variable, I wonder if its due to the fact that its giving multiple records back?

I tried setting a textbox to the TestVar1 value and it has an error. I wonder also how does it know to spit out AccountID?

The other part of the formula Filter('[dbo].[tblAccountBalance]',AccountID in TestVar1) seems to work as I tested that using a input box.

Thanks for help