I have an orders table. Each row in the table has a priority that’s manually assigned. I’m trying to allow the user (or later, an action) to reassign priorities. The strategy is:
- Priorities are assigned by the user and can be arbitrary, though unique numbers (e.g. for the 3 orders O1,O2,O2, the priorities could be O1 P=1, O2 P=2, O3 P=7)
- Use an action to reassign these priorities in adjacent multiples of 2 (e.g. the former set becomes O1 P=0, O2 P=2, O3, P=4)
- Now a user can adjust priority by reassigining a value in between two existing priorities (e.g. adjusting O3’s priority to 1 so: O1 P=0, O3, P = 1, O3 P=2,
- Re-sort according to step 2.
The way I’ve implemented this:
- There’s a single table called Orders. Orders has these columns: row number, Key, Order number, Priority, and adjusted priority and orders has a virtual column: Temp adjusted priority.
[Temp adjusted priority] has the formula:
count(select(orders[priority], [priority] < [_thisrow].[priority]))*2
- Action: Adjust priority for this row: For a record of orders. Set data values of some columns in this row. set [adjusted priority] to [temp adjusted priority]
- Action: adjusted priority for many rows. For a record of orders. Execute an action on a set of rows. Referenced rows:
Select(orders[key], [priority] <> "")Referenced action: Set priority for this row.
I’m having problems running adjusted priority for many rows.
It works fine for data sets of 15 orders. It’s bearable for up to a couple hundred. But as soon as you get to 500+, it causes Appsheet to hang and chrome to tell you the tab has become unresponsive. I’ve put this into a totally separate app, so there’s no other actions / workflows / dependent tables getting triggered.
- The virtual column, Temp adjusted priority, is set once and costs, probably, O(N) to do all N orders.
- Adjust priority for this row is simply copying a virtual column value to real column, probably O(1) for each order.
- The filter for adjusted priority for many rows is probably O(N) for all of the orders.
It seems to me like this should be a linear algorithm. Yet, it seems to kill appsheet on relatively small databases.
Why is this so brutal?
How should I do this?