Connecting tables within the app

(0) Share

Report

Posted on by CharlesBrewer

697

Hello,

Let me begin by saying I am very new to Powerapps, but I'm trying to work on a specific thing to learn. So what I am trying to do is have an app of a list of assets, with data I want to display. I have a table called tblassets in an SQL, that has most of the data I want. However I have another table, tblAssetCustom, that has several custom fields that I also need to include. Both of these tables have a column for AssetID. Is there any way to get Powerapps to link the data together so I can do say, a Thisitem.customfield1 or some such, to pull the data from the tblAssetCustom?

Categories:

Building Power Apps

I have the same question (0)

All responses (14)

Answers (0)

Sort by

Drrickryp Super User 2024 Season 1 on at

Like
a
(1)

Report
Copy link

Link copied!
@CharlesBrewer

You can use AddColumns() to reshape the table by using a lookup to the second table with a disambiguation since you have the same name in both tables for AssetID . FYI I usually have the primary key of every table simply "ID" and the foreign key as the name of the primary table with ID. In your case ID as the primary key of the assets table and assetsID as the foreign key in the tblAssetCustom table.

With({tac:LookUp(tblAssets,AssetID=tblAssetCustom[@AssetID]) }, AddColumns(tblAssets,"CustomColumn1",tac.CustomColumnName1, "CustomColumn2",tac.CustomColumnName2 ) )

Was this reply helpful? Yes No
CharlesBrewer 697 on at

Like
a
(0)

Report
Copy link

Link copied!

@Drrickryp
That looks interesting and probably what I need, but two questions!
1) Where would I put that? (Sorry I am a bit new).
2) When you say "reshape" the table, it won't make any actual changes to the table itself, I hope?

Was this reply helpful? Yes No
Drrickryp Super User 2024 Season 1 on at

Like
a
(0)

Report
Copy link

Link copied!
@CharlesBrewer

Reshaping the table does not affect the underlying tables. You can use that formula as the Items property of a gallery. Then the added columns will be available for labels as ThisItem.CustomColumn1 and ThisItem.CustomColumn2

You could also use it to create a Collection by putting it in the OnSelect property of a button such as

With({tac:LookUp(tblAssets,AssetID=tblAssetCustom[@AssetID]) }, ClearCollect(cAssetsPlus, AddColumns(tblAssets,"CustomColumn1",tac.CustomColumnName1, "CustomColumn2",tac.CustomColumnName2 ) ) )

and then use cAssetsPlus as the Items property of a gallery, a datatable, a dropdown or combobox. Obiously you can change the names of the "CustomColumn" to make more sense in your application. cAssetsPlus is an arbitrary name for the collection. You can call it anything you wish.

Was this reply helpful? Yes No
CharlesBrewer 697 on at

Like
a
(0)

Report
Copy link

Link copied!
@Drrickryp
Ok so one more silly question, maybe. If the gallery Items formula, there is already this by default:
SortByColumns(Search([@'[dbo].[tblAssets]'], TextSearchBox1.Text, "AssetName","AssetUnique","BuildNumber"), "AssetName", If(SortDescending1, Descending, Ascending))
Am I deleting that, or somehow appending, and having both?

Was this reply helpful? Yes No
Drrickryp Super User 2024 Season 1 on at

Like
a
(0)

Report
Copy link

Link copied!

@CharlesBrewer

If you use a button to create a collection, you can use it in your formula by replacing [@'[dbo].[tblAssets]' with cAssetsPlus. Just make sure that you replace the syntax in my formula with the correct table names and column names from your tables.

Was this reply helpful? Yes No
CharlesBrewer 697 on at

Like
a
(0)

Report
Copy link

Link copied!
@Drrickryp
I wasn't trying a button, because that sounds like something you'd have to manually do, everytime? Or I am probably misunderstanding. So I am trying to change the Gallery Items function, I assuming I need both in there. Google said to double semi-colon to get 2, so I am trying:

SortByColumns(Search([@'[dbo].[tblAssets]'], TextSearchBox1.Text, "AssetName","AssetUnique","BuildNumber"), "AssetName", If(SortDescending1, Descending, Ascending)) ;; With({tac:LookUp([@'[dbo].[tblAssets],AssetID=tblAssetCustom[@AssetID]) }, AddColumns([@'[dbo].[tblAssets],"Custom1",tac.CustomName1, "Custom2",tac.CustomName2 ) )
But this is most definitely not working 😞

Was this reply helpful? Yes No
Drrickryp Super User 2024 Season 1 on at

Like
a
(0)

Report
Copy link

Link copied!
@CharlesBrewer

Try this as the Items property of your gallery.

With({tac:LookUp([@'[dbo].[tblAssets],AssetID=tblAssetCustom[@AssetID]), //tac is lookup alias _assets: AddColumns([@'[dbo].[tblAssets],"Custom1",tac.CustomName1, //_assets reshapes "Custom2",tac.CustomName2 //the table adding columns }, // now using reshaped table with new columns in your current formula SortByColumns( // you can even search the new columns if you wish. Search( _assets, TextSearchBox1.Text,"AssetName","AssetUnique","BuildNumber" ),"AssetName", If(SortDescending1, Descending, Ascending) ) )

Was this reply helpful? Yes No
CharlesBrewer 697 on at

Like
a
(0)

Report
Copy link

Link copied!

@Drrickryp
It doesn't like that, I am getting this error :

Just to double check the names, it should be correct?
And those are definitely the column names

Was this reply helpful? Yes No
Drrickryp Super User 2024 Season 1 on at

Like
a
(0)

Report
Copy link

Link copied!

Sorry I can't see what part of the formula is causing the problem.

Was this reply helpful? Yes No
CharlesBrewer 697 on at

Like
a
(0)

Report
Copy link

Link copied!

@Drrickryp
I did a count of the { } and ( ), For the parenthesis I count 6 opening "(", but only 5 closing ")" though I am unsure where the one is perhaps missing.

Was this reply helpful? Yes No