Cannot Find My Mistake Change Value 0 to 1

desmond_lee · March 26, 2025, 9:58pm

Hi Expert

I am trying to find where is my mistake that the value of [TRACKER] should be change from 0 to 1 and if the value is 1 it will remain as 1. Example [PROJECT SITE NAME] CCNX subjected the form from the PROJECT LIST table the value of [TRACKER] will update from 0 to 1 or remain as 1 if its at 1 state. Why site CUSF and CMXJ cannot update to 1? I can’t anything wrong with my expression

SWITCH(
[PROJECT SITE NAME],
“CCNX”,
IF(
AND(
COUNT(SELECT(Submission[CCNX], [CCNX] = 1)) = COUNT(Submission[CCNX]),
[TRACKER] = 0
),
1,
[TRACKER]
),
“CSGK”,
IF(
AND(
COUNT(SELECT(Submission[CSGK], [CSGK] = 1)) = COUNT(Submission[CSGK]),
[TRACKER] = 0
),
1,
[TRACKER]
),
“CTWN”,
IF(
AND(
COUNT(SELECT(Submission[CTWN], [CTWN] = 1)) = COUNT(Submission[CTWN]),
[TRACKER] = 0
),
1,
[TRACKER]
),
“CCNZX2”,
IF(
AND(
COUNT(SELECT(Submission[CCNZX2], [CCNZX2] = 1)) = COUNT(Submission[CCNZX2]),
[TRACKER] = 0
),
1,
[TRACKER]
),
“CCNZX1”,
IF(
AND(
COUNT(SELECT(Submission[CCNZX1], [CCNZX1] = 1)) = COUNT(Submission[CCNZX1]),
[TRACKER] = 0
),
1,
[TRACKER]
),
“CCNKB”,
IF(
AND(
COUNT(SELECT(Submission[CCNKB], [CCNKB] = 1)) = COUNT(Submission[CCNKB]),
[TRACKER] = 0
),
1,
[TRACKER]
),
“CCNZB”,
IF(
AND(
COUNT(SELECT(Submission[CCNZB], [CCNZB] = 1)) = COUNT(Submission[CCNZB]),
[TRACKER] = 0
),
1,
[TRACKER]
),
“CMYP”,
IF(
AND(
COUNT(SELECT(Submission[CMYP], [CMYP] = 1)) = COUNT(Submission[CMYP]),
[TRACKER] = 0
),
1,
[TRACKER]
),
“CMXJ”,
IF(
AND(
COUNT(SELECT(Submission[CMXJ], [CMXJ] = 1)) = COUNT(Submission[CMXJ]),
[TRACKER] = 0
),
1,
[TRACKER]
),
“CUSF”,
IF(
AND(
COUNT(SELECT(Submission[CUSF], [CUSF] = 1)) = COUNT(Submission[CUSF]),
[TRACKER] = 0
),
1,
[TRACKER]
),
[TRACKER]
)

Gustavo_Eduardo · March 26, 2025, 10:24pm

Hi, have you tried doing something like this? I haven’t tried it, but it doesn’t hurt to try it.

