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.
