Solved: Expression Development - Page 2

ABBA · 09-05-2020 09:26 PM

This is my Table called [Tank Guage]
It has columns [Tank or Gauge Name], [Gauge-Ft], [Guage-In] [Total-Inches] etc.
There are several Tank or Gauge Name and a reading is recorded for each day of the month.
The reading on the 1 st of every month is “Beg Oil” for that tank

I am trying to calculate value of column [Production] by [Previous day Oil, Bbl] -[Todays Oil, Bbl]

Transfer happens only with Tank or Gauge Name=25294. If [Previous Day Total-Inches<Todays Total-Inches] for Tank or Gauge Name=25294, then [Transfer, Bbls] value is Previous day Oil, Bbl -Todays Oil, Bbl. Can someone help me express this in expression?

Sold, happens for all other Tank or Guage Names and similar logic follows,If Previous Day Total-Inches<Todays Total-Inches for Tank or Gauge Name=25294, then [Sold, Bbls] column value is [Previous day Oil, Bbl] -[Todays Oil, Bbl]. Can someone help me express this in expression as well please?

Steve

I recommend adding a normal (not virtual) column named (e.g.) Previous Day to the Tank Gauge table with an App formula expression of:

ANY(
  ORDERBY(
    FILTER(
      "Tank Gauge",
      AND(
        ([Date] < [_THISROW].[Date]),
        ([Tank or Gauge Name] = [_THISROW].[Tank or Gauge Name])
      )
    ),
    [Date],
      TRUE
  )
)

For the Transfer, Bbls column, set the App formula expression to:

IFS(
  ([Previous Day].[Total-Inches] < [Total-Inches]),
    ([Previous Day].[Oil, Bbl] - [Oil, Bbl])
)

These values will only be updated when the row itself is updated from a form or by an action. You’ll need to open each row in a form and save it (no changes needed) to get the computed value added. If you have too many rows to do that for, we can construct a way to do it with actions.