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:

General Questions

Connecting to Data

All responses (5)

Answers (0)

WarrenBelz 146,601 Most Valuable Professional on at

Like (0)

Report

Add Columns from another SharePoint List to existing collection

Exactly what type of field is 'Degree type'
Chris2131 6 on at

Like (1)

Report
Add Columns from another SharePoint List to existing collection

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', " " ) )
WarrenBelz 146,601 Most Valuable Professional on at

Like (1)

Report

Add Columns from another SharePoint List to existing collection

Updated rather than reposting - try that.
Chris2131 6 on at

Like (1)

Report

Add Columns from another SharePoint List to existing collection

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.
WarrenBelz 146,601 Most Valuable Professional on at

Like (1)

Report
Add Columns from another SharePoint List to existing collection

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