PowerApps Query Inner Join issue

(0) Share

Report

Posted on by Wilder1626

Hi everyone.

I have a DataTable1 already connected to Oracle Table using below code in Items:

'[QA].[Location]'

The problem i have is that i need to do an Inner Join with another table , cause i'm missing some columns.

In another Table called "Addr_t", I have the address components.

If i write this down in Oracle, it's as simple as:

Select a.Id, a.Name, b.Address, b.City, b,Province, b.PostalCode, b.Country 
From Location a 
Inner Join Addr_t b ON b.Addr_id = a.Addr_id And a.Id = '123456'

Now i'm trying to transpose that into PowerApps but without any good results by doing:

AddColumns(
 '[QA].[Location]',
 "Address", City","Province","PostalCode","Country",
 LookUp(
 '[QA].[Addr_t]',
 Addr_t = '[QA].[Location]'[@Addr_id] And '[QA].[Location]'[@Id] = "123456"
 ).City, Province, PostalCode,Country
)

What exactly am I doing wrong? How can i make this wotk?

Thank you for your help

Categories:

Building Power Apps

I have the same question (0)

All responses (3)

Answers (0)

Sort by

mdevaney 29,991 Moderator on at

Like (0)

Report
Copy link

Link copied!

@Wilder1626

I've got a great pattern for a RIGHT JOIN which you could use and then FILTER out any blanks to effectively create an INNER JOIN.

JOIN All Columns From Another Collection:

https://matthewdevaney.com/powerapps-collections-cookbook/join-all-columns-from-another-collection/

---
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
Wilder1626 89 on at

Like (0)

Report
Copy link

Link copied!
Thanks for your response. I'm new on Powerapps, so i may be slow to understand how to adjust it for my need.

I've got a hard time to understand your Solution code:
//Create two collections ClearCollect(myProduct4, {ProductID: "1000", 'ProductName': "Jacket", UnitPrice: 70}, {ProductID: "1001", 'ProductName': "T-Shirt", UnitPrice: 30}, {ProductID: "1002", 'ProductName': "Baseball Cap", UnitPrice: 20} ); ClearCollect(mySales4, {ProductID: "1001", QuantitySold: 4}, {ProductID: "1002", QuantitySold: 5}, {ProductID: "1001", QuantitySold: 7}, {ProductID: "1000", QuantitySold: 1}, {ProductID: "1000", QuantitySold: 8} ); //Add all columns from another table code ClearCollect(mySolution4, Ungroup( DropColumns( AddColumns( mySales4, "myGroupedColumn", Filter( myProduct4, ProductID=mySales4[@ProductID] ) ), "ProductID" ), "myGroupedColumn" ) );

Was this reply helpful? Yes No
Wilder1626 89 on at

Like (0)

Report
Copy link

Link copied!
OK. After a couple of days, i was able to test a few things but still not working correctly. I'm only missing the LocationID value. Kind of weird:

ShowColumns( AddColumns('[QA].[ADDR_T]', "LocationID", LookUp('[QA].[Location]',ADDR_ID ='[QA].[ADDR_T]'[@ADDR_ID])), "LocationID","Address","City","Province","PostalCode","Country")

Can you see what would be the problem? What i need to say is that i have a LocationID in both tables, but i want to have the one from the Location table. Not the one from the address table. It also say in the data field: LocationID(Custom) / Custom Card...

Was this reply helpful? Yes No