Connecting tables within the app

[CharlesBrewer]

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?

[Drrickryp]

@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
   )
)

[CharlesBrewer]

@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?

[Drrickryp]

@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.

[CharlesBrewer]

@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?

[Drrickryp]

@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.

[CharlesBrewer]

@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 😞
 

[Drrickryp]

@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)
    )
  
)

[CharlesBrewer]

@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

[Drrickryp]

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