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

MultiTech
Participant V

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

Here's a video that was originally released for my Patreon supporters, which shows a complete implementation of this process.

maxresdefault.jpg

Table of Contents
0:00 - Stream Loading
1:08 - Actual Start
1:45 - Getting the instructions from the community
   - https://www.googlecloudcommunity.com/gc/Tips-Tricks/Manual-Sorting-Move-Items-w-Buttons-Bonus-Sequen...
2:20 - Inserting the Sorting Column
4:11 - Auto-Incrementing Numbers (Auto-Counting)
5:09 - ACTIONS: Beginning
6:20 - ACTIONS: Set | Sort (MINUS 1) & Set | Sort (PLUS 1)
7:08 - ACTIONS: New | Line_Item (This order)
8:46 - ACTIONS: AUTO Delete
9:14 - ACTIONS: Ref Set | All Below (PLUS 1)
11:20 - ACTIONS: Ref Set | All Below (MINUS 1)
12:12 - ACTIONS: Ref Set | Previous Item (PLUS 1)
13:12 - ACTIONS: Ref Set | Next Item (MINUS 1)
15:04 - BUTTONS: Button | Move Item Down
17:10 - BUTTONS: Button | Move Item Up
17:47 - BUTTONS: Button | Insert Item
18:50 - BUTTONS: Button | Delete
20:34 - Button Holder (virtual column)
24:42 - Hiding the original Delete action
26:10 - OrderBy() on the list of items
28:41 - Inline View Event Action
29:03 - Testing things out/Misc Troubleshooting (names too closely similar)
31:53 - Sorting RESET Button


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

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

3X_a_e_aeed986bf799f9fa3313afeed739f070c7d44924.gif

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


 

3X_5_9_59c0f5d108975e9cb3cd1a0c63f8f9f1239c2ff2.png

 

  • 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? 3X_d_5_d51363a862e7ab883241c312ac5d7f271579cdd3.gif

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

3X_a_1_a19fd4933758013fd216a3fbca886a4ca9a92f18.png

  • 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


 

3X_e_5_e53613970158717b675176f7a3e210bfa1ab156f.png

 

  • 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).

 

3X_d_b_db6751be282a717b9d15029a811fd5e700988013.png

 

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.
 
Mark_S:

Let’s say I have only one table (Order_Line_Items) and no other ref tables.

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.
23 37 5,057
37 REPLIES 37

MultiTech
Participant V

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:

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.

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”
    • 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

You CAN overcome the single-person only issue and allow use by multi-tenants by storing all of the sorting details by user in a separate table and then apply a Security Filter or a Slice to extract and use only that user’s details when the data is loaded (Security Filter is best for efficiency). I don’t think this will add significantly to the processing.

Mark_S
Participant II

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

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

Mark_S
Participant II

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

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.

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.

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

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

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.

3X_f_9_f99ec2310f6cd951e137a4d96f7b5e08656758b8.png

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.

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.

What if my key is REF? How to make the ORDER BY FORMULA run correctly? Right now it said invalid ORDERBY formula? Please help

my data has no ref table

I stuck with the order by formula. Please help

My data has no ref table

PROBLEM2.JPG

Mark_S
Participant II

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

MultiTech
Participant V

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]

MultiTech
Participant V

@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! 3X_d_5_d51363a862e7ab883241c312ac5d7f271579cdd3.gif

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

You’re welcome. 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

3X_0_9_09b4de3435affda3f45545444cd0fbdd8762d85e.png


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])


3X_a_d_adb37e4dbb5d7dd6c85cdd1a0e8f3347920b615f.gif NNNOoooooooooo

Seems like quite complicated. I will give a try.

Thank you very much for sharing your expertise.

Here is another sample how to use manual sorting with just 2 actions:

https://www.appsheet.com/samples/Prioritized-List--Sample-App?appGuidString=40483c73-625c-4230-bef5-...

It uses the RANDBETWEEN() expression.

I use it in my App

3X_4_d_4dba49305a5a33429e4e68c3150210851ffe624c.gif

Oh my…

(Insert disclaimer for large data sets here…)

Right now my table using this RANDBETWEEN() method has more than 20.000 rows and 10 columns. I don’t see any performance problems.

@Fabian Actually you can see it in the gif you made compared to my original one.

3X_4_d_4dba49305a5a33429e4e68c3150210851ffe624c.gif 3X_a_e_aeed986bf799f9fa3313afeed739f070c7d44924.gif


Watch how long it takes when you tap an item before it’s moved…
3X_4_d_4dba49305a5a33429e4e68c3150210851ffe624c.gif

  • Taaaap move. taaap move, taaaaaaap move (some longer than others)

