Update Quarter Site Score Table Value When Survey Submitted

[desmond_lee]

Hi Experts

I manage to successfully update Q1 Site Score table (In fact there are 3 other similar table like this) value base on PROJECT SITE NAME as key with this expression below.

COUNT(
FILTER(
"Test Record",
AND(
[ANSWER 1]="BARELY",[RPT Q_ID]=1,
[PROJECT SITE NAME] = [_THISROW].[PROJECT SITE NAME]),
)
)
+
COUNT(
FILTER(
"Test Record",
AND(
[ANSWER 2]="BARELY",[RPT Q_ID]=1,
[PROJECT SITE NAME] = [_THISROW].[PROJECT SITE NAME]),
)
)
+
COUNT(
FILTER(
"Test Record",
AND(
[ANSWER 3]="BARELY",[RPT Q_ID]=1,
[PROJECT SITE NAME] = [_THISROW].[PROJECT SITE NAME]),
)
)
+
COUNT(
FILTER(
"Test Record",
AND(
[ANSWER 4]="BARELY",[RPT Q_ID]=1,
[PROJECT SITE NAME] = [_THISROW].[PROJECT SITE NAME]),
)
)
+
COUNT(
FILTER(
"Test Record",
AND(
[ANSWER 5]="BARELY",[RPT Q_ID]=1,
[PROJECT SITE NAME] = [_THISROW].[PROJECT SITE NAME]),
)
)
+
COUNT(
FILTER(
"Test Record",
AND(
[ANSWER 6]="BARELY",[RPT Q_ID]=1,
[PROJECT SITE NAME] = [_THISROW].[PROJECT SITE NAME]),
)
)

But when I try to update the last table I had this error and wonder where is my mistake. Trying to update Q1 Site Score table value Q1 into Quarter Site Score matrix table. See the sample below manual key in expted value

Wonder why there is an error in this expression when I try to update the table value.m

FILTER(
"Q1 Site Score",
AND(
[RPT Q_ID]=1,
[RPT Q_ID] = [_THISROW].[ RPT Q_ID])
)

[TeeSee1]

---

@desmond_lee wrote:

[_THISROW].[ RPT Q_ID]

---

I see a space before the RPT...

[desmond_lee]

No its not about the space. That is a wrong expression to put a single data. Filter is a list and I manage to correct it. This is the correct expression ANY(SELECT(Q1 Site Score[Q1], AND([_THISROW].[RPT Q_ID] = [RPT Q_ID],[PROJECT SITE NAME]="CCNZB"))). See below comparing test result vs the manual enter data on the table

Site shows result = 59.58 is the same answer as the above table. So the action is correct just that why it does not fires at the BOT when Test Record submitted ?

The test record form submit it fires off the bot to fill the Q! Site Score form and with the chage in this vale it suppose to update the 3rd table aobe. Just does not work. I put all action in group.

Even Type Data Change

Then 6 acations. Maybe appsheet can't work on the third table that lookup into the second table

[desmond_lee · Accepted solution]

I di it. But the expression is so long for 8 sites calculation. Wonder anywhere to shorten it.

