Populating a gallery efficiently?

[zuurg]

In my app I have 2 SharePoint lists: Projects and Resources.  Lets say for the sake of simplicity, this is what the structure of each looks like:

Projects:

Project ID (Number)

Project Name (Text)

Planned Start (Date)

Planned End (date)

and a bunch of other columns that aren't related to this issue

Resources:

Project ID (Number)

Resource (Person)

The Projects to Resources relationship is One to Many on the Project ID (you can have multiple resource records that have the same Project ID.

I'm trying to show a gallery that contains lines from the Projects list when a resource is selected from another gallery (basically trying to show all the projects that the selected Resources.Resource is involved with.

The information I'm trying to show is the Project #, Project Name, and Planned End from the Projects list.

The way I have it set up now is that Selecting the resource shows a new gallery where the Items property is set to:

Filter('Resources',Resource.DisplayName=galDashboardResources.Selected.Resource.DisplayName)

and in the template for that new gallery, I have 3 labels: Project ID, Project Name, and Planned End.

The Project ID is ThisItem.'Project ID', and the Name and Planned End are both LookUps:

LookUp(Projects,'Project ID'=ThisItem.'Project ID','Project Name')

LookUp(Projects,'Project ID'=ThisItem.'Project ID','Planned End')

So basically, for every record in this new gallery it's trying to show, it's having to do 2 lookups per record to get the name and planned end for the Project ID from the Resources list, which seems terribly inefficient.  Right now there isn't a lot of data in the lists, but I'm worried that down the road, it's going to be extremely slow, especially if someone is on many projects.

Is there a more efficient way to do this?  Maybe create a collection with just the Project ID, Name, and Planned End from the Projects list and use that as the Items of the project list gallery?  

[Drrickryp]

@zuurg 

You could create a variable to contain the record for your selected project.  So for example if your projects are the items property of dropdown or combobox,, in its OnChange property Set(var,Self.Selected).  If you select the project from a gallery in its OnSelect property: Set(var, ThisItem)  You could then reference it's ID, planned end and any other value contained in that record as var.ID, var.'Project Name', etc.  There would be only a single call back when the variable was created.

[zuurg]

This is what I'm putting into the OnVisible property for the screen the gallery is on:

ClearCollect(
    colFullProjectResources,
    AddColumns(
        Resources,
        "ProjectInfo",
        LookUp(
            'Projects',
            'Project ID'='Resources'[@'Project ID']
            )
    )
);

This seems to be sort of working, the only thing I don't like is that the column that it adds to the collection is the full record instead of the individual pieces of that record that I need (the name and planned end).

I'm also concerned that eventually the Resources list is going to have a lot of records in it given that any single project can have multiple resources tied to it.  I'm guessing that to build the collection, it's going through the Resources list one record at a time and looking up the information from the Projects list?  Just wondering if there's a more efficient way of doing this.

[Drrickryp]

@zuurg 

It seems to be working now but AddColumns() is not a delegatable function and sometimes does not throw a warning. ClearCollect is not delegatable either.

[Drrickryp]

@zuurg 

It seems to be working now but AddColumns() is not a delegatable function and sometimes does not throw a warning. ClearCollect is not delegatable either.