Alternatives to LOOKUP(MAX(SELECT(

Continuing the discussion from New INPUT() function — binding dynamic inputs to a data change action:


As an alternative to this approach, you could make use of MAXROW()

Reason:

LOOKUP(SELECT( is the equivalent of:

ANY(SELECT(SELECT(

- and nested select statements are a no-no


@Rifadm817 you might try the following, it should be easier on your system while accomplishing the same result:

LIST(MAXROW(Job Items, _RowNumber, [job ID] = [_ThisRow].[job ID]))


Alternatively to my alternative

  • This is the most efficient of them all

If you’ve got your tables connected with references, then on the Parent level you will have a list of [Related Job Items]

  • You can use that reference connection to de-reference that list of items, and you can easily pull the last item from the parent with:
    LIST(INDEX([Parent_Ref].[Related Job Items], COUNT([Parent_Ref].[Related Job Items])))
3 Likes

Hey, Hope you are doing good.

I actually always use LINKTOFORM() to go to form. Then do the normal data entry part. This post is just to demonstrate INPUT (). Some use cases like add to cart when user/client does not prefer moving out of one screen to another form and then coming back to same view after saving the form is really a big NO !

Clients prefer something like this always. Adding a row to inline view without changing from one view to another. Right now with INPUT() I am able to do somewhat close to the video above.

Here is another way I do it. This also take us to another screen to change everything.

1 Like

I have never tried this because I achieved everything using existing Lookup(). I will surely try. I never knew it would effect the efficiency. Does it effect the update time or sync time ?

1 Like

In this instance your original nested formulas wouldn’t, because it’s inside an action.

  • The visible effect will be, once you’ve got a larger database built up, executing that action will take longer and longer.
    • It will be like: “Tap… pause… update” where you’ll see a visible “hiccup” or pause while the app is doing a ton of calculations.

If you had a formula like that living inside a Virtual Column, then the sync time would be affected - taking longer and longer the bigger your data gets.


Using MaxRow() makes use of efficiencies built into the platform, AppSheet keeps indexes and things in the background the we’re not privy to.

1 Like

Am I understanding it wrong here or Appsheet just confusing me ? When I use MAXROW() is it basically the SELECT() ?

1 Like

I am under the impression that MAXROW is simply a wrapper for a more complex SELECT.

I have strong doubts about this statement. Do you have anything to back it up?

2 Likes

This is what I was told many years ago when working with app sheet support to increase the efficiencies of an app. I was doing things similar to nested selects and lookups - they pointed me in that direction.

I would assume things wouldn’t have changed, though I could be wrong.

Best
Matt


EDIT: but after seeing that screen shot of the horrible looking nested select from that MAXROW(), got me thinkin…

image

Literally this is what I was doing, and they told me to stop and use MAXROW()…

:rofl:

6 Likes
2 Likes

Now I do this :point_down: when I can

  • In fact, I’ll typically hold that “last whatever” record on the parent level as an enum (making it easier to de-ref things from) - but only if I need something from that reference more than once.
1 Like

Please provide a sample of this expression in MAXROW() version.