Add Columns from SharePoint List2 to Collection for SharePoint List1

[PVLove]

I hope someone can help me with this collection confusion. I have an existing collection, colFieldSeparation2, that collects information from one SharePoint list, TestOSCMainv2. I want to add additional columns to the collection from a separate SharePoint list, TestOSCIT. The existing collection, colFieldSeparation2, collects employee information and adds it to a gallery, LookupGallery. I'm collecting information for multiple employees and adding those records to the gallery with the collection. I need to add columns from a second SharePoint list to each employee record that is collected in colFieldSeparation2. I tried to use a ForAll statement with the second list, but it creates a blank row after each record. I also need to patch the records in the collection to both SharePoint lists.

Collect(
    colFieldSeparation2,
    {
        EmployeeID: locCurrentEmployee.Employee,
        EmployeeName: locCurrentEmployee.FirstName & " " & locCurrentEmployee.LastName,
        UnionNo: locCurrentEmployee.Craft & " / " & locCurrentEmployee.Class,
        JobNameNo: locCurrentEmployee.LastJob & " / " & locCurrentEmployee.JobDesc,
        HireDate: locCurrentEmployee.HireDate,
        Position: lblMultPosition.Text,
        UnionType: cbFldPosition.Selected.Value,
        CanRehire: cbFldRehire.Selected.Value,
        DSIEquipment: cbFldEquipment.Selected.Value,
        DSICreditCards: cbFldCards.Selected.Value,
        EquipmentCollected: cbFldEquipCollected.SelectedItems.Value,
        CreditCardType: cbFldCardCollected.SelectedItems.Value
    }
);

ForAll(
    TestOSCIT,
    Collect(
        colFieldSeparation2,
        {
            FormID: ID,
            EmployeeID: EmployeeID,
            EquipmentCollected: cbFldEquipCollected.SelectedItems.Value,
            ReviewVoicemail: cbFldVMAccess.Selected.Value,
            ReviewVoicemailYes: cbFldVMYes.Selected.DisplayName,
            EmailAccess: cbFldEmailAccess.Selected.Value,
            EmailAccessYes: cbFldEmailYes.Selected.DisplayName,
            FileAccess: cbFldFileAccess.Selected.Value,
            FileAccessYes: cbFldFileYes.Selected.DisplayName
        }
    )
);

Patch Statement for TestOSCMainv2
ForAll(
    colFieldSeparation2 As My,
    Patch(
        TestOSCMainv2,
        Defaults(TestOSCMainv2),
        {
            EmployeeID: My.EmployeeID,
            EmployeeName: My.EmployeeName,
            UnionNo: My.UnionNo,
            JobNameNo: My.JobNameNo,
            Position: My.Position,
            CanRehire:cbFldRehire.Selected.Value,
            EquipmentCollected: Concat(cbFldEquipCollected.SelectedItems,Value & ", "),
            CreditCardCollected: Concat(cbFldCardCollected.SelectedItems,Value & ", "),
            HireDate: dteHireDate.SelectedDate,
            NoticeType: rdoNoticeType.Selected.Value,
            SeparationDate: dteDateSeparation.SelectedDate,
            LaborMgr: cbFldLaborMgr.Selected.DisplayName,
            Superintendent:cbFldSuperintendent.Selected.DisplayName,
            Branch: rdoFldBranch.Selected.Value,
            SubmittingSeparation: rdoFldSubmitting.Selected.Value,
            SubmitterName: cbFldSubmitterName.Selected.DisplayName,
            SeparationType: rdoFldSeparationType.Selected.Value,
            SeparationTypeOther: txtFldSepTypeOther.Text,
            SeparationReason: rdoFldSeparationReason.Selected.Value,
            SeparationReasonOther: txtFldSeparationReasonOther.Text
        }
    )
);

[rzuber]

You can add columns to your collection by collecting different kinds of records to it.. but all columns with the same names have to have the same data type as the original definition of the columns collected.

The two columns I see that match are EmployeeID and EquipmentCollected columns.. Check to make sure those and any other matching columns have the same data types.

[PVLove]

@rzuber ,

Thanks for the quick reply!  I've tried to do a Collect with AddColumns and it still returns an error message.  Can you point me to the correct syntax I should be using?

Thanks!

Patti

[rzuber]

@PVLove Your syntax seems to be ok. The error indicates that there is something wrong with one of the column data types in the second Collect() statement.

What type of value is in locCurrentEmployee.Employee?

What type of value is in EmployeeID?

You can see by this image that if you try to first collect a string to a record's EmployeeID in a collection, and then try to collect a number value, you get the same error message.

Now if you collect that same record with quotes around the 1, you get:

[PVLove]

@rzuber ,

The EmployeeIDSearch, SharePoint list, is used to search and locate employee records using a Search icon with an OnSelect property of UpdateContext({locCurrentEmployee: LookUp(EmployeeIDSearch, Employee = Value(TextIDSearch.Text))}). The locCurrentEmployee.Employee is a number value in the EmployeeIDSearch list.

The EmployeeID is a number value in a gallery, AddEmployeeGallery, with the Default of a Text Input set to locCurrentEmployee.Employee.

I'm combining all of the information from the locCurrentEmployee collection and some columns from the TestOSCMainv2 SharePoint list to create the colFieldSeparation2 collection. I'm using the colFieldSeparation2 collection to capture employee records and add them to a gallery, LookupGallery.

