Count items in a collection from the first item/last item till a particular item

[sgokul95]

Hi All,

I am having a collection in which there is a column called "ID". The ID column contains unique value but the column is not sorted ascending or descending. 

I want to count the number of items from the first item till a particular item (The ID value that I know). 

Similarly, I also want to count the number of items from the last item in the collection till a particular item (The ID value that I know). 

Can anyone please help me out here? @RandyHayes 

Thanks in advance,

Gokul 

[RandyHayes]

@sgokul95 

Can you provide a simple example with some sample data, what you are trying to do and what you expect as a result?  That would be helpful.

[sgokul95]

Hi @RandyHayes , Yes. Consider a collection like below. 

ID	T Number	blah2
34	1
798	2
245	3
134	3
596	3
724	4

Consider that the row having ID value as 134 is the selected row. I need to fetch the item that is present before this selected item (i.e., the row having ID value as 245). Similarly I need to fetch the item that present after the selected item (i.e., the row having ID value as 596).

I need this previous and next item so that to navigate to the previous and next item from the current selected item. Once I fetch that row then I will use its ID value to fetch the item from the SharePoint and then will update the "Item" of the EditForm.

[RandyHayes · Accepted solution]

@sgokul95 

So in that case, the best thing to do is to index your collection with sequential numbers and then find the target number and then the prior and next records.

Example:

With({_items: yourDataSource},
    ClearCollect(yourCollection,
        ForAll(
            Sequence(CountRows(_items),
            Patch(Index(_items, Value), {_index: Value})
        )
    )
)

    

This will index your collection.

Then for the records:

LookUp(yourCollection, ID=134) will give you the record you are looking for.

Index(yourCollection, LookUp(yourCollection, ID=134, _index)-1) will give you the prior record.

Index(yourCollection, LookUp(yourCollection, ID=134, _index)+1) will give you the next record.

[sgokul95]

Hi @RandyHayes , Thanks so much for your suggestion. But I am really really sorry to further trouble you. Since I am a noob, I am finding it a bit difficult to understand your code and replacing with proper values in my end. 

Consider a collection "colA" that has the table of the rows that has the column ID.  Also consider "colSeq" as an empty collection in which maybe we can add the column containing the sequential numbers.

With({_items: colA},
    ClearCollect(colSeq,
        ForAll(
            Sequence(CountRows(_items),
            Patch(Index(_items, Value), {_index: Value})
        )
    )
)

I am getting error when using the above snippet. Can you please help me out here 😞

Update: 

I think I got it. Closing brace was missing in Sequence function.

[sgokul95]

Thanks for your help as always @RandyHayes !!