Issue to run the formula on much columns

[alisonbrandao]

I'm facing a diffcult.
I make this code below, but when I do some changing on the access_A or access_B, only access_A runs right, the access_B show ErrorB. I don't know whats happening? Why it runs only the first formula and the others, in other column not runs, being if that both was changed.

Columns A = access_A (access request - type YES/NO) / approve_A (approval - type YES/NO) / Status_access_A (Status - type Text - using formula)

IF(
AND([access_A] = TRUE, [approve_A] = TRUE),
'Approved',

IF(AND([access_A] = TRUE, [approve_A] = FALSE),
'Not authorized',

IF(AND([_THISROW_BEFORE].[access_A] = FALSE,
[_THISROW].[access_A] = TRUE),
'Pending',

IF(AND([_THISROW_BEFORE].[access_A] = TRUE,
[_THISROW].[access_A] = FALSE),
'Permission removed',

'ErrorA'
)
)
)
)

Columns B = access_B (access request - type YES/NO) / approve_B (approval - type YES/NO) / Status_access_B (Status - type Text - using formula)

IF(

AND([access_B] = TRUE, [approve_B] = TRUE),
'Approved',

IF(AND([access_B] = TRUE, [approve_B] = FALSE),
'Not authorized',

IF(AND([_THISROW_BEFORE].[access_B] = FALSE,
[_THISROW].[access_B] = TRUE),
'Pending',

IF(AND([_THISROW_BEFORE].[access_B] = TRUE,
[_THISROW].[access_B] = FALSE),
'Permission removed',

'ErrorB'
)
)
)
)

[Aurelien]

Hi @alisonbrandao 

---

@alisonbrandao wrote:

I make this code below, but when I do some changing on the access_A or access_B, only access_A runs right, the access_B show ErrorB. I don't know whats happening? Why it runs only the first formula and the others, in other column not runs, being if that both was changed.

---

The [_THISROW_BEFORE] part would work only with data change on bots. Can you confirm you are using these expressions in such context?

Here are some improvement suggestions that make it a little easier to read and compare:

IFS(
  AND([access_A], [approve_A]),
  'Approved',

  AND([access_A],NOT([approve_A])),
  'Not authorized',

  AND(NOT([_THISROW_BEFORE].[access_A]),[access_A]),
  'Pending',

  AND([_THISROW_BEFORE].[access_A],NOT([access_A])),
  'Permission removed',

  TRUE,
  'ErrorA'
)

and 

IFS(

AND([access_B], [approve_B]),
'Approved',

AND([access_B], NOT([approve_B])),
'Not authorized',

AND(NOT([_THISROW_BEFORE].[access_B]),[access_B]),
'Pending',

AND([_THISROW_BEFORE].[access_B],NOT([access_B])),
'Permission removed',

TRUE,
'ErrorB'
)

 From what I read, the expressions are strictly equivalent modulo the A or B part.

As said earlier, I suspect your expression will trigger only if you set a condition on the A.

Can you try this condition on the data change event:

OR(
  [_THISROW_BEFORE].[access_A]<>[_THISROW_AFTER].[access_A],
  [_THISROW_BEFORE].[approve_A]<>[_THISROW_AFTER].[approve_A],
  [_THISROW_BEFORE].[access_B]<>[_THISROW_AFTER].[access_B],
  [_THISROW_BEFORE].[approve_B]<>[_THISROW_AFTER].[approve_B]
)

EDIT: these expressions are equivalent:

[_THISROW_AFTER].[access_A]
and
[_THISROW].[access_A]
and
[access_A]

For reference: IFS() - AppSheet Help

[alisonbrandao]

