Unanswered

Filtering joined collections

(1) Share

Report

Posted on by SJ-15080742-0

Hi

I have 2 sharepoint lists, i create a collection from each of them, and then create a 3rd collection where i combine 2 first collections.
First collection(colPeriod) contains an agents id, team, an period they worked
Second collection(colAgent) contains agents id, and name
(they contain more data, but its irrellavant here i think)
The reason for there being 2 lists/collections is that an agent can have multiple workperiods for different teams.

So what i try to do, is add the agents name from second collection and add it to the first collection and i put this into a third collection:

ClearCollect(
    colCombined;
    AddColumns(
        colPeriod;
        'AgentName';
        LookUp(
            colAgent;
            AgentIdPeriod = AgentIdAgent;
            AgentName
        )
    )
);;

This seems to give me exactly what i need, all the data is in the new collection, colCombined.

The problem arises when i try to filter colCombined, then it seems there is somehow still a connection to the first and second collection.

I try to filter this data based on team (that comes from first collection, but is not present in second collection), and this seems to remove the names of the agents (which is present in second collection, but not in the first collection).

ClearCollect(
    colFiltered;
    Filter(colCombined; TeamName = varSelectedTeam)
)

i created a variable that contains the selected team.

So when i check the contents of the last collection, colFiltered, then it still has all the data from the first collection,
but the column i added to colCombined from second collection is now empty.
So somehow the filter i used on colCombined seems to be affected by the data in first and second collection.

Is there any way to break the connection between my collections? So i can filter my data without losing data?

I tried chatGPT and worked on it for a day now, but still i can not get my filter to work.

Categories:

General Questions

All responses (3)

Answers (0)

WarrenBelz 146,679 Most Valuable Professional on at

Like (0)

Report

Filtering joined collections

Hi SJ-15080742-0

As I noted initially, it makes no sense assuming varSelectedTeam contains a team name (I think you need to check this) that is in the collection (exact spelling and case)
SJ-15080742-0 4 on at

Like (0)

Report
Filtering joined collections

Hi Warren

I tried putting the unfiltered data on a gallery, and if i do that, i can see AgentIdPeriod and AgentName, but as soon as i filter data based on TeamName then AgentName dissappears but AgentIdPeriod is still there, thats why i assume that somehow colCombined is stilll connected to colPeriod and colAgent, because TeamName does not exist in colAgent, so filtering by TeamName in colAgent then of course remove all the names because there are no matches.

Maybe simpler to explain with a bit of code :)

If i filter the data, the AgentName columns become empty

ClearCollect( colFiltered; Filter(colCombined; TeamName = varSelectedTeam) )

If i remove the filter, then there is still data in AgentName
ClearCollect( colFiltered; colCombined )
WarrenBelz 146,679 Most Valuable Professional on at

Like (0)

Report

Filtering joined collections

Hi SJ-15080742- 0,

As you can probably realise, the behaviour you have posted does not make a lot of sense and the "elements" of colCombined certainly should not be "hanging around". For a debugging exercise, have you looked at colCombined and ensured that the AgentName field contains data in every record ?