((((COUNT(FILTER(
"Test Record",
AND(
[ANSWER 1]="BARELY",[PROJECT SITE NAME]="CCNZB",
[RPT Q_ID] = [_THISROW].[RPT Q_ID])))
+
COUNT(
FILTER(
"Test Record",
AND(
[ANSWER 2]="BARELY",[PROJECT SITE NAME]="CCNZB",
[RPT Q_ID] = [_THISROW].[RPT Q_ID])))
+
COUNT(
FILTER(
"Test Record",
AND(
[ANSWER 3]="BARELY",[PROJECT SITE NAME]="CCNZB",
[RPT Q_ID] = [_THISROW].[RPT Q_ID])))
+
COUNT(
FILTER(
"Test Record",
AND(
[ANSWER 4]="BARELY",[PROJECT SITE NAME]="CCNZB",
[RPT Q_ID] = [_THISROW].[RPT Q_ID])))
+
COUNT(
FILTER(
"Test Record",
AND(
[ANSWER 5]="BARELY",[PROJECT SITE NAME]="CCNZB",
[RPT Q_ID] = [_THISROW].[RPT Q_ID])))
+
COUNT(
FILTER(
"Test Record",
AND(
[ANSWER 6]="BARELY",[PROJECT SITE NAME]="CCNZB",
[RPT Q_ID] = [_THISROW].[RPT Q_ID]))))*1)
+
((COUNT(FILTER(
"Test Record",
AND(
[ANSWER 1]="PARTIALLY",[PROJECT SITE NAME]="CCNZB",
[RPT Q_ID] = [_THISROW].[RPT Q_ID])))
+
COUNT(
FILTER(
"Test Record",
AND(
[ANSWER 2]="PARTIALLY",[PROJECT SITE NAME]="CCNZB",
[RPT Q_ID] = [_THISROW].[RPT Q_ID])))
+
COUNT(
FILTER(
"Test Record",
AND(
[ANSWER 3]="PARTIALLY",[PROJECT SITE NAME]="CCNZB",
[RPT Q_ID] = [_THISROW].[RPT Q_ID])))
+
COUNT(
FILTER(
"Test Record",
AND(
[ANSWER 4]="PARTIALLY",[PROJECT SITE NAME]="CCNZB",
[RPT Q_ID] = [_THISROW].[RPT Q_ID])))
+
COUNT(
FILTER(
"Test Record",
AND(
[ANSWER 5]="PARTIALLY",[PROJECT SITE NAME]="CCNZB",
[RPT Q_ID] = [_THISROW].[RPT Q_ID])))
+
COUNT(
FILTER(
"Test Record",
AND(
[ANSWER 6]="PARTIALLY",[PROJECT SITE NAME]="CCNZB",
[RPT Q_ID] = [_THISROW].[RPT Q_ID]))))*2)
+
((COUNT(FILTER(
"Test Record",
AND(
[ANSWER 1]="MODERATELY",[PROJECT SITE NAME]="CCNZB",
[RPT Q_ID] = [_THISROW].[RPT Q_ID])))
+
COUNT(
FILTER(
"Test Record",
AND(
[ANSWER 2]="MODERATELY",[PROJECT SITE NAME]="CCNZB",
[RPT Q_ID] = [_THISROW].[RPT Q_ID])))
+
COUNT(
FILTER(
"Test Record",
AND(
[ANSWER 3]="MODERATELY",[PROJECT SITE NAME]="CCNZB",
[RPT Q_ID] = [_THISROW].[RPT Q_ID])))
+
COUNT(
FILTER(
"Test Record",
AND(
[ANSWER 4]="MODERATELY",[PROJECT SITE NAME]="CCNZB",
[RPT Q_ID] = [_THISROW].[RPT Q_ID])))
+
COUNT(
FILTER(
"Test Record",
AND(
[ANSWER 5]="MODERATELY",[PROJECT SITE NAME]="CCNZB",
[RPT Q_ID] = [_THISROW].[RPT Q_ID])))
+
COUNT(
FILTER(
"Test Record",
AND(
[ANSWER 6]="MODERATELY",[PROJECT SITE NAME]="CCNZB",
[RPT Q_ID] = [_THISROW].[RPT Q_ID]))))*3)
+
((COUNT(FILTER(
"Test Record",
AND(
[ANSWER 1]="MOSTLY",[PROJECT SITE NAME]="CCNZB",
[RPT Q_ID] = [_THISROW].[RPT Q_ID])))
+
COUNT(
FILTER(
"Test Record",
AND(
[ANSWER 2]="MOSTLY",[PROJECT SITE NAME]="CCNZB",
[RPT Q_ID] = [_THISROW].[RPT Q_ID])))
+
COUNT(
FILTER(
"Test Record",
AND(
[ANSWER 3]="MOSTLY",[PROJECT SITE NAME]="CCNZB",
[RPT Q_ID] = [_THISROW].[RPT Q_ID])))
+
COUNT(
FILTER(
"Test Record",
AND(
[ANSWER 4]="MOSTLY",[PROJECT SITE NAME]="CCNZB",
[RPT Q_ID] = [_THISROW].[RPT Q_ID])))
+
COUNT(
FILTER(
"Test Record",
AND(
[ANSWER 5]="MOSTLY",[PROJECT SITE NAME]="CCNZB",
[RPT Q_ID] = [_THISROW].[RPT Q_ID])))
+
COUNT(
FILTER(
"Test Record",
AND(
[ANSWER 6]="MOSTLY",[PROJECT SITE NAME]="CCNZB",
[RPT Q_ID] = [_THISROW].[RPT Q_ID]))))*4)
+
((COUNT(FILTER(
"Test Record",
AND(
[ANSWER 1]="FULLY",[PROJECT SITE NAME]="CCNZB",
[RPT Q_ID] = [_THISROW].[RPT Q_ID])))
+
COUNT(
FILTER(
"Test Record",
AND(
[ANSWER 2]="FULLY",[PROJECT SITE NAME]="CCNZB",
[RPT Q_ID] = [_THISROW].[RPT Q_ID])))
+
COUNT(
FILTER(
"Test Record",
AND(
[ANSWER 3]="FULLY",[PROJECT SITE NAME]="CCNZB",
[RPT Q_ID] = [_THISROW].[RPT Q_ID])))
+
COUNT(
FILTER(
"Test Record",
AND(
[ANSWER 4]="FULLY",[PROJECT SITE NAME]="CCNZB",
[RPT Q_ID] = [_THISROW].[RPT Q_ID])))
+
COUNT(
FILTER(
"Test Record",
AND(
[ANSWER 5]="FULLY",[PROJECT SITE NAME]="CCNZB",
[RPT Q_ID] = [_THISROW].[RPT Q_ID])))
+
COUNT(
FILTER(
"Test Record",
AND(
[ANSWER 6]="FULLY",[PROJECT SITE NAME]="CCNZB",
[RPT Q_ID] = [_THISROW].[RPT Q_ID]))))*5))
/DECIMAL
((COUNT(SELECT(TEST RECORD[PARTICIPANT`S SITE NAME], ( [_THISROW].[RPT Q_ID] = [RPT Q_ID]), TRUE)))*((5)*(6))*3)
)*100

The same expression but just change the rest of the 7 site at this location [PROJECT SITE NAME]="CCNZB"......