updating a collection from another collection with the same column names

[AmyF]

This seems simple but I cannot get the syntax right, I'd appreciate any help.

I have two collections (CollectionA and CollectionB) that have exactly the same column names and number of records.  The first collection has lots of columns of data and two of them match the columns in the second collection, namely, EmployeeName and EmployeeEmail.  So on the first collection the EmployeeEmail column is empty and I want to populate it on every record with the EmployeeEmail column from the second collection.  (sadly the second collection doesn't have an ID field, so I need to match on EmployeeName and that's not unique).  But, both collections are in the same order and have the same number of records, I just have to get the data from EmployeeEmail column in CollectionB into EmployeeEmail in CollectionA.

Nothing I've tried works.  All help is appreciated.

[PowerNuggets · Accepted solution]

Hi @AmyF 

Try add columns, see the example below it may help

ClearCollect(CollectionA,
{EmpName: "John Smith 1", Email: "Email1@email.com"},
{EmpName: "John Smith 2", Email: "Email2@email.com"},
{EmpName: "John Smith 3", Email: "Email3@email.com"}
);

ClearCollect(CollectionB,
{EmpName: "John Smith 1", Job: "Supervisor"},
{EmpName: "John Smith 2", Job: "Employee"},
{EmpName: "John Smith 3", Job: "Employee"}
);

ClearCollect(mySolution,
AddColumns(CollectionB,"Email",LookUp(CollectionA,EmpName=CollectionA[@EmpName],Email))
);

if this solves your issue, consider marking the post as solved

KR,

[AmyF]

Well we're almost there. 

Your example doesn't work.  (see image)

What's happening is that the primary collection ends up with the same email address of the first record of the secondary collection (which has the email addresses in it).  Your example was a bit backwards so I had to swap the collections and this is the code I came up with: 

ClearCollect(mySolution,
AddColumns(myCollection2,"Email",LookUp(myEmpXref,EmployeeName=myEmpXref[@EmployeeName],EmployeeEmail))
);

So what happens is that my main collection, myCollection2, now has the new column of "Email" with the email address from the first record in myEmpXref on all of the records in myCollection2.   It looks like it performs the lookup one time and then uses that value to populate the new Email column on myCollection2.

It's the same thing that happens if you cut/paste your entire example into an app, all your records have the first email address on them too.

[PowerNuggets · Accepted solution]

Hi @AmyF 

Yes it was just an example , hope it helped you somehow

KR,

[Brendan1 · Accepted solution]

The problem here is that you are looking up the record in myEmpXref against itself. 

This line: LookUp(myEmpXref,EmployeeName=myEmpXref[@EmployeeName] is always going to match against the first record.  It's like saying 'myEmpXref[@EmployeeName] = myEmpXref[@EmployeeName]', which will return true for every item in the myEmpXref collection, and therefore always return the first item in the collection.

I find it's easier to disambiguate collections using the As operator.  Try something like this:

ClearCollect(
mySolution,
AddColumns(
myCollection2 As m,
"Email",
LookUp(myEmpXref,EmployeeName=m.EmployeeName).EmployeeEmail
)
);

Cheers,

Brendan

[AmyF]

Thanks very much, Brendan.