Cannot Find My Mistake Change Value 0 to 1

[desmond_lee]

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 · Accepted solution]

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]
)

[Gustavo_Eduardo]

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]

"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 · Accepted solution]

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]

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]

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]

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]

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

[desmond_lee]

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]

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 · Accepted solution]

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]

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]

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]

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]

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]

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]

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]

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]

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/8894...