Help with Dereferencing a dereference

AppSheet AppSheet Q&A
1

I have three tables with the below columns.

I am trying to filter and add all the purchases for the day that are allocated to food cost in the Ref Stock Items Table.

I have the following formula

SUM(SELECT(Line Items[Cost],AND([Banking Date]=Today(),[Item].[ITEM].[Type]=“Food Cost”)))

But I get the following error

Column ITEM in expression ‘[Item].[ITEM].[Type]’ does not contain a reference.

Can someone help me with the correct dereference?

Tables and their Row

  1. Banking for the Day

_RowNumber Number

Timestamp DateTime

Banking ID Text

Credit Card ID Text

Banking Date Date

TOTAL TURNOVER Price

Less credit cards Price

Deliveries Price

Sub Total Price

Less cash food cost Price

Banking

  1. Line Items

_RowNumber Number

Timestamp DateTime

Invoice No Text

Date Date

Supplier Ref

Item Ref

Quantity Number

Price per Unit Price

Cost Price

  1. Ref Stock Items

ITEM ID Text

ITEM Text

Supplier Text

Туре Text

Price Text

Related Line Items List

2

You can only dereference the value of a column of type Ref. The only column of type Ref I see in your app is the Item column of the Line Items table. It appears that column refers to the Ref Stock Items table, which has an ITEM column that is not of type Ref. In order to dereference [Item].[ITEM], the ITEM column of the Ref Stock Items table must be of type Ref. That said, I don’t think you have a need to dereference TEXT. Try this:

SUM(
  SELECT(
    Line Items[Cost],
    AND(
      (TODAY() = [_THISROW].[Banking Date]),
      (“Food Cost” = [Item].[Type])
    )
  )
)
2 Likes
3

Thanks Steve. I think that worked.

Really appreciate the assistance. Hopefully I’ll get the hang of this sometime soon.

2 Likes