Curious how you handle copying to create a child l...

WillowMobileSys · 10-20-2020 04:05 PM

in my use case I have an Order table with an Order ID. A child table is Order Details that lists the products include in the order. I then have a Saved Orders table used to pre-populate the Order Details with recurring/frequent orders.

When a Saved Order is selected, I copy the product details to the Order Details table and then need to update those Order Detail rows with the parent Order ID of the current order being constructed.

Since we do not have an “UPDATE rows in another table with values from this row” type of action, I am wondering how any of you might handle the updating of the parent Order ID value into the newly created child rows?

So far, the most efficient, least intrusive way I can think of is to use a simple small table to capture the Order ID value just before the copy function and then retrieve that value from the table during the copy to be added to each new row.

Any better way?

Bahbus

I personally use CONTEXT(“Device”) to filter out Order IDs that don’t belong to that device/user, and I make the initial value of the Order ID in the Order Details the most recent, open Order ID from Order table. And if there aren’t any Order IDs to use, add new row to Order table first. But that is what works for my setup.

Marc_Dillon

I do like utilizing small “global variable” tables in a few of my apps. I wonder, how are you saving the value though, and then how do you retrieve it?

Along the lines of @Bahbus suggestion, a simple MAXROW( Orders , “_rownumber” ) should retrieve the appropriate Order ID for you in most cases.

Saving and retrieving the value would certainly be less expensive on a small table as you described, than a main data table.

Here’s another option:

Have a [new order id] column in whatever table you are triggering the copy action from. As the first action in your grouped action, set this column to UNIQUEID(). Then use that saved value as the Order ID for both parent and child. This method may not be possible for certain setups though.

Bahbus

That’s precisely what I do, actually, in combo with the CONTEXT(“Device”), and a DateTime column instead of rownumber.

In my setup, it’s acting as a menu/checkout cart on a tablet available to any customer. It doesn’t take any payment or anything. It’s merely to make it easy to communicate what you want to order if you’re one of those people who either don’t want to talk to employees more than you have to, or easily forget everything it is that you want, or whatever, and totally optional. I have it send everything via Discord to the register and backroom computers.

Anywho, point of that little story is that my setup works great because if someone starts a cart, it then just each new item is prepopulated with the parent. Using Initial Value comes with the added benefit being that is that person then walks away without checking out through the app, the next person can easily say, “that’s not me” click dropdown click new enter new name - which is obviously a new parent ID in my case. And now the next item they try to add to the cart is now prepopulated with the new person. Also preserves the original customers cart in case they come back later. Auto delete all carts at EoD.

Edit: Oh, and I actually also always go straight to the child order detail regardless of if there is already a parent. So…I dunno…I guess it depends on what all your setup is.

WillowMobileSys

Any idea what the processing expense is for that operation? If the table is indexed by _rownumber then it would be an acceptable approach. but if it need to search for the maximum _rownumber each time, it would not.

My plan is to use a Grouped action on Form Save of the Order. First action saves Order ID to “utility” table. Second action kicks off the copy of the “template” rows. The underlying action that adds the new row will simply assign the Order ID from the “utility” table.

My main concern is that this app is used mostly by browser and I am still not 100% on how the data between the browser and the server is handled. For instance, what happens if a Sync is issued while the Form Save action is still being processed. Or user is saving Orders in two browser tabs. Just haven’t had time to test these things out.

Probably my biggest issue is that it seems no matter what approach we take for this use case we are making certain assumptions that we will get the expected value -

whether its by a MAXROW() function or by using a “utility” table. These seem fragile. I much more like the concrete approach - “take this value from this row and place it into that row”

WillowMobileSys

@Steve I was just looking at the MAXROW() documentation which you have so graciously put together for all of us. Do you happen to know the processing expense of MAXROW() when using the [_rownumber] column? Is it O(1)? Or alternatively, do you know if the [_rownumber] column is indexed?

Steve

I don’t know the answer to either question.

I do know MAXROW() and MINROW() are effectively macros around nested SELECT() expressions, so they are heavy. I suspect ORDERBY() is a more efficient way to get a maximum or minimum:

ANY(
  ORDERBY(
    FILTER(
      "table",
      selection-exp
    ),
    [max-target],
      TRUE
  )
)

See also:

WillowMobileSys

@Steve @Marc_Dillon @Bahbus

Ok so I did some performance testing on my use case to compare using a utility table versus using MAXROW(). The timings were all very consistent. This test was adding an Order that copies 10 template rows into the Order Detail records. The timings were all in the neighborhood of 1:15 from the time the Order row appeared until the final child row was inserted. This was in a non-deployed app.

In between MAXROW() trial runs I added 1000 Order rows (later a second 1000 Order rows). These did not have any impact so this usage of MAXROW() using _rownumber appears to be taking advantage of indexing. But this may not be the case for all instances of MAXROW() against developer issued columns.

There was one big issue with the MAXROW() usage. In one of my trials, MAXROW() picked up the Order ID from the previous order instead of the current order. Since I was watching the row insertions, what I noticed is that there was a delay between when the sheet row was inserted and the row data appearing. I can only assume that delay meant the new Order row wasn’t available at the time MAXROW() ran.

Even more unexpected, each copied child row STILL received that previous Order ID even though the current Order row was indeed inserted by the time the 2nd child was copied. Could the MAXROW() result have been cached?

On the other hand, in the “utility” table approach I am adding the current Order ID as a new row and then deleting it after the children are copied. Though this seems closer to a concrete approach, it can still suffer problems if the row add fails. I did run a test by adding 6 different Orders back-to-back such that the last one was entered before the first completed. I had no issues but this doesn’t mean there won’t be at some point.

I see benefits of both approaches. MAXROW(0 is easy and just as performant. But since the “utility” table method seems closer to a more ideal solution and I feel I have more control over potential issues, I will be implementing that approach instead.

Thanks to you all for your input!!

Bahbus

Potential way to ensure that the orders at least don’t get the wrong parent ID: add a Yes/No to the parent on whether or not it has been completed and “checked out”. You could then filter out past completed orders, if also combined with CONTEXT(“Device”), you should be able to either get the right result or at the very worst, a blank result - which is probably still better than the wrong result.

Curious how you handle copying to create a child list and then update with Parent ID?