Alternatives to LOOKUP(MAX(SELECT(

[MultiTech]

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

New INPUT() function --- binding dynamic inputs to a data change action

Here is the formula to lookup the last row just added.

---

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

• MAXROW() | AppSheet Help Center

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

[Rifad]

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.

Add multiple items in a much easier way? Questions

I would suggest best way to do it is something similar to quick edit view inside a detail view. When we press add button instead of taking us to next screen there should be a way to stay in same screen and add products to the inline table. I Have been facing issues when my clients ask where is this app taking me to. Like they are confused where they are suddenly after click… click… click. and it keeps on changing from one view to other. The best way we could do this is by having quick edit and a…

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

[Rifad]

MultiTech_Visions:

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

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 ?

[MultiTech]

Rifadm817:

Does it effect the update time or sync time ?

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.

[Rifad]

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

[Marc_Dillon]

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

MultiTech_Visions:

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

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

[MultiTech]

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…

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

[Swoopy]

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

[Rifad]

Appsheet Apps Speed Questions

My general feedback is to go easy on format rules, both for a good user experience and also for performance. Expressions in format rules should be simple — avoid SELECT or its derivatives (like FILTER and MAXROW) I’d be curious to see your app and figure out if there are obvious ways we could improve its performance. Do you mind sending the details to support@appsheet.com. Thanks

[MultiTech]

MultiTech_Visions:

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

Now I do this when I can

MultiTech_Visions:

LIST(INDEX([Parent_Ref].[Related Job Items], COUNT([Parent_Ref].[Related Job Items])))

• 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.

[mcopp0315]

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

[MultiTech]

@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))
    or
    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.

[Roger_Dalomba]

I also have this issue…my sync time is getting longer and longer…I need an alternative.

this is in a format rule to lookup duplicate value when entering another record.

ISNOTBLANK(
FILTER(
“MASTER LOG”,
(AND([STATUS]<>“CANCELLED”,NOT(CONTAINS([STOCK #], “INC”)),[_THISROW].[STOCK #] = [STOCK #]))
)

• LIST([_THISROW])
  )

any help with this?

[Marc_Dillon]

Roger_Dalomba:

my sync time is getting longer and longer

this is in a format rule

I don’t believe that expressions in Format Rules have any affect on sync times. They just affect how long a view takes to load onto the screen when you switch to it.

Use the performance analyzer to see exactly where your long sync times are coming from.

[Roger_Dalomba]