Find a value of one column in a sharepoint list in another sharepoint list and populate the field in powerapps as per the findings .

[Prateek9]

Hello All,

I Have 2 Sharepoint Lists in my powerapps: Onboarding and Offboarding with a common column "Employee ID". My requirement is that i when display the data of Onboarded employees through gallery, it also displays whether that employee is active or Inactive and to do this it should search for the same employee ID in Offboarding List and depending upon whether employee ID in Onboarding List is present in Offboarding List or not , it should give 2 values :

1. Active - if employee ID in Onboarding List is not present in Offboarding List.

2.Inactive - if employee ID in onboarding List is present in Offboarding List

Can you please tell me what should be the code for this . Can we do this directly in Powerapps or is there a way to add a calculated column in Onboarding List which finds the value in Offboarding List

[shanecosgrove]

Hi,

You could use the countRows function with a filter to count the number of rows that the employeeID appears in the Offboarding list. If the CountRows returns 0 then the person is active, if it returns 1 then the person in inactive.

CountRows(Filter(Offboarding,'employee ID' = ThisItem.'employee ID'))

You can use the countRows inside an if statement to change the text.

[Prateek9]

Hi @shanecosgrove 

Thanks for replying , i did what you said and i am able to get the status. 

I now want to filter my gallery based on this selection . Can you please help me out with it .

Label Name in which in put the formula - Active/Inactive Label

I had to Hard code the values "Active, Inactive" in the combobox since this is not coming from a SP list. 

Below is the Items fields of gallery in which other filter have been applied.

[shanecosgrove]

So you will probably need to add a temporary column to your datasource in order to filter on Active/Inactive.

Here is an example of how to filter and use the add columns in the items property of the gallery.

"EmployeeStatus" will be the temporary column

Filter(AddColumns('Onboarding Details_V01',"EmployeeStatus,if(CountRows(Filter(Offboarding,'employee ID' = OnBoarding.'employee ID')) = 0,"Activce","inactive")),EmployeeStatus = EmployeeStatusDropdown.Selected.Value)

Using the above you can also change the active/inactive label to be ThisItem.EmployeeStatus

[Prateek9]

Hi @shanecosgrove 

Apologies for replying late as i was not able to check this our during the weekend. I tried today but ran into errors. 

1. How should i integrate your code along with the existing code present in Item property of the gallery as that code is for other filters to work.

2. since i was not able to integrate it , i then tried by only applying the above code but  i ran into multiple errors mostly in screens where i am using the Edit Form to enter the new data and the Patch Function , where i am adding the data to Sharepoint list . 

I tried the adding the above code in the Employee Status Label but there also i am getting the error with the "Type"