cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
akbarmahfuzalam
Post Prodigy
Post Prodigy

LookUp with Multiple Condition

Hi All,

 

I am looking for help in Lookup Function in Powerapps.
I have two galleries and I want the Text which is HB Hours to be populated with sum of another Gallery which has multiple entries of the same User Name Queue Name and date.

 

I want total duration in Hb Hours

 

What formula shall set to get the desired result

 

 

akbarmahfuzalam_0-1620034014295.png

 

akbarmahfuzalam_1-1620034263228.png

 

Thanks in advance

 

1 ACCEPTED SOLUTION

Accepted Solutions

I just saw you filtered the wrong way round. For filtering, you've got to say PowerApps which given values shall be identical to which ones in the collection. Therefore you have to replace the label Texts, not the collection/gallery ones. Please change Text property for testing purposes to:

Sum(Filter(CollHBData, "5/3/2021" = Text(ShiftDate,DateTimeFormat.ShortDate,"en-US"), "akbar.m.alam" = EID, "YourHiddenText" = 'Activity Name'),PDur)

Please take care that there is data in the collection by looking it up in Files > Collections > CollHBData. If yes, please take a screenshot of your PDur, ShiftDate and EID column. How many items are in the Sharepoint List HBData? Please take care that Activity name and the filter string in your text property are exactly the same.

If not, just insert a button, put a ClearCollect(CollHBData,HBData) in it and press it. 

View solution in original post

11 REPLIES 11
zaphod88
Responsive Resident
Responsive Resident

Are there lists/collections for both of the galleries?
Try
Sum(Filter(Gallery2.AllItems, 'Process Date'.SelectedDate = DateGallery2.SelectedDate, 'User Name'.Text = UserNameGallery2.Text, 'Queue Name'.Text = QueueNameGallery2.Text).TotalDuration.Text)
You have to replace the input names with your own, of course.

@zaphod88 

Thanks for your answer.

I tried to implement the code but unfortunately, it didn't work for me.

 

akbarmahfuzalam_0-1620039848919.png

 

And I have 

akbarmahfuzalam_1-1620039943252.png

 

akbarmahfuzalam_2-1620040195551.png

 

akbarmahfuzalam_3-1620040253917.png

Activity Name is Queue Name

ShiftDate is WorkedDate

EID is UserName/Title

 

akbarmahfuzalam_4-1620040354215.png

 

zaphod88
Responsive Resident
Responsive Resident

Try:

Sum(Filter(GalleryHB.AllItems, Label2_4.SelectedDate = Text(ShiftDate,DateTimeFormat.ShortDate,"en-US"), Lbl_UserName.Text = EID, Lbl_QueueName.Text = 'Activity Name').tDuration)

I hope I got all column and label names right - if not, you may have to correct them. You already get a delegation warning - I think it's a good idea to ClearCollect(CollHBData,HBData) and use the collection instead of the SP list.

@zaphod88 

 

I tried the below formula and even i tried to used the CollHBData but its fetching an error

akbarmahfuzalam_1-1620051966713.png

 


@zaphod88 wrote:

Try:

Sum(Filter(GalleryHB.AllItems, Label2_4.SelectedDate = Text(ShiftDate,DateTimeFormat.ShortDate,"en-US"), Lbl_UserName.Text = EID, Lbl_QueueName.Text = 'Activity Name').tDuration)

I hope I got all column and label names right - if not, you may have to correct them. You already get a delegation warning - I think it's a good idea to ClearCollect(CollHBData,HBData) and use the collection instead of the SP list.




 

Sum(Filter(GalleryHB.AllItems, Lbl_WorkedDate.SelectedDate = Text(ShiftDate,DateTimeFormat.ShortDate,"en-US"), Lbl_UserName.Text = EID, Lbl_QueueName.Text = 'Activity Name').PDur)

 

My fault. Try replacing the point before PDur with a comma, so:

Sum(Filter(GalleryHB.AllItems, Lbl_WorkedDate.SelectedDate = Text(ShiftDate,DateTimeFormat.ShortDate,"en-US"), Lbl_UserName.Text = EID, Lbl_QueueName.Text = 'Activity Name'),PDur)

@zaphod88 

 

Still, I find an error as mention below.

I tried the below formula as well

 

Sum(Filter(CollHBData, Lbl_WorkedDate.Text = Text(ShiftDate,DateTimeFormat.ShortDate,"en-US"), Lbl_UserName.Text = EID, Lbl_QueueName.Text = 'Activity Name'),PDur)

 

But I am not getting any value. The label is blank.

akbarmahfuzalam_2-1620055142317.png

 

 

akbarmahfuzalam_1-1620054414713.png

 

Maybe a silly question, but: Are there any items that match the Filter() conditions? Enter a name, a date and a Queue Name that are corresponding to an item in the gallery/the Sharepoint list into the labels and see if it works then. Maybe there is no item in the Sharepoint list that matches all three criteria.

If PowerApps is showing no errors, at least the syntax is correct. As I don't know if I got all those labels right, try to remove 1 or 2 conditions for testing purposes and see if it works then. You could also try to replace the Text(...) just by ShiftDate.

@zaphod88 

 

I tried that way as well

akbarmahfuzalam_4-1620057543680.png

 

 

 

akbarmahfuzalam_1-1620057280371.png

 

akbarmahfuzalam_2-1620057389765.png

 

 

 

 

Found a problem:

zaphod88_0-1620058104494.png

Could you try it again after correcting? 

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.

Top Solution Authors
Top Kudoed Authors
Users online (2,819)