Lookup between two collections and update

[stephenJames]

Hello,

         In my PowerApp i have two collections that are brought in from two SharePoint lists at App Run. What i am looking to do is Lookup a particular column value in collection2 and see if it exists in collection1, if it doesn't i want to update collection1 with the record from collection2. 

I've done something similar if the field !IsBlank but not if the record doesn't exist . 

The app is for an employee directory and org chart. The data i am adding is Vacant positions to the main Employee collection. So when it checks the Main Employee collection list and doesn't find a particular position, it pulls that position from my positions collection and adds it to my Employee collection with the Vacant dummy data i have in that collection. 

Does anyone have any suggestions for how i may be able to get this to work?

[RandyHayes]

@stephenJames 

Please consider this formula as a solution to your question:

Collect(collection1, 
    ForAll(
        Filter(collection2, column="columnValue" && !(column in collection2.column)), 
        <either a records structure to match collection1, or ThisRecord if the schema is the same>
    )
)

Not sure if you were just interested in one record or all.  The above deals with all of them.  If the schema is the same between collections, then you can specify ThisRecord in the ForAll.  If not, then you will need a record syntax that matches what you want in collection1.

I hope this is helpful for you.

[stephenJames]

Thanks for the suggestion @RandyHayes 

Here's what i have managed to plug in so far

Collect(colAll, 
    ForAll(
        Filter(colPositions, JobTitle && !(JobTitle in colPositions.JobTitle)), 
            {
                FirstName: "",
                Last_Name: "",
                FullName: FullName,
                ManagersName: "",
                ManagerJobTitle: ManagerJobTitle,
                WorkPhone: "",
                EMail: Email,
                ol_Departemnt: Department,
                Divsion: Division.Value,
                JobTitle: JobTitle,
                ImageURI: "",
                Locations: Locations,
                Height: "",
                Width: "",
                xCoord: "",
                yCoord: ""
            }
    )
);

The structure of colAll is different from colPostions so I'm defining the syntax. However I'm not getting the required results as I think I'm not defining things correctly.
What i am expecting to happen is any JobTitle in colPostions that isnt found in colAll is collected from colPositions and put into colAll. Because colAll has alot more columns than colPostions i have listed out all the columns in the structure on the left for colAll and blanked out the fields that wont get any data from colPositions. 

[RandyHayes]

@stephenJames 

Your filter part of that formula is not correct.

You are just stating JobTitle as a criteria - this will not evaluate to true or false and nothing will really happen.

You originally mentioned "Lookup a particular column value in collection2 and see if it exists in collection1"

So, I assume from the formula you provided that JobTitle is the particular column you want, but you had no value specified - so this is where you need that:

Filter(colPositions, JobTitle="some Title" && !(JobTitle in colPositions.JobTitle)), 

And actually, I notice a slight mistake in my formula, you also want to see if this exists in collection1, so the filter formula should be:

Filter(colPositions, JobTitle="some Title" && !(JobTitle in colAll.JobTitle)), 

[stephenJames]

@RandyHayes 

So if i have multiple jobtitles i needs to check against would i need to duplicate the code multiple times 

for example 

Filter(colPositions, JobTitle="Jobtitle1" && !(JobTitle in colAll.JobTitle)),
Filter(colPositions, JobTitle="Jobtitle2" && !(JobTitle in colAll.JobTitle)),
Filter(colPositions, JobTitle="Jobtitle3" && !(JobTitle in colAll.JobTitle)),

Or is there a way to bunch it all up together to grab all the jobtitles from one collection and check if they exist in the other and add them if they don't.

I apologize if my original comment didn't explain what i am trying to achieve clearly.

[RandyHayes · Accepted solution]

@stephenJames 

No, that would not work, you would need this:

Filter(colPositions, JobTitle in "Jobtitle1|Jobtitle2|Jobtitle3" && !(JobTitle in colAll.JobTitle)),

This will give you what you need.

[stephenJames]

Thanks @RandyHayes that was exactly what i was looking for.