You said that "[_THISROW_BEFORE] part would work only with data change on bots", so I thought antoher solution without to use this expression but happened the same issue.
The column A running good but the column B no. (I DON'T KNOW WHY IT !)

Look at the other code that I did, I removed all code from initial value, and I concentrated all on the formula field.

(I likeed your tip about improvement suggestions - I'll check it after)

Columns A = access_A (access request - type YES/NO) / approve_A (approval - type YES/NO) / Status_access_A (Status - type Text - using formula)

IF(
AND(NOT(IN([ID], COLABORADORES[ID])), [access_A] = FALSE),
'Not resquested',

IF(AND(NOT(IN([ID], COLABORADORES[ID])), [access_A] = TRUE),
'Pending',

IF(AND([access_A] = TRUE, [approval_A] = TRUE),
'Approved',

IF(AND([access_A] = TRUE, [approval_A] = FALSE),
'Not authorized',

IF(AND([access_A] = FALSE, [Status_access_A] = 'Approved'),
'Not resquested',

IF(AND([access_A] = TRUE,
OR([Status_access_A] = 'Not authorized', [Status_access_A] = 'Not resquested')),
'Pending',

[Status_access_A]
)
)
)
)
)
)

Columns B = access_B (access request - type YES/NO) / approve_B (approval - type YES/NO) / Status_access_B (Status - type Text - using formula)

IF(
AND(NOT(IN([ID], COLABORADORES[ID])), [access_B] = FALSE),
'Not resquested',

IF(AND(NOT(IN([ID], COLABORADORES[ID])), [access_B] = TRUE),
'Pending',

IF(AND([access_B] = TRUE, [approval_B] = TRUE),
'Approved',

IF(AND([access_B] = TRUE, [approval_B] = FALSE),
'Not authorized',

IF(AND([access_B] = FALSE, [Status_access_B] = 'Approved'),
'Not resquested',

IF(AND([access_B] = TRUE,
OR([Status_access_B] = 'Not authorized', [Status_access_B] = 'Not resquested')),
'Pending',

[Status_access_B]
)
)
)
)
)
)

[Aurelien]

Can you please share a screeenshot of where is this expression?

Another tip, for the future: snippets of code can be shared using this code editor:

This:

IF(
AND(NOT(IN([ID], COLABORADORES[ID])), [access_A] = FALSE),
'Not resquested',

IF(AND(NOT(IN([ID], COLABORADORES[ID])), [access_A] = TRUE),
'Pending',

IF(AND([access_A] = TRUE, [approval_A] = TRUE),
'Approved',

IF(AND([access_A] = TRUE, [approval_A] = FALSE),
'Not authorized',

IF(AND([access_A] = FALSE, [Status_access_A] = 'Approved'),
'Not resquested',

IF(AND([access_A] = TRUE,
OR([Status_access_A] = 'Not authorized', [Status_access_A] = 'Not resquested')),
'Pending',

[Status_access_A]
)
)
)
)
)
)

would turn into this:

IF(
  AND(NOT(IN([ID], COLABORADORES[ID])), [access_A] = FALSE),
  'Not resquested',

  IF(AND(NOT(IN([ID], COLABORADORES[ID])), [access_A] = TRUE),
    'Pending',

    IF(AND([access_A] = TRUE, [approval_A] = TRUE),
      'Approved',

      IF(AND([access_A] = TRUE, [approval_A] = FALSE),
        'Not authorized',

        IF(AND([access_A] = FALSE, [Status_access_A] = 'Approved'),
          'Not resquested',

          IF(AND([access_A] = TRUE,
            OR([Status_access_A] = 'Not authorized', [Status_access_A] = 'Not resquested')),
            'Pending',

            [Status_access_A]
          )
        )
      )
    )
  )
)

[alisonbrandao]

Find attached my real app

[Aurelien]

Thank you for the screenshots.

From what I see, if I focus on A:

- you have a expression on a real column [status_access_A]

- you have your event that is looking for any update, under the condition that either [approval_A] or [approval_B] is not blank

- and this will reset the [approval_A] and [approval_B] to be blank again

What attracts my attention is: how do you update the values that makes [approval_A] or [approval_B] to be not blank?

Just in case: real column are update only when opening a form and saving again, while virtual columns are calculated on the fly.

In your situation, the bot can be triggered only with a change on real column.

Here is my suggestion:

- Create an action button to manually update the value of the column [approval_A] with the expression you have (Data: set the values of some columns in this row)

- do the same for [approval_B]

==> Can you confirm if your bot triggers in this case?

[alisonbrandao]

What attracts my attention is: how do you update the values that makes [approval_A] or [approval_B] to be not blank? 
- In the logic there is a sequence, so, first the code changes the columns after the bot start and clean the column.

This form the bot doesn't trigger good

[Aurelien]

Hi @alisonbrandao 

This is curious: 

I'm noticing only now, also, a circular expression: it may be the cause of the malfunction?

Additionnaly:

---

@alisonbrandao wrote:

In the logic there is a sequence, so, first the code changes the columns after the bot start and clean the column.

---

OK.

Allow me to kindly rephrase my question more specifically: what makes the code to change the column? Are you referring to the formula expression?

Are you expecting the bot to fire on form saving? Or after a change have been made with a quick edit column on a detail view? Or after a change have been made after another bot has been running?

For the last option, make sure you have this option enabled:

[alisonbrandao · Accepted solution]

Hi Aurelien,

     I found my mistake. 🤕
     I had forgot to add the every status columns on the slice, only status_acess_A was added. When I added the others, my code worked good, but I noted that was complex, so, I smaller and used your improvement suggestions. Thanks a lot!

Along my code I make two bots for each access event. One bot is triggered when the changes the access, because if the requester request the access, the status changes to pending and the approver needs to approve.
And other bot is triggered when the approval is FALSE (Not authorized), because the access_A on the detail view shows to the user that was't approved.

Thanks a lot for your patience and attention!