Data,  Helpful hints

Shift numbered items in a gallery by more than one row

There are many posts on reordering a gallery with up/down buttons, but what if you want to shift an item a large number of places and re-order everything in between in one action ? The assumption here is that the gallery contains a sequential numeric field, is sorted by this field and the user wants to change a row item to another position with the result that the gallery will then be sorted in the new order.
Firstly, there are two possibilities on the number shift – it could be larger or smaller than its original position, so that needs to be dealt with separately. I have also allowed for there being no other unique identifier (such as the ID) in the record, so will use the number field only.

There are four basic actions required

  • Capture the old and new positions before anything is moved.
  • Get the item to be moved “out of the way”.
  • Add or subtract 1 from the position of the rows “in between” the old and new numbers.
  • Put the shifted row into its new position

So the resulting code – using a Text Input for the RowNo field and this OnChange of that control

With(
   {
      _Old: ThisItem.RowNo,
      _New: Value(Self.Text)
   },
   Patch(
      DataSource,
      ThisItem,
      {RowNo: 0}
   );
   If(
      _Old > _New,
      ForAll(
         Filter(
            Sort(
               DataSource,
               RowNo,
               SortOrder.Descending
            ),
            RowNo >= _New && RowNo < _Old
         ) As _Order,
         Patch(
            DataSource,
            LookUp(
               DataSource,
               RowNo = _Order.RowNo
            ),
            {RowNo: _Order.RowNo + 1}
         )
      ),
      _New > _Old,
      ForAll(
         Filter(
            Sort(
               DataSource,
               RowNo
            ),
            RowNo <= _New && RowNo > _Old
         ) As _Order,
         Patch(
            DataSource,
            LookUp(
               DataSource,
               RowNo = _Order.RowNo
            ),
            {RowNo: _Order.RowNo - 1}
         )
      )
   );
   Patch(
      DataSource,
      LookUp(
         DataSource,
         RowNo = 0
      ),
      {RowNo: _New}
   )
)

achieves these four steps and re-orders the gallery

6 Comments

  • Kerim

    Hi Warren,

    What should we use for the gallery items ?

    I tried the following formula but I have an issue with your patch code: the function can not operate on the same data source that is used for All

    With(
    {_items: PortfolioProjectList},
    ForAll(
    Sequence(CountRows(_items)),
    Patch(
    Last(
    FirstN(
    _items,
    Value
    )
    ),
    {RowNo: Value}
    )
    )
    )

  • Kerim

    Hi Warren,

    so sorry for the confusin I think my ctrl c was misleading.

    I’m trying to use your code in a gallery with a text input : I’m using the code in the on change property of the control.

    I replaced data source with my sharepoint list however I then have an error:

    “This Function cannot operate in the same data source that is used in for all”

    Thanks,

Leave a Reply

Your email address will not be published. Required fields are marked *