This has been on my list of things to do for awhile now, finally getting around to it. (^_^)
Here's a video that was originally released for my Patreon supporters, which shows a complete implementation of this process.
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
How to add buttons to a table view to allow for moving items up/down in that list.
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.
Summary
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
In addition, you’ll need to adjust the REF_ROWS() formula on the Order table
Summary
Check out the [Related Order_Line_Items] VC in the Orders table
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
The final bit of the puzzle is to conform your table view
Summary
Check out the Order_Line_Items_Inline view
Hope it helps! Let me know if you have any questions.
Let’s say I have only one table (Order_Line_Items) and no other ref tables.
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.)
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.
When a user taps one of the buttons, to move something, they’re physically modifying that record in the database.
Meaning one person will be creating/modifying/deleting the records (at any one time).
(Or at least they should coordinate with each other while working.)
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.
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.
Is it necessary to have parent child reference connection to use this trick?
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?
Is it necessary to have parent child reference connection to use this trick?
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 actions “Ref 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.
[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])
Table[Key]
-
split(
left(
concatenate(Table[Key]),
find(
[Key],
concatenate(Table[Key])
) + 7
), " , "
) - list("", [Key])
[Line_Item_Order_Link].[Related Order_Line_Items]
was substituted for Table[Key]
[LineItemID]
was substituted for [Key]
list(index(
Table[Key],
count(
split(
left(
concatenate(Table[Key]),
find(
[Key],
concatenate(Table[Key])
) + 7
), " , "
) - list("")
) - 1
))
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?
ORDERBY()
in the Ref_Rows()
VC on the Orders table.~ The ORDERBY() Formula ~
In addition, you’ll need to adjust the REF_ROWS() formula on the Order 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.”
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])
ORDERBY()
wrapped around your table call… that’s the equivalent of a SELECT()
statement.
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.
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
Very clear. That’s what I wanted to know. I will try and let you know. Thank you
Reminds me of this feature request:
I’m finding myself having to repeat an ORDERBY() for a slice over and over again - I can’t help but think of all the cycles wasted in this repetition during sync/calculation. If I had the ability to order the rows of a slice AT THE SLICE LEVEL - then I wouldn’t need to worry about the sort order for any of my views, when using any formulas I know the rows will be in the order I’m expecting, etc. The same could be said for regular data sources as well - being able to order rows at the table le…
@Mark_S You’re welcome. (^_^)
Thanks for inspiring the update!
man thank you so much for this it is much appreciated! You must have spent a ton of time on this.
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.
I want to group items and sort within the group.
@Mark_S This get’s much more complex, but interestingly enough it’s actually how the original implementation I made works… in a way.
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.
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])
ORDERBY(Order_Line_Items[LineItemID], [Line_Item_Sort_Number])
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)
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])
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:
It uses the RANDBETWEEN()
expression.
I use it in my App
(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.
I don’t see any performance problems.
Watch how long it takes when you tap an item before it’s moved…
Compared to the responsiveness of my original gif (which is actually modifying 2 records, not just one)
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.
SELECT()
you may run)
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.
That is amazing thank you
After some experimentation, I’ve found that if REF_ROWS() is the entirety of the column’s App formula expression, the Add button in the related inline view will auto-fill the column referenced in the REF_ROWS() expression in the new row. To retain this magic, the REF_ROWS() results cannot be modified in any way, such as by list addition or subtraction, or with SORT() or ORDERBY(). Magic: REF_ROWS(...) (REF_ROWS(...)) Not magic: REF_ROWS(...) + LIST(...) REF_ROWS(...) - LIST(...) SORT(REF_R…
@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?
Any workaround to retain both the magical part of REF_ROWS and manual sorting?
Make your own action to create a child record with the parent value pre-populated
Move the ORDERBY()
out of the REF_ROWS()
and instead order everything inside the formula use
ORDERBY()
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?
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.
@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
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?