Solved: Expression Development

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.

See also:

View solution in original post

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.

See also:

ABBA

Thank you so much @Steve for helping with development of the expression.
I understand the development and logic.
I am getting the parenthesis error

I dont see how this could happen

Steve

I missed one. I’ve corrected my earlier post.

ABBA

@Steve
We forgot to add the condition that Transfer is calculated only when (25293=[Tank or Gauge Name])

I get the Transfer Bbls by
IFS(
([Previous Day].[Total-Inches] < [Total-Inches]),
([Previous Day].[Oil, Bbl] - [Oil, Bbl])
)

I did it by adding a
IF((25293=[Tank or Gauge Name]),
IFS(
([Previous Day].[Total-Inches] < [Total-Inches]),
([Previous Day].[Oil, Bbl] - [Oil, Bbl])
),"")

ABBA

@Steve @Marc_Dillon @_Excelrati would like to pre-polulate the Form for Tank Table, with data from Previous Day when the App User decides to Add New with the + sign
Can you please show me how we can achieve this

ABBA

@LeventK Bump