An app to demonstrate how to do manual sorting
The purpose of this app is to demonstrate all the elements needed to make an inline sorting adjustment system. ____ It requires 14 unique actions & a virtual column (to serve as a Button_Holder inside the table view). - When complete, inside the table of [Related Order_Line_Items] you have a system of buttons available that allow you to move things up/down in the list - as well as inserting an item below the one you tap.
Also see: tablet mode & fullscreen mode
How we built this app
~ The Virtual Column ~ On the Order_Line_Item table, there is a virtual column named [Button_Holder]; this column serves the only purpose of holding the buttons inside the table view. ___ ~ The Actions ~ There are 14 unique actions that make up the system: - Four core actions - Four reference updating actions - Four visible buttons - In addition there is a pair of actions to reset the sorting numbers when things get out-of-control ___ ~ The Magic ~ The core of how the Ref actions work is through a unique formula: `````` split( left( concatenate([Line_Item_Order_Link].[Related Order_Line_Items]), find( [LineItemID], concatenate([Line_Item_Order_Link].[Related Order_Line_Items]) ) + (len([LineItemID] - 1) ), " , " ) ````` This formula finds the location of the item in question INSIDE the list of [Related Order_Line_Items]. If you follow the formula, you can read it as the following: - Split for me, -- only the LEFT() side of... -- The CONCATENATE() of all the [Related Order_Line_Items] from this record's parent -- Give me only the left side of that UP TO... --- FIND() for me, the ID of this record inside that list of related records from the parent --- and + the length of the ID value (minus 1, since we automatically find the first character) -- So we've got a list of all the [Related Order_Line_Items] IDs from the parent, and we've now cut things off using LEFT() right at the end of the ID for the record we're running this formula on. - Now take that list and split it base on "space comma space" - the default comma separator used by the system - turning the text string back into a list (so the system can work with it). ____ This gives us a list of all the items UP TO THE ONE YOU'RE RUNNING THE FORMULA ON. - Taking the master list and subtracting THIS list from it gives you everything that comes AFTER the item - Wrapping this in a COUNT() gives you the position of that item in the list - Subtracting one (from that index position number) gives you the item BEFORE this one (without having to do a hard LOOKUP(), SELECT(), MAXROW(), MINROW(), etc.). ____ ____ All of this may look intimidating, or like it would be taxing on the system; but in fact it's the exact opposite. This formula is simply doing some text-manipulation - and that's super fast - as opposed to doing a LOOKUP() or something over the entire table.
This is a preview of the data sets used in the app.
This is a high-level model of both the data entities and the UI elements in the app.