Form initial value with SUM(SELECT and new entries

hello everyone i have been using appsheet for a coupple of weeks now and i have been struggling with this situation:

i have one table that keeps in each row one percent completion of various tasks for a single inspection, this table has one column for each task, and of course a date column. but the users never fill every task, cause only some of them have advance on each inspection. (its a construction inspection, so, someday register the % of completion in the walls, other day the ceilings and the rest of the walls etc…)

i have a table view that shows all the inspection for one proyect, but its a little hard to check the total % advance cause i cannot have subtotals in that view ( i saw and tried a lot of the methods to reach the subtotals that are posted in the forums but at the end i created a 2nd table with virtual columns that sum(select the totals for each inspection and used that like another view.

Still, In the form for a new inspection, i want the user can check easily how much its the accumulated % for each task (to avoid changing to the totals views for any check), to do that i used a formula in the initial value of each task that sum all the inspections percents registered in the past inspections, if by example i have 3 inspections for one task for 30% completion each one, the form will have a 90% as initial value and when the user wants to do the 4th inspection, with that info in the form they can easily set that task to 10% in that inspection and close the 100%. the problem comes when i have previous tasks completed or other tasks with some value diferent from 0% cause the initial value of all the tasks will have a “new value” for that new inspection (that cames from the initial value sum-select), if i save the new register of that new inspection it will add that new value (the result of the sum of all the older inspection) to the total and of course im going to get tasks with more than 100%.

basically what i need to know if there is a way to put the sum(select in the initial value, as a kind of form placeholder, like the HTML ones, that shows some information in the form but doesn’t keep it as a record when the form is saved. if anyone has a diferent approach or any way to do that will be very very helpfull.

1 Like

i got a kind of illumination! I ve used this

concatenate(“Habilitación “,round(sum(select(ejecucion[A_Habilitacion], [ID_Benef]=[_thisrow].[ID_Benef])))*100,”%”)

in the description field of the table for one task, and got this on the form:

it shows the sum in the name of the field now, i can live with that.

edit: i erased the round() cause it was rounding evething to 1 and then lock the bar to increase in 10% steps so i dont get weird or long decimals in the name

You can try having a Show column type thats shows that %, so it’s not a field, but it’s info that you need the user to see

I’m gonna read this again another day to help you a little bit more since I don’t have much time right now.
Have a look at the link for the moment


Ill give it a shot, lets see what i can do, thanks for the reply!

1 Like