Manual Sorting (Move Items ↑↓ w/Buttons) || Bonus: Sequential Numbering

Hey everyone

This has been on my list of things to do for awhile now, finally getting around to it. (^_^)

  • In this post I’ll walk you though how you can implement a series of actions that will allow you to move [Related Items] up/down in a list easily with a push of a button.

This implementation works best when you have a parent-child relationship


---------------- Manual Sorting ----------------

How to add buttons to a table view to allow for moving items up/down in that list.

---- Efficiency Notice ----

This implementation, while complex, makes use of text-manipulation formulas to accomplish all the “math to find neighboring records” - which means it processes extremely fast.

  • There are other methods you can use to create a push-button-ordering system, some of which are detailed in the thread below, but they rely on variations of a SELECT() formula.

This implementation is intended for apps with large data-sets, who can’t really afford to run a SELECT() over a table with 10,000 records.

---- Efficiency Notice End ----


~ The Sort Column ~

First and foremost: you need a sorting column in the table (to store the sort number for that item)

Summary

  • I like to hide this in the form with CONTEXT("ViewType") <> "Form"
    • this way it’s out of the way inside the form


~ The Actions ~

There are 14 unique actions involved in making this work smoothly.

Action Name
Set | Sort (MINUS 1)
Set | Sort (PLUS 1)
New | Line_Item (This order)
AUTO Delete
Ref Set | ALL below (PLUS 1)
Ref Set | ALL below (MINUS 1)
Ref Set | Previous Item (PLUS 1)
Ref Set | Next Item (MINUS 1)
Button | Move Item Down
Button | Insert Item
Button | Move Item Up
Button | Delete item
Set | RESET Sort (to index position)
Ref Set | RESET line item Sorts

Check out all the actions inside the Sample App

I bet you weren’t thinking all THAT was necessary, huh? partyparrot (Appsheet)

~ The ORDERBY() Formula ~

In addition, you’ll need to adjust the REF_ROWS() formula on the Order table

Summary
  • You need to include an ORDERBY() to ensure items are STORED in the correct order in the lists
    • This way, when you set the sort number of the seventh item created to 1 - that item sits in the 1st position instead of the 7th inside the VC.

  • Otherwise, all the actions (to adjust the sort numbers of neighboring items) won’t mesh right.

Check out the [Related Order_Line_Items] VC in the Orders table



~ The Button Holder VC ~

Now that you’ve got everything situated (list in the correct order, actions ready to modify some sorting numbers), you just need a place to see these buttons inside the table view - that’s what the [Button_Holder] VC is for.

Summary

  • As you can see, the App Formula is " "
    • yeah… that’s a single space inside a set of double-quotes. (^_^)
      • You really only need to presence of the VC, it doesn’t matter what’s inside, what type it is… etc.
  • It doesn’t even matter what the VC is called, or it’s display name/description.
    • When you put the buttons on the column in the table view, the column header disappears anyways.


~ The Table View ~

The final bit of the puzzle is to conform your table view

Summary
  • Make sure you sort by the [Sort Column]
  • You can show the sort column if you want (it never changes though - or appears to never change, as the sort numbers are always in the correct order).

Check out the Order_Line_Items_Inline view



Hope it helps! Let me know if you have any questions.



~~~ Update ~~~

I have updated the Products table to include manual sorting there as well.

  • This demonstrates how to accomplish this trick when you don’t have a parent-child relationship, but still want to have buttons to sort things.

NOTICE: The implementation demonstrated on the Products table makes use of SELECT() style formulas; this implementation is inheirently less efficient than the parent-child version.

  • The parent-child version is all de-references and text-manipulation - lightning fast compared to SELECT() formulas.
10 Likes

BONUS TIP: Sequential Numbering of Line-Items


If you look at the [Order_Name] column on the Orders table, you’ll notice an initial value formula:

concatenate(
	"Order #", 
  count(Orders[OrderID]) + 1
)

This is a very basic way to generate a sequential ID - though I am not using it for the ID, just to generate the Name of the order in a sequential way. (I’m still relying on UNIQUEID() to generate my IDs.)

This demonstrates how simple it can be to implement a sequential numbering system.


If I wanted to include a numbering system for the line items, I would use the following INITIAL VALUE formula:

But you can see how this would get wacky real quick once you rearranged things, added new items, deleted some, etc.

  • This is why I didn’t use sequential numbering for the ID, instead I stuck to using UNIQUEID() to generate a valid 8-character GUID.

Learn more about how to handle sequential keys here:

4 Likes

Does this method hold up in multi tenant applications? In any case, super cool. I had done something like this a few years ago but ultimately scrapped it - yours looks like a cleaner implementation.

