Problem with the virtual column and formula and 2 table

AppSheet AppSheet Q&A
1

I have 2 tables and I need to create 2 formulas for virtual columns.

Tables:

Client

-ID_Client

-Name

-Address

Records

ID_Registros

Client (Ref- ID_Client)

Date

Description

Received (Price)

Delivered (Price)

In the table “Client”, need in a virtual column, the sum of “Received” minus the sum of “Delivered”. I use this but it always results in zero

sum(SELECT(Records[Received],[ Client] =[_THISROW].[ ID_Client]))-sum(SELECT(Records[Delivered],[ Client] =[_THISROW].[ ID_Client]))

In the table “Records”, need the progressive historical result.

Use this:

sum(SELECT(Records[Received],and([Client] =[_THISROW].[ID_Client],[date] <=[_THISROW].[date])))-sum(SELECT(Records[Delivered],and([Client] =[_THISROW].[ID_Client],[date] <=[_THISROW].[date])))

But I have a problema… When there is one record per day, the results are correct.bad result when there are several records in the same day

the results are correct
the results are correct856×646 26.1 KB

bad result
bad result849×648 31.1 KB

any ideas???

2

First step: when you have a reference set up, you don’t need to re-query the entire child table, you can just query the [Related…] column. I.e., instead of SUM(SELECT( table[col] , … ) ) , you’d do SUM(SELECT( [Related…][col] , … ) ) , and then no need to match the id value. This will shorten and simplify your formulas, making everything easier to work with.

1 Like
3

Thank you very much for your suggestion.
I tried it with the first formula and it worked.

Any ideas for the second formula problem?

1 Like
4

1: (as per @Marc_Dillon )

(
  SUM(
    SELECT(
      [Related Records][Received],
      ([Client] = [_THISROW].[ID_Client])
    )
  )
  - SUM(
    SELECT(
      [Related Records][Delivered],
      ([Client] = [_THISROW].[ID_Client])
    )
  )
)

2:

(
  SUM(
    SELECT(
      Records[Received],
      AND(
        ([Client] = [_THISROW].[ID_Client]),
        OR(
          ([_THISROW].[Date] > [Date]),
          ([_THISROW].[_ROWNUMBER] > [_ROWNUMBER])
        )
      )
    )
  )
  - SUM(
    SELECT(
      Records[Delivered],
      AND(
        ([Client] = [_THISROW].[ID_Client]),
        OR(
          ([_THISROW].[Date] > [Date]),
          ([_THISROW].[_ROWNUMBER] > [_ROWNUMBER])
        )
      )
    )
  )
)