adding column from another list

[Krishna_R]

Hi Reader,

I'm having two lists as following

Students List:

Classes List:

I'm trying to add classname into the gallery like below. but I'm getting an error while referencing to the thisitem.ClassName

Switch(vSort.vCol,
"Title",SortByColumns(Filter(Students,StartsWith(Title,Ip_Search.Text)),"Title",If(vSort.vDir=Descending,Ascending,Descending)),
"ClassName",SortByColumns(Filter(AddColumns(Students,"ClassName",LookUp(Classes,ID = Students[@'Class ID']).Title),StartsWith(Title,Ip_Search.Text)),"ClassName",If(vSort.vDir=Descending,Ascending,Descending)))

I'm not sure what I'm doing wrong here. any thoughts ?

[joe_hannes_col]

Hello @Krishna_R,

I think this might be because you are using the AddColumns function in a gallery control. This additional column is not persistently stored into your database.

To be able to reference it within the gallery, you would have to use the AddColumns function together with Collect or ClearCollect, and set the new collection as the source of your gallery. Then you should be able to reference ThisItem.ClassName within your gallery.

You can find more info here: https://docs.microsoft.com/en-us/powerapps/maker/canvas-apps/functions/function-table-shaping#step-b...

[Krishna_R]

Hi @joe_hannes_col, thanks for the info.

so does this mean on screen visible I've to collect all the DB data to a collection and need to perform search & sort on collection ?

[joe_hannes_col]

Hello @Krishna_R,

Yes, that should work. If your gallery only shows a defined subset of the data, you could also filter the data you are loading into the collection. 

[Krishna_R]

@joe_hannes_col considering data load it will be huge and more than 2000 records. will there be any workaround ?

[joe_hannes_col]

Hello @Krishna_R,

If you want to display the class name in your gallery, you could also do the following:

• Add a label to your gallery 
• Set its text property to:

LookUp(Classes,ID = ThisItem.'Class ID').Title)​

[Krishna_R]

@joe_hannes_col thanks for this. this might help . but it might be difficult to sort & search ! and will it impact performance as we're using lookup inside a gallery ?

[joe_hannes_col]

If I understood correctly, you are using the formula from your first post as the source of your gallery, right? Adding the lookup field to your gallery would not require you to make changes to the gallery source.

So if sorting is working right now, adding the lookup field would not impact it.

Regarding performance: Yes, any additional call to a data source is going to have an impact on performance. I would not expect a huge negative impact, but if you feel that your app is too sluggish: you could create a collection from your classes table, and then reference this collection in your gallery source and in the lookup formula within your gallery. This should speed things up.

If this is possible, it would be best to create this collection only once, e.g. using the app's OnStart property.