App formula should be calculated during 1st update...

Neeraj_Malik · 01-15-2021 05:10 AM

Dear Friends,
Once again looking for help

This is my table structure

Only Column A to I are being entered with [Status]=Pending at the time of adding a row and other columns shall remain blank

Then only columns M to Q are entered/update during 1st Update with [Status]=Allocated where
[Payment ID 1] is ref type getting values from another slice where [ZWL Amount Available 1] is greater than zero
[ZWL Amount Available 1] and [ZWL Rate 1] are lookup value against the [Payment ID 1]
[ZWL Paid 1] has pp formula and [USD Equivalent 1] has spreadsheet formula

During 2nd update column, AJ onwards are to be updated by updating the [Status]=Received, without changing the existing value of any of the exiting column i.e columns A to Q

The issue here is, during 2nd update the app formulas recalculate everything. And bigger challenge is that the calculation is based on the updated [ZWL Amount Available 1] which got updated during 1st update. Can’t use the formula as initial value also b’s of the same reason.

Looking for suggestion to handle this scenario

Steve

Wrap your existing App formula expression with something like this:

SWITCH(
  [Status],
  "Pending",
    ...expression to evalute for pending...,
  "Allocated",
    ...expression to evalute for allocated..,
  [_THIS]
)

The [_THIS] at the end is the default value to set if [Status] isn’t matched, which effectively keeps the existing value unchanged.

There are also other approaches.

Neeraj_Malik

Thanks @Steve, I was not aware of this switch expression
It worked for most of the columns, but pls help me to understand how to get a similar result for the column with data type ref.
In my given scenario column [Payment ID 1] getting Payment ID values from a slice that has Payment ID with [ZWL Amount Available] more than zero. So, if the [ZWL Amount Available] becomes zero in the slice for the payment ID being used then that Payment ID gets removed from the column [Payment ID 1] and accordingly all the values of corresponding columns with lookup expression and other related calculations sets to blank/zero.

Steve

I don’t understand this.

Neeraj_Malik

@Steve
there is a slice maintaining Payment IDs and its Amount Available against that Payment ID, accordingly filters the list of Payment IDs having Amount Available more than zero
During 1st update [Payment ID] value is selected from this list of Payment IDs having Amount Available more than zero. And all other lookup values against this selected Payment ID in other corresponding columns.
There will be a state when this selected Payment ID will have Amount Available as zero. During that time if we try to execute 2nd update with [Status]=Received, then this payment ID doesn;t show up in the same row where it was selected earlier and then other corresponding lookup values sets to blank despite of this switch expression.

Not sure if I could explain it well now

Neeraj_Malik

@Steve
I’m realizing now that the challenge is not on the column [Payment ID] that has ref type, it’s basically with all the column that has lookup expression. Everytime I try to update any other column, app formula with lookup expression recalculate everything and values get changed accordingly. So this Switch expression is not of help in my case.
Can you suggest any other approach pls?

App formula should be calculated during 1st update but not the subsequent updates