ClearCollect not working as intended

[notj]

Here is my formula.

ClearCollect(RecentItems, 
ForAll(MyEditFacilities, Filter(SPSource, FacilityCode=FacilityCode)));

Unfortunately it's returning 0 items. I've confirmed that there is data in the SPSource that has a FacilityCode that matches the FacilityCode in the record in the collection of MyEditFacilities. 

[RandyHayes]

@notj 

Please consider changing your Formula to the following:

ClearCollect(RecentItems, 
    ForAll(MyEditFacilities As _MEF, 
        Filter(SPSource, FacilityCode=_MEF.FacilityCode)
    )
);

This will ensure the proper Facility code is being compared.

I hope this is helpful for you. 

[notj]

@RandyHayes  still no data, unfortunately.

[RandyHayes]

@notj 

Can you explain a little more about your desired end result?  From your original formula, you were intending to get a Table (your collection) of Tables (your filters).  Is that the intention and are you stating that you have no data as in an empty collection or that it is not giving you what you might have wanted?

[notj]

I have a collection(MyEditFacilities) of records(just one for testing, but many in production) and a SP list of items(just one for testing, many in production). 

I'm trying to do a collect for all items in my SP list where FacilityCode matches the FacilityCode in my original collection(MyEditFacilities).

[RandyHayes]

@notj 

So to be clear, you have a collection called MyEditFacilities and it has records (not important what schema except that there is a FacilityCode numeric column in the record).

You did not mention it as a response to my question of what you want as a result - do you want a collection of the records associated with the FacilityCode or do you want a table of tables.

This is important to know as I do not know if your Filter is going to return one or many records.

IN other words is your FacilityCode unique in your list, and if you use, for example,  Filter(SPSource, FacilityCode=1234) are you expecting that there will be one record returned or potentially many?

So, do you want a table with columns relevant to the SPsource, or do you want a table with tables in it that represent the results of all records matching the FacilityCode?

There is a very distinct different.

[notj]

@RandyHayes 

The column is text, but yes, you have the rest of the information correct about MyEditFacilities. 

Sorry, I thought I explained I wanted to collect the records from the SharePoint list.

The schema of the MyEditFacilities has a text column named FacilityCode. The SharePoint list has a text column named FacilityCode. 

I'm trying to ClearCollect a second collection that includes the items from the SP list that have a FacilityCode that matches any of the items in the first collection, MyEditFacilities.

In other words, for every unique FacilityCode in MyEditFacilities, I want to Collect from the SharePoint List where the FacilityCode matches. Into a new collection. I expect multiple records in all of the collections/SharePoint list.

[RandyHayes]

@notj 

I believe I understand what you are trying to get to, but there is still a subtle answer that is needed.  If your original Formula stated something like ID, I would be crystal clear on it, but you are using FacilityCode - I need to understand the uniqueness of that.

I will explain by example:

Let's say your MyEditFacilities is a table of records (again not important what the record schema is, only that there is a FacilityCode)

MyEditFacilities has the following table:

    {FacilityCode:"AAA"}, 
    {FacilityCode:"BBB"}, 
    {FacilityCode:"CCC"}

IF Your SPList however has the following:

    {ID:1, FacilityCode:"AAA"}, 
    {ID:2, FacilityCode:"AAA"},  
    {ID:3, FacilityCode:"BBB"},
    {ID:4, FacilityCode:"CCC"},  
    {ID:5, FacilityCode:"BBB"},
    {ID:6, FacilityCode:"AAA"}

Do you then want a table in your resultant collection like this:

    {FacilityCode:"AAA",
        Facilities: [ {ID:1, FacilityCode:"AAA"}, {ID:2, FacilityCode:"AAA"}, {ID:6, FacilityCode:"AAA"} ]
    },
    {FacilityCode:"BBB",
        Facilities: [ {ID:3, FacilityCode:"BBB"}, {ID:5, FacilityCode:"BBB"} ]
    },
    {FacilityCode:"CCC",
        Facilities: [ {ID:4, FacilityCode:"CCC"} ]
    }

OR like this:

    {ID:1, FacilityCode:"AAA"}, 
    {ID:2, FacilityCode:"AAA"}, 
    {ID:6, FacilityCode:"AAA"},
    {ID:3, FacilityCode:"BBB"}, 
    {ID:5, FacilityCode:"BBB"},
    {ID:4, FacilityCode:"CCC"}

---- OR ------

Is your SPList more like this...where the FacilityCode is not repeated in the records?

    {ID:1, FacilityCode:"AAA"}, 
    {ID:2, FacilityCode:"BBB"},  
    {ID:3, FacilityCode:"CCC"},
    {ID:4, FacilityCode:"DDD"},  
    {ID:5, FacilityCode:"EEE"},
    {ID:6, FacilityCode:"FFF"}

and you want your resultant collection to be like this:

    {ID:1, FacilityCode:"AAA"}, 
    {ID:2, FacilityCode:"BBB"}, 
    {ID:3, FacilityCode:"CCC"}

(again based on the above records of the MyEditFacilities)

Sorry to harp on this, but I want to make sure you provide the distinction as I don't want to provide you a formula only for you to come back and say, that's not what I wanted.

[notj]

I understand what you're asking. The FacilityCode might show up in both locations more than once. I want to return all records separately. There can be multiple items in the SP List with the same FacilityCode. I want to return all of them, as long as they match a FacilityCode in MyEditFacilities.

[RandyHayes]

@notj 

Okay, so you still didn't specify from my examples what you are looking for, so I will just have to assume based on your original formula.

Set your formula to the following:

ClearCollect(RecentItems, 
    ForAll(MyEditFacilities As _MEF, 
        {Facilities: Filter(SPSource, FacilityCode=_MEF.FacilityCode)}
    )
);