Compared to the responsiveness of my original gif (which is actually modifying 2 records, not just one)
3X_a_e_aeed986bf799f9fa3313afeed739f070c7d44924.gif

Boom boom boom - as soon as I tap the action, both rows - not just the one I tapped, but also the neighboring record - are adjusted; there’s no 1/2 second hitch.


I developed this technique in response to complaints from my users that, “moving items up and down in a list is taking too long.” I originally had something similar where I was doing SELECT() and things; I switched to this so things would be as fast as possible.

  • This method works well with large data sets because it utilizes de-references and text-manipulation formulas - all of which process really really fast. (Compared to any SELECT() you may run)
  • Additionally: when the formula runs, it’s only processing over (8) records (in the preview gif above) because there’s only 8 [Related Order_Line_Items] in the list.
    • Even if I had 20,000 rows in that table - the original formulas I propose are only working on 8.
  • And even then, it’s just taking the [Order_Parent].[Related Order_Line_Items]… CONCATENATE()ing that out into a text string a couple of times, and doing some FIND() and LEFT() stuff.

Hi @MultiTech_Visions you are right. Thank you for sharing this Tip.

@Fabian thank you for expanding the discussion! It’s through discussions like these that we can answer a whole bunch of questions that people may be having, that I missed in my original post.

tecpme_casm
Participant II

That is amazing thank you

Nirmal_Giri
Participant III

@MultiTech_Visions
The manual sorting is amazing. Thank you…

However, as Steve mentioned, the magical behavior does not work when I use ORDERBY(REF_ROWS()…). I have noticed that this works for parent-child relationship that is already created/added.
If I remove the ORDERBY, my app works and I can add new items as the autofill of column referenced works but the manual sorting does not.
If I keep the ORDERBY, the manual sorting works (and that too on older rows with parent-child relationship defined) but the autofill does not happen, and I cannot proceed without this.

Any workaround to retain both the magical part of REF_ROWS and manual sorting?

Two things you can do here:

  1. Make your own action to create a child record with the parent value pre-populated

    • Basically remove the original “Add” action from that context, and instead show an action you’ve created to essentially do the same thing - but this one is hard-coded to capture the ref.
  2. Move the ORDERBY() out of the REF_ROWS() and instead order everything inside the formula use

    • The idea here is to move the ordering formula from the root VC into each use of the VC
    • Go throughout your app, find every instance where you’ve called the VC and wrap THAT use in the ORDERBY()
      • You can see how this sux as now you’ve got to replicate that orderby() several times over, but this will retain the ref-magic while still allowing you to get things in the correct order.

ZoroK
Participant I

Hi, I have tested in the sample app, but cannot understand the usage for these 2 actions,

 

Set | RESET Sort (to index position)
Ref Set | RESET line item Sorts

 

Can anyone explain these actions for me? I clicked it in the sample app but nothing happen. Many many thanks.

Hello congratulations for the tutorial.
I would like to ask you for clarification on my specific case:

1. Is it possible to make your formula work for an undefined but constantly expanding number of rows by users? (continue to insert lines)

2. Is it possible to understand how to obtain sorting by groups? ex. be able to sort individual records in a grouped view? using the [order] column as a decimal, how can the order value be automatically filled in based on the selected group?

3. Does this system work even if the end user has a filtered view of the data based on the value of [parent] column?

1billyfw
Participant II

I can't resist asking in case anyone has a quick answer. 

When you add new items (say we add a new product), the app allows you to create duplicates in Product_Sort_Number. (Whatever you set the Sort value to in the new Product Form can collide/equal a sort number already in use.)  

I'm not sure why the app, upon saving a new product, wouldn't re-sort everything below the new number in order to prevent duplicates. Any reason not to do this? 

I feel like I must be missing something since it didn't come up in this whole discussion. Perhaps the collisions are irrelevant for some reason I can't think of?

Either way, tremendous tutorial / example. 

SkrOYC
Participant V

@1billyfw wrote:

When you add new items (say we add a new product), the app allows you to create duplicates in Product_Sort_Number.


I think it's expected that you use an initial value for the Sort number (instead of allowing manual input) using something like:

COUNT([Ref].[Related_table])+1

Bulubulu
Participant V

Thanks for all that! Hoping that one day it will be implemented to make manual sorting easier with drag and drop.

That said, I'm having a problem with the "Insert item" button not appearing to complete and not working. I would like this button to be able to insert an item between two lines.

Could you please help me to make this button work?

Top Labels in this Space