I have a table with around 1 million rows, and I need to calculate and update the weighted score based on project category creation. For a single project, the maximum number of rows will be 100,000.
I have three tables involved:
1. User Table โ End users provide ratings for each row based on material.
2. Total Impact Table โ Admin calculates the total impact for each relevant column.
3. Weighted Score Table โ This table contains user input columns and total impact columns. The weighted score is calculated based on these two inputs.
For example, I am using the following formula in the Weighted Score column for Supplier Spend:
IF([SUPPLIER_SPEND] = "error", "",IF(ISNOTBLANK([SUPPLIER SPEND]),SWITCH(TRUE,[SUPPLIER SPEND] <= 5, 1,[SUPPLIER SPEND] <= 10, 2, [SUPPLIER SPEND] <= 15, 3, [SUPPLIER SPEND] <= 20, 4, 5 ) * [101_IMPACT SUPPLIER SPEND],"n/a" ))
The Issue:
The values in the Weighted Score column only update when I open a row and click the edit button. However, I need to apply this calculation to all 100,000 rows at once whenever there is a relevant change in data.
What I Have Tried:
I tried using App Formula & actions
*I don't want to use a virtual column for this because it impacts performance significantly.
My Question:
Is there a way in AppSheet to update all rows at once without requiring manual edits? Any suggestions or best practices would be greatly appreciated!
LinkedIn
Twitter