IFS(
AND(
IN(

[PROJECT SITE NAME],

LIST(“CCNX”, “CSGK”, “CTWN”, “CCNZX2”, “CCNZX1”, “CCNKB”, “CCNZB”, “CMYP”, “CMXJ”, "CUSF)

),
[TRACKER] = 0,
COUNT([Related Submissions]) = COUNT(SELECT(Submission[ID], [PROJECT SITE NAME] = [_THISROW].[PROJECT SITE NAME] AND [Submission_Status] = 1))
),
1,
[TRACKER]
)

desmond_lee · March 27, 2025, 12:16am

“Related Submissions” - There is no relationship between that to table. The only thing common is the site name where submission tale is place horizontally as header and OE Lead V1 table vertical repeating data at [PROJECT SITE NAME]

Gustavo_Eduardo · March 27, 2025, 12:59am

IF(
OR(
AND(
[PROJECT SITE NAME] = “CCNX”,
COUNT(SELECT(Submission[ID], [CCNX] = 1)) = COUNT(SELECT(Submission[ID], ISNOTBLANK([CCNX])))
),
AND(
[PROJECT SITE NAME] = “CSGK”,
COUNT(SELECT(Submission[ID], [CSGK] = 1)) = COUNT(SELECT(Submission[ID], ISNOTBLANK([CSGK])))
),
AND(
[PROJECT SITE NAME] = “CTWN”,
COUNT(SELECT(Submission[ID], [CTWN] = 1)) = COUNT(SELECT(Submission[ID], ISNOTBLANK([CTWN])))
),
AND(
[PROJECT SITE NAME] = “CCNZX2”,
COUNT(SELECT(Submission[ID], [CCNZX2] = 1)) = COUNT(SELECT(Submission[ID], ISNOTBLANK([CCNZX2])))
),
AND(
[PROJECT SITE NAME] = “CCNZX1”,
COUNT(SELECT(Submission[ID], [CCNZX1] = 1)) = COUNT(SELECT(Submission[ID], ISNOTBLANK([CCNZX1])))
),
AND(
[PROJECT SITE NAME] = “CCNKB”,
COUNT(SELECT(Submission[ID], [CCNKB] = 1)) = COUNT(SELECT(Submission[ID], ISNOTBLANK([CCNKB])))
),
AND(
[PROJECT SITE NAME] = “CCNZB”,
COUNT(SELECT(Submission[ID], [CCNZB] = 1)) = COUNT(SELECT(Submission[ID], ISNOTBLANK([CCNZB])))
),
AND(
[PROJECT SITE NAME] = “CMYP”,
COUNT(SELECT(Submission[ID], [CMYP] = 1)) = COUNT(SELECT(Submission[ID], ISNOTBLANK([CMYP])))
),
AND(
[PROJECT SITE NAME] = “CMXJ”,
COUNT(SELECT(Submission[ID], [CMXJ] = 1)) = COUNT(SELECT(Submission[ID], ISNOTBLANK([CMXJ])))
),
AND(
[PROJECT SITE NAME] = “CUSF”,
COUNT(SELECT(Submission[ID], [CUSF] = 1)) = COUNT(SELECT(Submission[ID], ISNOTBLANK([CUSF])))
)
),
1,
[TRACKER]
)

desmond_lee · March 27, 2025, 2:18am

Wow !!! you are amazing. The test answer is correct where CCNZB has no submission and it’s o. Beside the [TRACKER] there is a column [STATUS]. What if say I only want [STATUS]=“ACTIVE” and make [TRACKER]=“INACTIVE]” permanent [TRACKER]=I

Gustavo_Eduardo · March 27, 2025, 2:32am

Try this:

IF(
AND(
OR(
AND([PROJECT SITE NAME] = “CCNX”,
COUNT(SELECT(Submission[ID], [CCNX] = 1)) = COUNT(SELECT(Submission[ID], ISNOTBLANK([CCNX])))),
AND([PROJECT SITE NAME] = “CSGK”,
COUNT(SELECT(Submission[ID], [CSGK] = 1)) = COUNT(SELECT(Submission[ID], ISNOTBLANK([CSGK])))),
AND([PROJECT SITE NAME] = “CTWN”,
COUNT(SELECT(Submission[ID], [CTWN] = 1)) = COUNT(SELECT(Submission[ID], ISNOTBLANK([CTWN])))),
AND([PROJECT SITE NAME] = “CCNZX2”,
COUNT(SELECT(Submission[ID], [CCNZX2] = 1)) = COUNT(SELECT(Submission[ID], ISNOTBLANK([CCNZX2])))),
AND([PROJECT SITE NAME] = “CCNZX1”,
COUNT(SELECT(Submission[ID], [CCNZX1] = 1)) = COUNT(SELECT(Submission[ID], ISNOTBLANK([CCNZX1])))),
AND([PROJECT SITE NAME] = “CCNKB”,
COUNT(SELECT(Submission[ID], [CCNKB] = 1)) = COUNT(SELECT(Submission[ID], ISNOTBLANK([CCNKB])))),
AND([PROJECT SITE NAME] = “CCNZB”,
COUNT(SELECT(Submission[ID], [CCNZB] = 1)) = COUNT(SELECT(Submission[ID], ISNOTBLANK([CCNZB])))),
AND([PROJECT SITE NAME] = “CMYP”,
COUNT(SELECT(Submission[ID], [CMYP] = 1)) = COUNT(SELECT(Submission[ID], ISNOTBLANK([CMYP])))),
AND([PROJECT SITE NAME] = “CMXJ”,
COUNT(SELECT(Submission[ID], [CMXJ] = 1)) = COUNT(SELECT(Submission[ID], ISNOTBLANK([CMXJ])))),
AND([PROJECT SITE NAME] = “CUSF”,
COUNT(SELECT(Submission[ID], [CUSF] = 1)) = COUNT(SELECT(Submission[ID], ISNOTBLANK([CUSF]))))
),
[STATUS] = “ACTIVE”
),
IF([TRACKER] = “INACTIVE”, “I”, 1),
[TRACKER]
)

