cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
JunkoOhara
Advocate II
Advocate II

Merging 2 collections - AddColumns and UpdateIf

Hello all,

 

I have two collections, one is StaffProfile and another is temperatures recorded.

I would like to create a merged collection with all staff members with temperatures recorded.

Matching field is the Staff Number.

 

This is what i got so far, if I hard code "test" if successfully updates the collection.

I am assuming the Lookup syntax is wrong so I am not able to retrieve the data..

 

Would anybody have any idea what I am doing wrong?

 

 

ClearCollect(colStaff;Filter(StaffProfile; Location=LbLocSelText_1.Text));;
ClearCollect(colEntry;Filter(EntryProtocol; DateItemCreatedText = LbDateSelected_2.Text));;
ClearCollect(colmergedData;AddColumns(colStaff;
"TempIN";"";
"TempOUT";""
));;
UpdateIf(colmergedData; StaffNumber=Title; {
    TempIN:LookUp(colEntry;StaffNumber=Title;'Temperature on entry');
    TempOUT:"test"
    })

 

 

2020-06-25 15_27_54-Window.png

 

1 ACCEPTED SOLUTION

Accepted Solutions
v-xida-msft
Community Support
Community Support

Hi @JunkoOhara ,

Based on the UpdateIf function that you mentioned, I think there is something wrong with it. The filter columns specified within the second argument of the UpdateIf function, must be existed in the colmergedData collection already, but the "StaffNumber" column is not existed in the colmergedData collection.

 

I have made a test on my side, please consider modify your formula as below:

ClearCollect(colStaff;Filter(StaffProfile; Location=LbLocSelText_1.Text));;
ClearCollect(colEntry;Filter(EntryProtocol; DateItemCreatedText = LbDateSelected_2.Text));;
ClearCollect(     // Modify formula here
             colmergedData;
             AddColumns(
                          colStaff;
                          "TempIN"; LookUp(colEntry; StaffNumber = colStaff[@Title]; 'Temperature on entry');
                          "TempOUT"; "test"
            )
);;

Please consider try above formula without using UpdateIf function, then check if the "TempIN" column in colmergedData collection has been populated with proper value.

 

Best regards,

Community Support Team _ Kris Dai
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

6 REPLIES 6
KrishnaV
Super User III
Super User III

Hi @JunkoOhara ,

 

Try this:

ClearCollect(colStaff;Filter(StaffProfile; Location=LbLocSelText_1.Text));;
ClearCollect(colEntry;Filter(EntryProtocol; DateItemCreatedText = LbDateSelected_2.Text));;
ClearCollect(colmergedData;AddColumns(colStaff;
"TempIN";"";
"TempOUT";""
));;
UpdateIf(colmergedData; StaffNumber=Title; {
    TempIN:if(IsBlank(LookUp(colEntry;StaffNumber=Title;'Temperature on entry')),"No Value Match",LookUp(colEntry;StaffNumber=Title;'Temperature on entry'));
    TempOUT:"test"
    })

See the below article for syntax and example for UPDATEIF:
https://docs.microsoft.com/en-us/powerapps/maker/canvas-apps/functions/function-update-updateif


I hope this resolved your issue if you see any challenge/need further help please let me know I am always happy to do it for my community.

Regards,
Krishna
If this post helps you give a 👍 and if it solved your issue consider Accept it as the solution to help the other members find it more.


I hope this resolved your issue if you see any challenge/need further help please let me know I am always happy to do it for my community.

Regards,
KrishnaV
Business Applications MVP
Twitter | Linkedin | YouTube | GitHub
If this post helps you give a and if it solved your issue consider Accept it as the solution to help the other members / new members of the community.

Hi @JunkoOhara ,

 

Try this, I have tested and worked as expected:

  1. OnVisible of the screen set the below:

 

ClearCollect(collStaff,{DateItemCreated:Now(),StaffNumber:1014,Temp:36.3});
Collect(collStaff,{DateItemCreated:Now(),StaffNumber:516,Temp:36.2});
Collect(collStaff,{DateItemCreated:Now(),StaffNumber:1383,Temp:38.3});
Collect(collStaff,{DateItemCreated:Now(),StaffNumber:1002,Temp:41.3});

ClearCollect(collEntry,{Title:1002,FullName:"KrishnaV"});
Collect(collEntry,{Title:1003,FullName:"KVN"});
Collect(collEntry,{Title:1007,FullName:"ABC"});​

 

  • Now Create a button on the screen to get merged collection: onSelect set the below formula

 

