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()


LOOKUP(SELECT( is the equivalent of:


- 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])))

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?


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.


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


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



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.

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

1 Like

I would also greatly benefit from an example alternative to MAXROW()!

1 Like

@Swoopy @mcopp0315

The alternative to the MAXROW() formula is really dependent on the goal of the formula.

  • If I were trying to find the last item entered into an order…

    • On the Orders table, I would already have a reverse reference of all the [Related Order_Items]

    • So really what I need to do is get the last item out of that list…
      INDEX([Related Order_Items], COUNT([Related Order_Items]))

    • If I were trying to find the first item of the order - alternative to MINROW()
      INDEX([Related Order_Items], 1)

  • If I were trying to find the “oldest order still pending shipment” - another alt to MINROW()

    • I would have created an “Orders_Pending_Shipment” slice - to hold a list of all the orders that are still in the “pending shipment” status

    • So really all I need to do is put this slice in order by timestamp, and pull the first out out of the list.
      Index(OrderBy(Orders_Pending_Shipment[OrderID], [Order_Timestamp]), 1)

    • If were trying to find the newest order still pending shipment
      INDEX(OrderBy(Orders_Pending_Shipment[OrderID], [Order_Timestamp]), COUNT(Orders_Pending_Shipment[OrderID))
      Index(OrderBy(Orders_Pending_Shipment[OrderID], [Order_Timestamp], true), 1)

It really comes down to finding ways to ‘store variables’ - finding a way to store the result of a formula - instead of just re-doing the formula over and over.