cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
sgokul95
Helper I
Helper I

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

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 

1 ACCEPTED SOLUTION

Accepted Solutions
RandyHayes
Super User
Super User

@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.

 

 

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up below. Solved your problem? - Click on Accept as Solution below. Others seeking the same answers will be happy you did.
NOTE: My normal response times will be Mon to Fri from 1 PM to 10 PM UTC (and lots of other times too!)
Check out my PowerApps Videos too! And, follow me on Twitter @RandyHayes

Really want to show your appreciation? Buy Me A Cup Of Coffee!

View solution in original post

5 REPLIES 5
RandyHayes
Super User
Super User

@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.

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up below. Solved your problem? - Click on Accept as Solution below. Others seeking the same answers will be happy you did.
NOTE: My normal response times will be Mon to Fri from 1 PM to 10 PM UTC (and lots of other times too!)
Check out my PowerApps Videos too! And, follow me on Twitter @RandyHayes

Really want to show your appreciation? Buy Me A Cup Of Coffee!

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

 

IDT Number blah2
341 
7982 
2453 
1343 
5963 
7244 

 

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
Super User
Super User

@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.

 

 

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up below. Solved your problem? - Click on Accept as Solution below. Others seeking the same answers will be happy you did.
NOTE: My normal response times will be Mon to Fri from 1 PM to 10 PM UTC (and lots of other times too!)
Check out my PowerApps Videos too! And, follow me on Twitter @RandyHayes

Really want to show your appreciation? Buy Me A Cup Of Coffee!

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.

Thanks for your help as always @RandyHayes !!

Helpful resources

Announcements
Microsoft 365 Conference – December 6-8, 2022

Microsoft 365 Conference – December 6-8, 2022

Join us in Las Vegas to experience community, incredible learning opportunities, and connections that will help grow skills, know-how, and more.

Top Solution Authors
Top Kudoed Authors
Users online (1,559)