How to populate a table after submitting a form

[Originws]

Hello everyone, I have created some apps with appsheets useful to facilitate my work but now I find myself unable to understand how to structure the data so that once a field in a form has been filled in, it populates a table with multiple records each with a formula.

The fields of the form are as follows:

ID (automatically generated)
DATE (the date of the test)
HRMAX (heart rate)
SPEED (speed achieved)

EX:

ID: Auto
DATA : 10-11-2023

HR MAX: 180
SPEED: 15,5

Once the form has been filled out, I would like these table to be populated in this way:

DATA                     RANGE FC       FC MIN       FC MAX
10-11-2023         70-76%              126               137
10-11-2023         76-81%              137               146
10-11-2023         81-88%              146               158
10-11-2023         88-93%              158               167
10-11-2023         93-100%            167              180

The date remains today, the ranges remain the same while FC MIN and FC MAX are calculated in this way (FCMAX =180 * 70/100) while FC MAX (FCMAX * 76/100) and so on taking the first and second as reference RANGE FC value.
Obviously I need to create a history because I might want to generate this table even after 2 months following some tests. I wonder if all this is possible with appsheet and above all how best to organize the data.

Thanks to anyone who will help me.

[WillowMobileSys]

If I understand correctly, a row is Saved in one table and based on the Save you want to add rows to a different table.  Is that correct?

There are two ways to achieve this.  Both require creating a set of actions that uses your source table to add rows to your target table.

1) Attach the actions to the Form Saved behavior of your source table Form view.  I.e. the user taps save, on an add or edit, the actions are triggered.  This runs immediately on the user device and then the results are submitted to the servers.

2) Create an automation so whenever the row is added or edited, the Bot is triggered, activating the series of actions.  This runs on the server and then the added/updated rows are brought into the app on the user device through a Sync.

For the set of actions...

I would create an action loop that iterates 5 times, once for each "Range FC" value you wish to record in the table.  To do this I would add a Counter column that you use to control the loop and when to end.  On iteration 1, add row for "Range FC' = 70%-76%, etc. 

You can get more detail how to set this up in Looping with actions

I hope this helps!

[Originws]

Hi, thanks for the reply, I tried and I think I solved it by creating a BOT that starts every time a new record is inserted in table 1 (I honestly don't know how to modify it, since if the values of this record are subsequently modified I would like the values of the 8 records in table 2 to also be modified as they are directly connected to each other) 8 rows are added in table 2 with logic set by the formulas (which depend on the values of the record inserted in table 1).

Now I have 2 problems to solve:
1) Understand how to manage in the event of changes/deletion of the values of the record in table 1. The bot allows you to manage insertion, modification and deletion but I don't know how to manage these two cases as they are connected to the record in table 1 added via the form.

2) I need to join the columns of 3 different tables creating 2 views. I tried using virtual columns with "reference" as the type but I couldn't solve it.

I'm writing the 3 tables just in case so that maybe someone can give me a hand:

T1:

NAME (Key)
DATE (Key)
HR
FCMIN% (hidden)
HRMAX% (hidden)
RANGEFC% (virtual column that concatenates FCMIN% and FCMAX%)
_computedKey -> concatenate NAME + DATE (hidden)

T2:

ID (uniqueid() )
DATE (key)
HR MAX
Speed
PROFILE -> (reference to the NAME column of T1)

T3:

ID ( UNIQUEID() ) KEY
DATE
FCMIN (hidden)
FCMAX (hidden)
VAM
Speed
MIN HR TO REACH (hidden)
MAX HR TO REACH (hidden)
RANGEFC% -> virtual column that I use to concatenate MIN FC TO REACH and MAX FC TO REACH
FC MIN - FC MAX -> virtual column that I use to concatenate FCMIN and FCMAX

I would like to create 2 views;

View 1:

RANGE FC % of the T1 table
MIN HR from table T3
HR MAX from the T3 table

View 2:

VAM of table T3
SPEED table T3
RANGEFC% of table T3

I think the second one is much simpler, while the first one I tried without success.