My new requirement is to add additional columns from a third SharePoint list to the colFieldSeparation2 collection in order to include them with each employee record created and stored in the collection. I hope that makes sense.

[rzuber]

@PVLove I understand. However, when you run Defaults(datasource) it returns a record with all the defaults from that datasource. Are there columns in the data source whose names match those columns already in the collection that have a different data type from the column already defined in the collection?

Each and every column you are trying to pull into your collection must have a matching data type for any matching column that was defined in the first Collect() call.

Is this the only place you do collecting into this collection? If you remove the ForAll(), do you get an error on the first Collect() statement? If so, it's possible the error isn't in this formula... It could be from a different location.. But if you only collect to this collection in this formula, then focus on the data types for the columns in TestOSCIT.

[PVLove]

@rzuber ,

I do apologize for not completely understanding what I'm trying to do and/or say.  But I changed the second Collect statement, and it resolved the error message.  But when I add an Employee record it still adds duplicates in the gallery and then overwrites the first record when I add a second record.  Do I need to create a second collection and then merge the two to retrieve the columns I need from the TestOSCIT list?

Corrected second ForAll:
ForAll(
    TestOSCIT,
    Collect(
        colFieldSeparation2,
        {
            FormID: locCurrentEmployee.ID,
            EmployeeID: locCurrentEmployee.Employee,
            EquipmentCollected: Concat(cbFldEquipCollected.SelectedItems, Value & ", "),
            ReviewVoicemail: cbFldVMAccess.Selected.Value,
            ReviewVoicemailYes: cbFldVMYes.Selected.DisplayName,
            EmailAccess: cbFldEmailAccess.Selected.Value,
            EmailAccessYes: cbFldEmailYes.Selected.DisplayName,
            FileAccess: cbFldFileAccess.Selected.Value,
            FileAccessYes: cbFldFileYes.Selected.DisplayName
        }
    )
);

[rzuber]

@PVLove - I think I understand.. You are trying to add information to an employee record that exists in the collection, with more information located in the second location?

If that is the case, maybe this will work (I am writing this on the fly, so please check for any typos):

ForAll(
    TestOSCIT,
    With(
        {
            _existing: LookUp(colFieldSeparation2, EmployeeID = locCurrentEmployee.Employee),
            _newdata: {
                FormID: locCurrentEmployee.ID,
                EmployeeID: locCurrentEmployee.Employee,
                EquipmentCollected: Concat(cbFldEquipCollected.SelectedItems, Value & ", "),
                ReviewVoicemail: cbFldVMAccess.Selected.Value,
                ReviewVoicemailYes: cbFldVMYes.Selected.DisplayName,
                EmailAccess: cbFldEmailAccess.Selected.Value,
                EmailAccessYes: cbFldEmailYes.Selected.DisplayName,
                FileAccess: cbFldFileAccess.Selected.Value,
                FileAccessYes: cbFldFileYes.Selected.DisplayName
            }
        },
        If(
            IsBlank(_existing),
            Collect(colFieldSeparation2, _newdata),
            Patch(colFieldSeparation2, _existing, _newdata)
        )
    )
)

In the action portion of the With() statement, you might be able to get away with just:

Patch(colFieldSeparation2, _existing, _newdata)

but I didn't test that.

[PVLove]

@rzuber ,

Thank you so much for your time and patience.  But I was able to resolve the issue before I got your solution.  I added a LookUp to each new column in the collection.  Now I'm trying to patch the second list and that's not working.  Can you help me with that, or should I post a new request?

Updated Collection:
Collect(
    colFieldSeparation2,
    {
        EmployeeID: locCurrentEmployee.Employee,
        EmployeeName: locCurrentEmployee.FirstName & " " & locCurrentEmployee.LastName,
        UnionNo: locCurrentEmployee.Craft & " / " & locCurrentEmployee.Class,
        JobNameNo: locCurrentEmployee.LastJob & " / " & locCurrentEmployee.JobDesc,
        HireDate: locCurrentEmployee.HireDate,
        Position: lblMultPosition.Text,
        UnionType: cbFldPosition.Selected.Value,
        CanRehire: cbFldRehire.Selected.Value,
        DSIEquipment: cbFldEquipment.Selected.Value,
        DSICreditCards: cbFldCards.Selected.Value,
        EquipmentCollected: Concat(cbFldEquipCollected.SelectedItems, Value & ", "),
        CreditCardType: Concat(cbFldCardCollected.SelectedItems, Value & ", "),
        FormID: locCurrentEmployee.ID,
        ReviewVoicemail: LookUp(TestOSCIT, ReviewVoicemail = cbFldVMAccess.Selected.Value),
        ReviewVoicemailYes: LookUp(TestOSCIT, ReviewVoicemailYes = cbFldVMYes.Selected.DisplayName),
        EmailAccess: LookUp(TestOSCIT, EmailAccess = cbFldEmailAccess.Selected.Value),
        EmailAccessYess: LookUp(TestOSCIT, EmailAccessYes = cbFldEmailYes.Selected.DisplayName),
        FileAccess: LookUp(TestOSCIT, FileAccess = cbFldFileAccess.Selected.Value),
        FileAccessYes: LookUp(TestOSCIT, FileAccessYes = cbFldFileYes.Selected.DisplayName)
    }
);

[rzuber]

@PVLove Is the second list patching the columns to new records? What is the issue?