desmond_lee · March 27, 2025, 3:18am

Hi Gustavo

The first item is not correct. BTW what is “I” for in this -([TRACKER] = “NONACTIVE”, “I”

IF(
AND(
OR(
AND([PROJECT SITE NAME] = “CCNX”,
COUNT(SELECT(Submission[RPT M_ID], [CCNX] = 1)) = COUNT(SELECT(Submission[RPT M_ID], ISNOTBLANK([CCNX])))),
AND([PROJECT SITE NAME] = “CSGK”,
COUNT(SELECT(Submission[RPT M_ID], [CSGK] = 1)) = COUNT(SELECT(Submission[RPT M_ID], ISNOTBLANK([CSGK])))),
AND([PROJECT SITE NAME] = “CTWN”,
COUNT(SELECT(Submission[RPT M_ID], [CTWN] = 1)) = COUNT(SELECT(Submission[RPT M_ID], ISNOTBLANK([CTWN])))),
AND([PROJECT SITE NAME] = “CCNZX2”,
COUNT(SELECT(Submission[RPT M_ID], [CCNZX2] = 1)) = COUNT(SELECT(Submission[RPT M_ID], ISNOTBLANK([CCNZX2])))),
AND([PROJECT SITE NAME] = “CCNZX1”,
COUNT(SELECT(Submission[RPT M_ID], [CCNZX1] = 1)) = COUNT(SELECT(Submission[RPT M_ID], ISNOTBLANK([CCNZX1])))),
AND([PROJECT SITE NAME] = “CCNKB”,
COUNT(SELECT(Submission[RPT M_ID], [CCNKB] = 1)) = COUNT(SELECT(Submission[RPT M_ID], ISNOTBLANK([CCNKB])))),
AND([PROJECT SITE NAME] = “CCNZB”,
COUNT(SELECT(Submission[RPT M_ID], [CCNZB] = 1)) = COUNT(SELECT(Submission[RPT M_ID], ISNOTBLANK([CCNZB])))),
AND([PROJECT SITE NAME] = “CMYP”,
COUNT(SELECT(Submission[RPT M_ID], [CMYP] = 1)) = COUNT(SELECT(Submission[RPT M_ID], ISNOTBLANK([CMYP])))),
AND([PROJECT SITE NAME] = “CMXJ”,
COUNT(SELECT(Submission[RPT M_ID], [CMXJ] = 1)) = COUNT(SELECT(Submission[RPT M_ID], ISNOTBLANK([CMXJ])))),
AND([PROJECT SITE NAME] = “CUSF”,
COUNT(SELECT(Submission[RPT M_ID], [CUSF] = 1)) = COUNT(SELECT(Submission[RPT M_ID], ISNOTBLANK([CUSF]))))
),
[STATUS] = “ACTIVE”
),
IF([TRACKER] = “NONACTIVE”, “I”, 1),
[TRACKER]
)

Gustavo_Eduardo · March 27, 2025, 9:59am

Sorry for the delay in the answer. You have written “I”, that’s why I thought I was a value you needed.

desmond_lee · March 27, 2025, 12:08pm

hahaha sorry. So if the column [STATUS] has two value as follows [STATUS]=“ACTIVE” AND [STATUS]=“NONACTIVE”. IF the value is then the [TRACKER]=1 permanent. The rest is the same

Gustavo_Eduardo · March 27, 2025, 12:31pm

Ok, then try this:

IF(
OR(
AND(
[PROJECT SITE NAME] = “CCNX”,
COUNT(SELECT(Submission[ID], [CCNX] = 1)) = COUNT(SELECT(Submission[ID], ISNOTBLANK([CCNX])))
),
AND(
[PROJECT SITE NAME] = “CSGK”,
COUNT(SELECT(Submission[ID], [CSGK] = 1)) = COUNT(SELECT(Submission[ID], ISNOTBLANK([CSGK])))
),
AND(
[PROJECT SITE NAME] = “CTWN”,
COUNT(SELECT(Submission[ID], [CTWN] = 1)) = COUNT(SELECT(Submission[ID], ISNOTBLANK([CTWN])))
),
AND(
[PROJECT SITE NAME] = “CCNZX2”,
COUNT(SELECT(Submission[ID], [CCNZX2] = 1)) = COUNT(SELECT(Submission[ID], ISNOTBLANK([CCNZX2])))
),
AND(
[PROJECT SITE NAME] = “CCNZX1”,
COUNT(SELECT(Submission[ID], [CCNZX1] = 1)) = COUNT(SELECT(Submission[ID], ISNOTBLANK([CCNZX1])))
),
AND(
[PROJECT SITE NAME] = “CCNKB”,
COUNT(SELECT(Submission[ID], [CCNKB] = 1)) = COUNT(SELECT(Submission[ID], ISNOTBLANK([CCNKB])))
),
AND(
[PROJECT SITE NAME] = “CCNZB”,
COUNT(SELECT(Submission[ID], [CCNZB] = 1)) = COUNT(SELECT(Submission[ID], ISNOTBLANK([CCNZB])))
),
AND(
[PROJECT SITE NAME] = “CMYP”,
COUNT(SELECT(Submission[ID], [CMYP] = 1)) = COUNT(SELECT(Submission[ID], ISNOTBLANK([CMYP])))
),
AND(
[PROJECT SITE NAME] = “CMXJ”,
COUNT(SELECT(Submission[ID], [CMXJ] = 1)) = COUNT(SELECT(Submission[ID], ISNOTBLANK([CMXJ])))
),
AND(
[PROJECT SITE NAME] = “CUSF”,
COUNT(SELECT(Submission[ID], [CUSF] = 1)) = COUNT(SELECT(Submission[ID], ISNOTBLANK([CUSF])))
)
),
1,
IF(
[STATUS] = “ACTIVE”,
1,
[TRACKER]
)
)

Gustavo_Eduardo · March 27, 2025, 12:40pm

The concept is as follows:

If this happens, then that happens, otherwise this other thing. (this other thing) is another if, that is, it’s a nested “if.”

If any of the initial conditions are met, then it’s 1; otherwise, it analyzes another situation; this is where status = active comes in; if so, then 1, otherwise, take the value from the tracker column.

desmond_lee · March 27, 2025, 10:06pm

The first row with [STATUS]=“NONATIVE” still return 0. So I tweak a bit in the below and got it

IF(
[STATUS] = “NONACTIVE”,
1,
IF(
OR(
AND(
[PROJECT SITE NAME] = “CCNX”,
COUNT(SELECT(Submission[RPT M_ID], [CCNX] = 1)) = COUNT(SELECT(Submission[RPT M_ID],
ISNOTBLANK([CCNX])))),
AND(
[PROJECT SITE NAME] = “CSGK”,
COUNT(SELECT(Submission[RPT M_ID], [CSGK] = 1)) = COUNT(SELECT(
Submission[RPT M_ID],ISNOTBLANK([CSGK])))),
AND(
[PROJECT SITE NAME] = “CTWN”,
COUNT(SELECT(Submission[RPT M_ID], [CTWN] = 1)) = COUNT(SELECT(Submission[RPT M_ID],
ISNOTBLANK([CTWN])))),
AND(
[PROJECT SITE NAME] = “CCNZX2”,
COUNT(SELECT(Submission[RPT M_ID], [CCNZX2] = 1)) = COUNT(SELECT(
Submission[RPT M_ID],ISNOTBLANK([CCNZX2])))),
AND(
[PROJECT SITE NAME] = “CCNZX1”,
COUNT(SELECT(Submission[RPT M_ID], [CCNZX1] = 1)) = COUNT(SELECT(
Submission[RPT M_ID], ISNOTBLANK([CCNZX1])))),
AND(
[PROJECT SITE NAME] = “CCNKB”,
COUNT(SELECT(Submission[RPT M_ID], [CCNKB] = 1)) = COUNT(SELECT(
Submission[RPT M_ID], ISNOTBLANK([CCNKB])))),
AND(
[PROJECT SITE NAME] = “CCNZB”,
COUNT(SELECT(Submission[RPT M_ID], [CCNZB] = 1)) = COUNT(SELECT(
Submission[RPT M_ID], ISNOTBLANK([CCNZB])))),
AND(
[PROJECT SITE NAME] = “CMYP”,
COUNT(SELECT(Submission[RPT M_ID], [CMYP] = 1)) = COUNT(SELECT(
Submission[RPT M_ID], ISNOTBLANK([CMYP])))),
AND(
[PROJECT SITE NAME] = “CMXJ”,
COUNT(SELECT(Submission[RPT M_ID], [CMXJ] = 1)) = COUNT(SELECT(
Submission[RPT M_ID], ISNOTBLANK([CMXJ])))),
AND(
[PROJECT SITE NAME] = “CUSF”,
COUNT(SELECT(Submission[RPT M_ID], [CUSF] = 1)) = COUNT(SELECT(
Submission[RPT M_ID], ISNOTBLANK([CUSF]))))
),
1,
[TRACKER]
)
)

desmond_lee · March 27, 2025, 10:16pm

I have another question on this same form but instead of submission form it on another table. The action is still on [TRACKER] column. Post on the title - Return Table OE Lead V1 to 0 if it’s 1

Gustavo_Eduardo · March 27, 2025, 10:16pm

I think your formula might be a bit inefficient. Please try something like this.

IF(

[STATUS] = “NONACTIVE”,

1,

IF(

OR(

AND([PROJECT SITE NAME] = “CCNX”, [MatchingCount] = [NonBlankCount]),

AND([PROJECT SITE NAME] = “CSGK”, [MatchingCount] = [NonBlankCount]),

AND([PROJECT SITE NAME] = “CTWN”, [MatchingCount] = [NonBlankCount]),

AND([PROJECT SITE NAME] = “CCNZX2”, [MatchingCount] = [NonBlankCount]),

AND([PROJECT SITE NAME] = “CCNZX1”, [MatchingCount] = [NonBlankCount]),

AND([PROJECT SITE NAME] = “CCNKB”, [MatchingCount] = [NonBlankCount]),

AND([PROJECT SITE NAME] = “CCNZB”, [MatchingCount] = [NonBlankCount]),

AND([PROJECT SITE NAME] = “CMYP”, [MatchingCount] = [NonBlankCount]),

AND([PROJECT SITE NAME] = “CMXJ”, [MatchingCount] = [NonBlankCount]),

AND([PROJECT SITE NAME] = “CUSF”, [MatchingCount] = [NonBlankCount])

),

1,

[TRACKER]

)

desmond_lee · March 27, 2025, 10:23pm

There is an error. Macthingcount - I never seen this function before. Submission table has only value 0 an 1. There is no other value

Gustavo_Eduardo · March 27, 2025, 11:35pm

Okay, maybe it’s my mistake. I haven’t tried it, that’s the truth. The thing is, I see a lot of selects together, so it seems to me that an alternative could be made.

desmond_lee · March 28, 2025, 12:02am

Thank you very much. You are awesome. There is another question in a separate post and still on this form and this very same table table. Just that the submission table will become survey Matrix table

Gustavo_Eduardo · March 28, 2025, 12:38am

Thank you so much for your kind words. Send me the link to that post. If I can help, I’d be happy to.

desmond_lee · March 28, 2025, 12:40am

Hi Thank again. This is the link

https://www.googlecloudcommunity.com/gc/AppSheet-Q-A/Return-Table-OE-Lead-V1-to-0-if-it-s-1/m-p/889411#M265254

Topic		Replies	Views
Update Quarter Site Score Table Value When Survey Submitted AppSheet Q&A expressions	3	10	April 15, 2024
IN for multiple values AppSheet Q&A expressions	10	43	October 19, 2020
Switch query AppSheet Q&A databases , expressions	13	28	April 6, 2022

Cannot Find My Mistake Change Value 0 to 1

AI Suggested topics