Unanswered

Add Columns from another SharePoint List to existing collection

(1) Share

Report

Posted on by Chris2131

I have three SharePoint lists that i want to make up my one collection. Two of the lists have under 200 items in it, the third has over 2000 items. I am able to combine them all OnStart of the app but the load time takes a while. I am trying to speed things up by getting the first two lists added OnStart then OnVisible of the app add in the bigger list to the collection. I get no errors for the code but when i check my collection the new column is not added onVisible.

OnStart:

ClearCollect(
    colResumeDB,
    Filter(
        SortByColumns(
            AddColumns(
                'Resume Database',
                'Employee Name',
                Last(
                    Split(
                        Name.DisplayName,
                        " "
                    )
                ).Value & ", " & First(
                    Split(
                        Name.DisplayName,
                        " "
                    )
                ).Value,
                DegreeType,
                Concat(
                    Filter(
                        'Resume Education',
                        ResumeID = 'Resume Database'[@ID]
                    ),
                    'Degree Type'.Value,
                    " "
                ),
                FieldStudyType,
                Concat(
                    Filter(
                        'Resume Education',
                        ResumeID = 'Resume Database'[@ID]
                    ),
                    'Field of Study'.Value,
                    " "
                ),
                StudyOther,
                Concat(
                    Filter(
                        'Resume Education',
                        ResumeID = 'Resume Database'[@ID]
                    ),
                    'Study Other',
                    " "
                ),
                School,
                Concat(
                    Filter(
                        'Resume Education',
                        ResumeID = 'Resume Database'[@ID]
                    ),
                    'University/School',
                    " "
                )
            ),
            "Employee Name",
            SortOrder.Ascending
        ),
        EmployeeStatus.Value = "Active"
    )
);

OnVisible:

AddColumns(
    colResumeDB,
    JobTitle,
    Concat(
        Filter(
            'Resume Work History',
            ResumeID = 23
        ),
        'Job Title',
        " "
    )
)

Any help would be appreciated. Thanks

Categories:

Building Power Apps

I have the same question (0)

All responses (5)

Answers (0)

WarrenBelz 153,084 Most Valuable Professional on at

Like (1)

Report
Hi Chris2131

A couple of suggestions here as the code you have seems to be valid. To speed this up and do it in one action,

Firstly grab the data set you want - this will greatly simplify the filter to the data source

Then add a field (it will be a Table) with the matching records from the second list

Then add all the columns from a field you already now have as well as the last field from the third list

With( { _Data1: Filter( SortByColumns( 'Resume Database', "Employee Name", SortOrder.Ascending ), EmployeeStatus.Value = "Active" ) }, With( { _Data2: AddColumns( _Data1, _Resume, Filter( 'Resume Education', ResumeID = ID ) ) }, ClearCollect( colResumeDB, AddColumns( _Data2, 'Employee Name', Last( Split( Name.DisplayName, " " ) ).Value & ", " & First( Split( Name.DisplayName, " " ) ).Value, DegreeType, Concat( _Resume.'Degree Type', Value, " " ), FieldStudyType, Concat( _Resume.'Field of Study', Value, " " ), StudyOther, Concat( _Resume.'Study Other', " " ), School, Concat( _Resume.'University/School', " " ), JobTitle, Concat( Filter( 'Resume Work History', ResumeID = 23 ), 'Job Title', " " ) ) ) )

Please click Does this answer your question if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it a Like.
MVP (Business Applications) Visit my blog Practical Power Apps Buy me a coffee

Was this reply helpful? Yes No
Chris2131 6 on at

Like (1)

Report

Hi @WarrenBelz, thank you for the help. I think this is very close. One thing I noticed is that when i am trying to Concat the choice fields I am getting an error on the Value piece.

The error goes away if i remove Value but then in the collection I see a ? mark instead of the data.

Was this reply helpful? Yes No
WarrenBelz 153,084 Most Valuable Professional on at

Like (1)

Report

Updated rather than reposting - try that.

Was this reply helpful? Yes No
Chris2131 6 on at

Like (1)

Report
No luck @WarrenBelz. Got the same error with the updated code. I tried the below as well and got back the ? as well as it saying [object Object] when previewing the collection.

DegreeType, Value( Concat( _Resume.'Degree Type', " " ) )

Was this reply helpful? Yes No
WarrenBelz 153,084 Most Valuable Professional on at

Like (0)

Report

Exactly what type of field is 'Degree type'

Was this reply helpful? Yes No