In my PowerApp, I have 2 collections:
1. ClearCollect(colWaitlists, SortByColumns(Filter(colTESTDATA, BatchCode in colRejected.BatchCode, NominationStatus = "Waitlisted"), "field_2", "Created")) -- here, I get all the Waitlisted status participants in colTESTDATA in an ascending order (based on the registration date)
2. ClearCollect(CountPerBatch, AddColumns(GroupBy(colRejected, "field_2", "Grouped"), "Count", CountRows(Grouped)) -- here, I count the # of Rejected participants in colRejected
Now I want to create a 3rd collection, where it would only get the number of waitlisted participants from colWaitlists based on how many are there rejected in a batch (CountPerBatch collection). For example, there are 5 waitlists on Batch 1 and since there are 3 rejected participants from the same batch. I want to get the first 3 waitlists and put them in my 3rd collection.
I have my formula for my 3rd collection as
ClearCollect(colRecords, FirstN(colWaitlists, LookUp(CountPerBatch, field_2 = ThisItem.BatchCode).Count))
However, it only collects the number of waitlists from the first batch (in an instance, I reject multiple participants from different batches).
Please help.
example:
Here's my colWaitlists, it contains 4 records (wherein 3 participants have the same batch [14] and the other is on batch 15)
There are two rejections in Batch 14 and 1 in batch 15, hence two records and one record were captured on its Count respectively (CountPerBatch collection)
However, on my 3rd collection (colRecords)--only 2 waitlisted from Batch 14 was captured, it should retrieve one waitlisted from Batch 15