2 Likes

Good point! Thanks for bringing it up @Jonathon

When a user taps one of the buttons, to move something, they’re physically modifying that record in the database.

  • If you have multiple people all modifying the same line items, you’ll end up with “cross-contamination” :biohazard:
    • Where one user’s edits interfere with another users edits

It’s important to only implement this sort of functionality in a system where people interacting with the app will do so in a singular way.

Meaning one person will be creating/modifying/deleting the records (at any one time).

  • You can still have multiple people working from the same data, but only implement this kind of sorting thing where the situation means that users will only mainly be dealing with their own data - their own line-items or what not - when creating things.
    • Once things are created others can go back and edit things, like an admin approving things or something, just be aware that two people shouldn’t be “working” on the same data at the same time.

(Or at least they should coordinate with each other while working.)

  • “Okay, I just updated the row”
  • “Let me update and check… Yup, I see it now. Perfect.”

This sort of system works well in the following scenarios:

  • Invoicing (because typically there’s one person doing the invoicing work)
  • Orders (because again, there’s typically 1 person making the order)
  • Reports
  • Creating lists of things to do
  • Really any situation where you’ve got several items that could be reordered
4 Likes

Thanks @MultiTech_Visions . This is what I’ve been searching for year. Your cool features help me lot.

1 Like

(^_^) You’re welcome, I’m happy to hear it.

2 Likes

@MultiTech_Visions

  1. Is it necessary to have parent child reference connection to use this trick?

  2. In my case I have no ref tables. I have only one table view and I want to use this sorting trick in that table. Is it possible?

1 Like

Negative, that was just to make it easier to see things inside the inline view; but you could easily do this for a base-table.

1 Like

Just to make clear;

Let’s say I have only one table (Order_Line_Items) and no any other ref tables. I want use UX table view for this table with up/down sort arrows.

Then how expressions should be changed for actionsRef Set | Previous Item (PLUS 1)” and “Ref Set | Next Item (MINUS 1)”?

I tried to figure out this but I’m confused with parent child ref tables in expressions. :innocent:

1 Like

@Mark_S Sure sure, there’s really only a sleight modification to the formulas.

If we look at the “Ref Set | ALL below (PLUS 1)” action

  • it’s got the following formula:
[Line_Item_Order_Link].[Related Order_Line_Items]
 - 
split(
  left(
    concatenate([Line_Item_Order_Link].[Related Order_Line_Items]),
    find(
      [LineItemID],
      concatenate([Line_Item_Order_Link].[Related Order_Line_Items])
    ) + 7
  ), " , "
) - list("", [LineItemID])

So you’re wanting to know how can I repleace the [Line_Item_Order_Link].[Related Order_Line_Items] part for a regular table…

Table[Key]
 - 
split(
  left(
    concatenate(Table[Key]),
    find(
      [Key],
      concatenate(Table[Key])
    ) + 7
  ), " , "
) - list("", [Key])
  • Notice that the only changes were the following:

    1. each instance of [Line_Item_Order_Link].[Related Order_Line_Items] was substituted for Table[Key]
      • Both of these are lists of references; ANY list of references works here
    2. [LineItemID] was substituted for [Key]

Next let’s look at “Ref Set | Previous Item (PLUS 1)”

  • It’s nearly the same modification
list(index(
  Table[Key], 
  count(
    split(
      left(
        concatenate(Table[Key]),
        find(
          [Key],
          concatenate(Table[Key])
        ) + 7
      ), " , "
    ) - list("")
  ) - 1
))

Again, the only things that changed were the list of references and the column value used.

2 Likes

Very clear. That’s what I wanted to know. I will try and let you know. Thank you

1 Like

I tried with your sample app as you explained.

Now items/rows are moving up and down but not to the sequence.
Can you just check out the reason?

Link to app

1 Like

This is what happens if you forget to include the ORDERBY() in the Ref_Rows() VC on the Orders table.


@Mark_S Since you’ve removed the parent level, the ORDERBY() has been removed from this mix;
You can see what I was talking about when I said, “things won’t mesh well.”

To fix this, you need to wrap every instance of your Table[Key] with an ORDERBY() like the following:

ORDERBY(Order_Line_Items[LineItemID], [Line_Item_Sort_Number]),
 - 
split(
  left(
    concatenate(ORDERBY(Order_Line_Items[LineItemID], [Line_Item_Sort_Number])),
    find(
      [LineItemID],
      concatenate(ORDERBY(Order_Line_Items[LineItemID], [Line_Item_Sort_Number]))
    ) + 7
  ), " , "
) - list("", [LineItemID])

