How to simplify a LIST / INDEX app formula?

AppSheet AppSheet Q&A
,
1

I’m after ideas / methods / hints to simplify the following App Formula.
The result I want is a list of nearest 5 row references from the WAITING_LIST table where the CONTAINS criteria are met for each row_ref.
TIA

Screenshot 2024-08-21 at 11.10.10.png
Screenshot 2024-08-21 at 11.10.10.png1708×1516 203 KB

The formula below works - but seems like it’s the long way round.
Can I simplify the app formula that creates the list of row_refs?

LIST(
INDEX(
  ORDERBY(
    FILTER(
      "WAITING_LIST",
      CONTAINS([Related SECTIONs][_sectionType],[_suggestedSection])
    ),
    DISTANCE(
      [_memberPostcode_LatLong], 
      [groupLatLong]
    )
  ),1),
INDEX(
  ORDERBY(
    FILTER(
      "WAITING_LIST",
      CONTAINS([Related SECTIONs][_sectionType],[_suggestedSection])
    ),
    DISTANCE(
      [_memberPostcode_LatLong], 
      [groupLatLong]
    )
  ),2),
INDEX(
  ORDERBY(
    FILTER(
      "WAITING_LIST",
      CONTAINS([Related SECTIONs][_sectionType],[_suggestedSection])
    ),
    DISTANCE(
      [_memberPostcode_LatLong], 
      [groupLatLong]
    )
  ),3),
INDEX(
  ORDERBY(
    FILTER(
      "WAITING_LIST",
      CONTAINS([Related SECTIONs][_sectionType],[_suggestedSection])
    ),
    DISTANCE(
      [_memberPostcode_LatLong], 
      [groupLatLong]
    )
  ),4),
INDEX(
  ORDERBY(
    FILTER(
      "WAITING_LIST",
      CONTAINS([Related SECTIONs][_sectionType],[_suggestedSection])
    ),
    DISTANCE(
      [_memberPostcode_LatLong], 
      [groupLatLong]
    )
  ),5)
)
2

It sounds that you are looking for TOP() function?

In that case, please try the below expression:

TOP( ORDERBY(
FILTER(
“WAITING_LIST”,
CONTAINS([Related SECTIONs][_sectionType],[_suggestedSection])
),
DISTANCE(
[_memberPostcode_LatLong],
[groupLatLong]
)
) , 5)

TOP() - AppSheet Help

2 Likes
3

Genius - not come across that function before. Thank you, replaced my app formula perfectly.

1 Like
4

You are welcome.