Show data in gallery start from the middle of rows

[Syukri_Zainal90]

Hi all, 

I have 2 SPO list, 1, Invoice_Header & 2. Invoice_Details.   1 row of data in Invoice Header list can have multiple row of data in Invoice Details list. I have created powerapps screen which is when user click at which invoice header.ID, will go to next screen show the list of the invoice details. Then, I use print() function. Then I limit one screen to print only 20 rows of data by using this code --> FirstN(Filter('Invoice_details', Running_Number_No_SB = Gallery1.Selected.ID),20).

If the invoice details have more than 20 rows of data, how do I code so that the next page will show data from  21 - 40 rows?

I'm stuck at below picture

Really appreciate feedback.

Thank you in advance.

[Chris-D]

Hi @Syukri_Zainal90, you can get the correct 20 items for your page by slightly modifying your formula using LastN() and FirstN() together, and wrapping in some logic. 

// Uses a variable, PageNumber, to indicate current page
With({
    Records:

    // Use FirstN() to get the records *up to* the current page
    FirstN(
        Filter('Invoice_details', Running_Number_No_SB = Gallery1.Selected.ID),

        // e.g. for page 3, get first 60 records
        20 * PageNumber
    )
},
    With({
        // Work out number of whole pages to get remainder
        WholePages: (CountRows(Records)/20) -Int(CountRows(Records)/20)
    },
        With({
            // How many on this page?
            ThisPage: CountRows(Records) -(WholePages*20)
        },
            // LastN() to get the records for this page
            LastN(
                Records,
                // ThisPage could be 0 if CountRows(Records) is a multiple of 20
                If( ThisPage, ThisPage, 20)
            )
        )
    )
)

[WarrenBelz · Accepted solution]

Hi @Syukri_Zainal90 ,

You can also number the rows like this and then at the bottom pick out any set you need

With(
   {
      _Data: 
      Filter(
         'Invoice_details',
         Running_Number_No_SB = Gallery1.Selected.ID
      )
   },
   With(
      {
         _Rows: 
         ForAll(
            Sequence(CountRows(_Data)),
            Patch(
               Index(
                  _Data,
                  Value
               ),
               {RowNo: Value}
            )
         )
      },
      Filter(
         _Rows,
         RowNo > 20 && RowNo < 41
      )
   )
)

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

MVP (Business Applications)   Visit my blog Practical Power Apps

[Syukri_Zainal90]

Hi @Chris-D 

Thank you for the reply. I already try your code, and I put 20* 3 for page 3. But it seems the data show start from row number 10. Any advice?

[Syukri_Zainal90]

Hi @WarrenBelz ,

Thank you for your reply. It work and easy for me to choose range of rows that I want to show in the report.

Really appreciate !!

[Chris-D]

Hi @Syukri_Zainal90, apologies, I'd made a mistake working out WholePages. 

I liked @WarrenBelz solution though so I built on this and added code to get the desired page from it. 

// Use With() to define some input parameters
With({
    PageNumber: Value( TextInput5_2.Text),
    RowsPerPage: Value( TextInput5_3.Text),
    Data: Gallery7.AllItems
},
    // Use With() to add variables to scope
    With({
        Data:

        // ForAll() Sequence() makes a list of numbers
        // Patch() adds a row number to each record
        ForAll( 
            Sequence( CountRows( Data)),
            Patch( Index( Data, Value), { RowNum: Value})
        ),

        // Work out the maximum page number
        MaxPages: RoundUp( CountRows(Data)/RowsPerPage, 0)
    },
        With({
            // Min() ensures we don't overrun our data
            PageNumber: Min( MaxPages, PageNumber)
        },
            With({
                // Work out start row. Page 1 starts from row 1.
                StartRow: 1+ (PageNumber-1) *RowsPerPage
            },
                // User Filter() to grab the rows we want
                Filter( Data, RowNum >= StartRow, RowNum < StartRow +RowsPerPage)
            )
        )
    )
)

[Syukri_Zainal90]

Hi @Chris-D ,

Appreciate your idea. Based on your code meaning that I need to input which number of rows that I want to view. But for this time, the rows is depends on the which running number that user will click on the home page.

Anyway, appreciate again your idea and I can use that one for another project.

Thanks a lot!!!

[Chris-D]

No problem. I always try and write code with an 'input parameters' section like this. It makes the code easier to use/tweak for different situations and easier for others to use too. Glad you found it useful.