NOTICE: If you have a large # of records, this will have an effect on performance, BTW.

  • With the original parent-child setup, you’ve got a simple list you’re calling
    • No computation needed
  • With a table setup you’re still just working with a list, but the Table[Key] result will be computed each time
    • Light computation needed (Since it’s a complete table call, with no criteria, it will be relatively efficient)
  • However with an ORDERBY() wrapped around your table call… that’s the equivalent of a SELECT() statement.
    • Heavy Computation Needed (This will work, because it’s a form of “brute force,” but it’s not going to be efficient.)

If your table is going to have more than, say 1000 records, I would advise against this setup

  • Instead just show the sort number in a table view and enable quick edits.

2 Likes

Reminds me of this feature request:

Would be nice to be able to specify a Sort Order for a table - at the root table level

  • instead of everything being defaulted to the [_rownumber]

3 Likes

I thought having parent child relationship will badly affect on the performance. That is why I tried to experience this trick without ref table.

Actually my table has more the 500 rows and now I feel it is better to use your original trick which is working well in my case.

Thanks for sharing this useful trick and I appreciate your quick reply to my questions with very detail explanations.

1 Like

@Mark_S You’re welcome. (^_^)

I’ve updated the sample app to include manual sorting on the Product table

  • So anyone can see how to do it when using a root-table.

Thanks for inspiring the update! partyparrot (Appsheet)

2 Likes

man thank you so much for this it is much appreciated! You must have spent a ton of time on this.

2 Likes

Hi @MultiTech_Visions,

In my case, I want to group items and sort within the group. I tried it with this trick but seems not working. Is there any thing to be changed in expressions to achieve this.

You’re welcome. :blush: I’ve got maybe 10 or so hours in this one - it was something I’d implemented in another app, so it was just a matter of isolating out what was needed and copying to a fresh sample.


@Mark_S This get’s much more complex, but interestingly enough it’s actually how the original implementation I made works… in a way.

For grouping to work, it’s best to have an actual Ref parent-level

  • This way you have all the [Related Items] for each group inside a list on the parent’s level
    • making it much easier to accomplish batch changing.

This is actually a much more complex system, and for that reason I chose to not include it originally.


  • To make this work smoothly, you really need to think about things at two levels
    1. The Group level (which needs it’s own sort number); and
    2. The Item level (which has it’s own sort number within it’s group)

You need to combine these two together to create a “Global Sort Number” in the form of a decimal; where the “full numbers” - the numbers on the left side of the decimal point - represent the group sort number, and the individual item sort numbers are used for the decimal numbers.

  • I find it’s helpful to use 2 decimal places for the decimal


What if I don’t have a parent level?

  • Not having a parent level will make this sort of thing much more difficult, and would require you to apply an actual SELECT() formula inside all the formulas.

So instead of using:

ORDERBY(Order_Line_Items[LineItemID], [Line_Item_Sort_Number]),
 - 
split(
  left(
    concatenate(ORDERBY(Order_Line_Items[LineItemID], [Line_Item_Sort_Number])),
    find(
      [LineItemID],
      concatenate(ORDERBY(Order_Line_Items[LineItemID], [Line_Item_Sort_Number]))
    ) + 7
  ), " , "
) - list("", [LineItemID])

You’ll need to apply a criteria to the rows pulled.

ORDERBY(Order_Line_Items[LineItemID], [Line_Item_Sort_Number])

  • This pulls every value from the table, sorted by it’s sort number.
  • You need to change this so it only pulls the rows that match the grouping; you do this with a SELECT() (or other port)

(I feel it necessary to warn again about the impacts of having an ORDERBY(SELECT(...)) nested like that - if you have a lot of data, this is going to have a serious effect on performance. Especially in this instance, where you’ve got 3 duplications of the same ORDERBY(SELECT...)) inside the same formula. Just FYI… ye be warned)

The final version of the formula from above would be:

ORDERBY(SELECT(Order_Line_Items[LineItemID], [Grouping] = [_thisrow].[Grouping]), [Line_Item_Sort_Number]),
 - 
split(
  left(
    concatenate(ORDERBY(SELECT(Order_Line_Items[LineItemID], [Grouping] = [_thisrow].[Grouping]), [Line_Item_Sort_Number])),
    find(
      [LineItemID],
      concatenate(ORDERBY(SELECT(Order_Line_Items[LineItemID], [Grouping] = [_thisrow].[Grouping]), [Line_Item_Sort_Number]))
    ) + 7
  ), " , "
) - list("", [LineItemID])

:scream: :scream_cat:
Small crazy green guy NNNOoooooooooo

1 Like

:scream: :scream:Seems like quite complicated. I will give a try.

Thank you very much for sharing your expertise.

1 Like