ClearCollect(colMergedData,AddColumns(collStaff,"TempIN",""));
ForAll(collEntry,
UpdateIf(colMergedData,StaffNumber=Title,{TempIN:Temp}))​

 

  • Now check the collection by view -> collections->colMergedData
    MergedCollection.png
    Note: You need to add Forall for your formula my friend.


I hope this resolved your issue if you see any challenge/need further help please let me know I am always happy to do it for my community.

Regards,
Krishna
If this post helps you give a 👍 and if it solved your issue consider Accept it as the solution to help the other members find it more.


I hope this resolved your issue if you see any challenge/need further help please let me know I am always happy to do it for my community.

Regards,
KrishnaV
Business Applications MVP
Twitter | Linkedin | YouTube | GitHub
If this post helps you give a and if it solved your issue consider Accept it as the solution to help the other members / new members of the community.

Hi @JunkoOhara ,

 

My bad, I have changed the code a little bit for onSelect event of the button. With that change the final code looks like as below:

Try this, I have tested and worked as expected:

  1. OnVisible of the screen set the below:

 

ClearCollect(collStaff,{DateItemCreated:Now(),StaffNumber:1014,Temp:36.3});
Collect(collStaff,{DateItemCreated:Now(),StaffNumber:516,Temp:36.2});
Collect(collStaff,{DateItemCreated:Now(),StaffNumber:1383,Temp:38.3});
Collect(collStaff,{DateItemCreated:Now(),StaffNumber:1002,Temp:41.3});

ClearCollect(collEntry,{Title:1002,FullName:"KrishnaV"});
Collect(collEntry,{Title:1003,FullName:"KVN"});
Collect(collEntry,{Title:1007,FullName:"ABC"});​

 

  • Now Create a button on the screen to get merged collection: onSelect set the below formula
ClearCollect(colMergedData,AddColumns(collEntry,"TempIN",""));
ForAll(
    collStaff,
    UpdateIf(
        colMergedData,
        StaffNumber = Title,
        {TempIN: Temp}
    )
)

The output will be:
MergedCollection_1.pngMergedCollection_2.pngMergedCollection_3.png


I hope this resolved your issue if you see any challenge/need further help please let me know I am always happy to do it for my community.

Regards,
Krishna
If this post helps you give a 👍 and if it solved your issue consider Accept it as the solution to help the other members find it more.


I hope this resolved your issue if you see any challenge/need further help please let me know I am always happy to do it for my community.

Regards,
KrishnaV
Business Applications MVP
Twitter | Linkedin | YouTube | GitHub
If this post helps you give a and if it solved your issue consider Accept it as the solution to help the other members / new members of the community.
v-xida-msft
Community Support
Community Support

Hi @JunkoOhara ,

Based on the UpdateIf function that you mentioned, I think there is something wrong with it. The filter columns specified within the second argument of the UpdateIf function, must be existed in the colmergedData collection already, but the "StaffNumber" column is not existed in the colmergedData collection.

 

I have made a test on my side, please consider modify your formula as below:

ClearCollect(colStaff;Filter(StaffProfile; Location=LbLocSelText_1.Text));;
ClearCollect(colEntry;Filter(EntryProtocol; DateItemCreatedText = LbDateSelected_2.Text));;
ClearCollect(     // Modify formula here
             colmergedData;
             AddColumns(
                          colStaff;
                          "TempIN"; LookUp(colEntry; StaffNumber = colStaff[@Title]; 'Temperature on entry');
                          "TempOUT"; "test"
            )
);;

Please consider try above formula without using UpdateIf function, then check if the "TempIN" column in colmergedData collection has been populated with proper value.

 

Best regards,

Community Support Team _ Kris Dai
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

Thank you @KrishnaV  Yes your sample worked on my APP as well.

 

But once I connect to my data, it does get the data, but the "ForAll" is not working..

I am thinking maybe this is because my collection has around 400 staffs and it is over the capacity of PowerAPPs..

 

ClearCollect(colmergedData;AddColumns(colStaff;"TempIN";"";"TempOUT";""));;

ForAll(colEntry;
UpdateIf(colmergedData;StaffNumber=Title;{
    TempIN:'Temperature on entry';
    TempOUT:'Temperature on exit'
}
)
)

2020-06-26 18_11_46-ALL_StaffRegisterReport - Saved (Unpublished) - Power Apps.png

 

 

Thank you so much, "colStaff[@Title]" did the magic.

Helpful resources

Announcements
PA User Group

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

secondImage

Demo Extravaganza is Back!

We are excited to announce that Demo Extravaganza for 2021 has started!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

Power Apps June 2021

June Power Apps Community Call

Don't miss the call this month on June 16th - 8a PDT

Top Solution Authors
Top Kudoed Authors
Users online (58,229)