Tricky formula - counting first child of related parent

AppSheet AppSheet Q&A
1

I have tables called Clients, Food Orders and Order Manifests. The Clients table and the Order Manifests table both have Related Food Orders columns. Within each Order Manifest, I want to capture a count of the number of clients whose first-ever food order is included within this Order Manifest.

Thoughts? Much appreciated.

2

I’ve figured out one approach, but would like to know if there’s a better one.

Here’s what I did: In clients, I added a vc called First Order Manifest which stores a ref:

Lookup(MIN(
   SELECT(
     Order Manifests[_RowNumber],
     IN(
       [_THISROW].[Row ID],
      [Related Food Orders][Client]
    )
  )
),
   Order Manifests, _RowNumber, Row ID)

And added a vc in Manifests that looks for it’s own ref in clients and counts them:

COUNT(
  SELECT(
      Clients[Row ID],
      [First Order Manifest] = [_THISROW].[Row ID],
    )
)

Is there a more efficient way?

3

Maybe this

COUNT( SELECT(
  [related foodOrders][row id] ,
  [row id] = MINROW( foodOrders , timestamp/rownumber , [client]=[_THISROW].[client] )
) )
2 Likes