Power Apps

How to show different datasource columns in a gallery?

(0) Share

Report

Posted on by Srihari.molaka

103

Hi Experts,

I have added SQL Server as a connection to my powerApp and added two tables as data sources to my app.

In that two tables one is header table and another is line table.

I created a gallery to my powerApp. For each record 5 columns need to show. In that,

1. The journal number in header table.

2. Journal name in header table.

3.Currency code and sum of amount these two columns are from line table.

4. Account number in the line table.

Journal number is the unique id in the header table and for one journal id multiple lines in the line table.

I need help for 3 & 4 columns and need to filter the gallery based on journal number and column 4.

whether i need to use filter inside another filter or any other?

Please can anyone help me to solve this by suggest formulas.

Thanks,

with regards,

User237.

Categories:

Building Power Apps

I have the same question (0)

All responses (5)

Answers (0)

Vijay Tailor 2,961 on at

Like (0)

Report
Copy link

Link copied!

Hi @User237

as per my understanding, you need to use Join functionality.

for that, you can check the below URL , Hope this solution help you to resolve your issue.
https://powerusers.microsoft.com/t5/Building-Power-Apps/join-two-sql-tables/m-p/348783#M100124

Thanks,
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."

Was this reply helpful? Yes No
v-yutliu-msft Microsoft Employee on at

Like (0)

Report
Copy link

Link copied!

Hi @User237 ,

Could you show me the structure of the two tables?

From your description, do you have these fields in your two tables?
1)you have fields named journal number, Journal name in header table;

2)you have fields named Currency code, sum of amount, Account number in the line table.

Do you want to filter the gallery based on journal number and Account number?

Could you tell me:

1)the relationship between the two tables?

2)formula in the gallery's Items?
3)the filter rule?

Based on your description, I do not quite understand your filter rule.

Best regards,

Was this reply helpful? Yes No
agorkemt 73 on at

Like (0)

Report
Copy link

Link copied!

Hey there,

If you still looking for a solution maybe you can try 'lookup' function and in your gallery, for each control you can use this lookup function and retrieve data from sql server for each gallery control. I tried something similar to this but it was a form, not a gallery and it worked.

You can also watch this video from Shane Young, too. the syntax etc, it explains real simple;

https://youtu.be/W5Z61Q9Rehs?si=4izvqSjwI7SCXvf4

Have a nice day!

Was this reply helpful? Yes No
timl 36,774 Super User 2026 Season 1 on at

Like (0)

Report
Copy link

Link copied!
Hi User237,

An efficient way to do which would be delegable is to create a view in SQL Server that joins the header and line tables. You would then add the view to your Power App and use that as the data source for your gallery.

The SQL for the view would look like this:

CREATE VIEW JournalSummary AS SELECT h.JournalNumber, h.JournalName, l.CurrencyCode, SUM(l.Amount) AS TotalAmount, l.AccountNumber FROM HeaderTable h INNER JOIN LineTable l ON h.JournalNumber = l.JournalNumber GROUP BY h.JournalNumber, h.JournalName, l.CurrencyCode, l.AccountNumber;

Was this reply helpful? Yes No
Suggested answer

CA1105 545 Moderator on at

Like (0)

Report
Copy link

Link copied!

You can use Join in SQL servers to combine the 2 table data but also you can easily do 'Lookup' function in your add with both the sources.

Was this reply helpful? Yes No