How to cache a calculation and use it in another column!

[Atlas]

So, I think I have really big LOGIC problem or may be there is some feature of appsheet that I miss out

I am working on a progress payment report app based on the inspections on construction sites.
I have a few calculations based on the other columns and calculations.

I don’t want to take much time of you, so I will try to explain without going deep just giving the formulas that I used.

In ny tables LOGEMENTS and P.P is predefined and user just enter FINIS column manually. So 1. and 2. formula returns the result instantly. But 3. formula dosen’t show up instantly because it sums up the P.PROGRESS column values if there are another matching values in APLICATION. But without saving and reopening the same form it doesn’t calculate A.PROGRESS
How can I “cache” P.PROGRESS column that will be sum, and return the result in instant?

1.FORMULA FOR PROGRESS COLUMN

[FINIS] / [LOGEMENTS] * 
                 LOOKUP([_THISROW.[PRODUCTION],
                 "PRODUCTIONS","PRODUCTION",
                 "P.P")

2.FORMULA FOR P.PROGRESS COLUMN

[PROGRESS] / LOOKUP ( [_THISROW].[PRODUCTION],
         "PRODUCTIONS", "PRODUCTION" , 
         "P.P" )

3.FORMULA FOR A.PROGRESS COLUMN

     3) SUM(
        SELECT(DATA[PROGRESS],
               [APLICATION]=_THISROW].[APLICATION]
/LOOKUP([THISROW].[APLICATION],
        "APLICATIONS","APLICATION",
        "A.P")
/COUNT(
        SELECT(PROJECTS[PROJECT],
               [PROJECT]=[_THISROW].[PROJECT]))

@praveen @Peter @AppSheet_Manager

[AlexM]

Is 3 in a virtual column?

[Atlas]

Nope, it is real.

[AlexM]

add a virtual one and try it

[Atlas]

@AlexM Thank you for your suggestion. İt calculates without editing but I need that calculation in my spreadsheet. How can I achive to add tihs real column?

[AlexM]

Just an idea, never tried it.

Have an action to set this column to …formula… and set it to run when the form is saved.

@Aleksi was saying something about this a while ago but can’t remember exactly what and how.

[Atlas]

Okay now, using “set the values of some columns in this row” action button writes the values. But it is still require user to finishing the form and reenter the form again for to click action button. Is it possible to de-ref or another expression to make this process automatically…

Thank you for sharing your time…

@Aleksi @AlexM

[Aleksi]

@Atlas May I ask why don’t you then calculate it with the normal column?

[Atlas]

Hi @Aleksi Thank you for attention

A.PROGRESS column has SELECT() and SUM function. That brings the value from another table and divides it with the SUM of selected value . The expression itself works fine. When I use this formula in normal column the calculated result doesn’t update automatically unless I edit. When I press edit, then I see the updated expression result.

I think, the app doesn’t work as the same logic has spreadsheets, it required to save and retrieve information back…

And this brings in minds another question! It seems, appsheet and spreadsheets formulas behavior very different.In the spreadsheet the formula is dynamic, so the formula brings the sum of matched records with the condition and when I add new records values and it recalculates it and brings the correct value.
In appsheet values don’t update it self only if I click edit. So if you have a formula that returns based on the other column values it required to click edit and update all time. So the whole work and app becomes useless.

The first problem is to write calculated cell to spreadsheet.
The second problem is to make automatic updates for rows without clinking the edit button…

Does it achievable?

[Aleksi]

If that’s the case, then you should update the record with an action as “Execute an action on a set of rows”. Think about a case where you have some kind of reference between records… when the child record is added or updated, the parent record will updated as well. I believe it’s something you are looking for.

[AlexM]

That’s the one I was remembering!

[Atlas]

I will try to write an expression for this action and will inform you about the process,

Thank you again.
@Aleksi @AlexM

[Aleksi]

You’re welcome

[Atlas]

In the end, I did’t figure it out how tot update the records. In app, when I add a new record or when I make an update, specific columns in all records needs an update, to add the correct values to the spreadsheet.

columns to be update

How can I make this work? Could you please guide me to create this workflow.
Thank you in advance.

[yashbansal1000]

IF(OR(B28=0, B1=1),B27,B28)
B28 is the resultant cell.
B1 is the cell containing 1(recalculate) or 0(use the cached one)
B27 can be replaced by the formula referencing other things.

I believe this shall do. Can be added as a property in the GPT functions as well probably.