Re: App not following branching rules in a bot

1minManager · 04-04-2023 10:30 AM

Hopefully this is not another terminal Bot bug...

So I've created a simple Bot to send an email with a PDF attachment. It triggers off a schedule, looks at slice data, works on each row and ignores security filters. The client wants it so that it sends a different attachment depending on if the site is in Wales or not (I'm UK based). So in the Bot I created a "Branch on condition" rule of:

OR(
   LEFT([Postcode],2)="CF",
   LEFT([Postcode],2)="LD",
   LEFT([Postcode],2)="LL",
   LEFT([Postcode],2)="NP",
   LEFT([Postcode],2)="SA",
   LEFT([Postcode],4)="SY16",
   LEFT([Postcode],4)="SY17",
   LEFT([Postcode],4)="SY18",
   LEFT([Postcode],4)="SY19",
   LEFT([Postcode],4)="SY20",
   LEFT([Postcode],4)="SY23",
   LEFT([Postcode],4)="SY24",
   LEFT([Postcode],4)="SY25",
   AND(
      OR(
         LEFT([Postcode],3)="CH5",
         LEFT([Postcode],3)="CH6",
         LEFT([Postcode],3)="CH7",
         LEFT([Postcode],3)="CH8"
      ), 
      NOT(OR(
         LEFT([Postcode],4)="CH61",
         LEFT([Postcode],4)="CH62",
         LEFT([Postcode],4)="CH63",
         LEFT([Postcode],4)="CH64",
         LEFT([Postcode],4)="CH65",
         LEFT([Postcode],4)="CH66",
         LEFT([Postcode],4)="CH88",
         LEFT([Postcode],4)="CH89"
      ))
   )
)

Weird thing is, it always branched TRUE even when the [Postcode] does not match e.g. "BS405EL". So I took the above formula and put it into a virtual column called [Is Wales], as a sort of santity check. Sure enough a few are showing in the virtual column as FALSE even though the Branch condition is sending then down the TRUE branch.

So I assume it's maybe something with the Bot. So I put the above formula into the template file of the email body. This shows in the email body as an N despite it adding the PDF template for TRUE. I even break the above formula into its 3 main sections to check each one, all are correct in the email body

To try and force it down the FALSE branch I wrap the entire Branch formula in NOT(). All this suceeds in doing is now all emails are showing the FALSE template. Well at least that template works...

But when I removed the Not() everything is still going down the FALSE Branch!!!! So it's almost like the Branch condition is somehow locked onto whatever the last result is. Not() can switch this but seemingly nothing else!

Wondering if its the N bit which should show TRUE I do this in the Branch condition:

IF(
...Branch Formula...,
TRUE,
FALSE

This changes nothing...

Changed Branch formula to use the virtual column [Is Wales], no change...

Finally tried

[Postcode]="BS405EL"

Nope, everything still goes down the FALSE branch....

Think this maybe related to @Joseph_Seddik post https://www.googlecloudcommunity.com/gc/AppSheet-Q-A/Automation-critical-problem-and-workaround-Bots...

Copying in @devingu @Roderick @zito from the above post in case this is related. Text in red highlights the stangest part which might give you a steer.

Just off to create a second bot to avoid having to use the branch. I'll report back shortly...

Simon, 1minManager.com

WillowMobileSys

Negative Logic will get you every time!!

EDITED: My comments below are wrong. Still I think the logic needs further validation for accuracy.

~~The NOT(OR(...)) is incorrect. Since your intention is to disallow all of those, you need to use NOT(AND(...)).~~

Honestly, since it is only 2 - 3 digit prefixes for which you are disallowing certain 4 digit prefixes. it's not many more entries so I would just include the valid 4 digit values in the initial OR() list.

~~For ease of maintenance and simplicity of use, I personally would create a utility table with these entries and then just perform a table lookup.~~

WillowMobileSys

Alas! After reviewing again, I am wrong. The AND() would NEVER be true since the Postcode cannot begin with different 4 char values at the same time.

BUT, I still think there's something not quite right about the logic. I'll take a deeper look at it.

WillowMobileSys

Just a little restructuring is needed:

AND(
    OR(
       LEFT([Postcode],2)="CF",
       LEFT([Postcode],2)="LD",
       LEFT([Postcode],2)="LL",
       LEFT([Postcode],2)="NP",
       LEFT([Postcode],2)="SA",
       LEFT([Postcode],4)="SY16",
       LEFT([Postcode],4)="SY17",
       LEFT([Postcode],4)="SY18",
       LEFT([Postcode],4)="SY19",
       LEFT([Postcode],4)="SY20",
       LEFT([Postcode],4)="SY23",
       LEFT([Postcode],4)="SY24",
       LEFT([Postcode],4)="SY25",
       LEFT([Postcode],3)="CH5",
       LEFT([Postcode],3)="CH6",
       LEFT([Postcode],3)="CH7",
       LEFT([Postcode],3)="CH8"
    ), 
    NOT(OR(
         LEFT([Postcode],4)="CH61",
         LEFT([Postcode],4)="CH62",
         LEFT([Postcode],4)="CH63",
         LEFT([Postcode],4)="CH64",
         LEFT([Postcode],4)="CH65",
         LEFT([Postcode],4)="CH66",
         LEFT([Postcode],4)="CH88",
         LEFT([Postcode],4)="CH89"
    ))
)

I hope this helps!!!

Roderick

Great eye @WillowMobileSys!

WillowMobileSys

Actually, I don't!! Ugh! the Negative logic got me!!!

But I got there in the end!!

1minManager

Hi @WillowMobileSys @Roderick

So last night I split the Bot into 2 seperate Bots and ran my formula as the row Filter Condition. With the Wales Bot being the exact formula and the Not Wales Bot simply have NOT() around it. This worked fine. Which pointed towards it not being my formula.

This morning I recreated the Bot with the Branch condition from yesterday. To keep it even simpler I dispensed with any templates and just used the email subject to indicate which branch it took and put some formulas in the email body to show how virtual columns where computing the data.

First run, everything went down the TRUE branch. Including postcodes such as "WA2 9PH", "BS20 7TR", "CH43 5UT", "LA12 9JU", "OL4 3LQ" & "SY5 0TF".

Next I tried @WillowMobileSys forumla. Which I agree is a little simplier, but does not to me seem to offer any different logic. Anyway, tried it and it didn't work. 4th email to come in had a postcode of "BB12 0BN".

So this still appears to me to be a bug.

1minManager

In fact to further clarify... Nothing went down the FALSE branch. Despite the exact same formula giving FALSE as the result when used in both a virtual column and a formula in the email body.

WillowMobileSys

@1minManager wrote:

Which I agree is a little simplier, but does not to me seem to offer any different logic

You are correct...no difference in the expressions which I no see with a fresh perspective. That's 2 strikes! How many do I get?

@1minManager wrote:

Despite the exact same formula giving FALSE as the result when used in both a virtual column and a formula in the email body.

I am starting to believe you are experiencing a bug as well.

Did you by chance try using the Virtual Column to perform the branching? I am wondering if inspection of a column behaves differently than execution of an expression inside of the branching condition.

FYI, another option is to have 2 bots, one that covers the TRUE branch and another that covers the FALSE branch. Not ideal as there likely is step duplication but wanted to throw that out there.

1minManager

That's 2 strikes! How many do I get?

Haha, one more then you get sent to the Google Gulag 😂

Did you by chance try using the Virtual Column to perform the branching?

Yes I tried the virtual column and it didn't work as the branch condition. But it does work if that VC is in the email body as <<[Is Wales]>>

...another option is to have 2 bots...

Must have not made it clear, but thats what I did last night and the formula works fine as the Referenced Row formula. This is were I am now.

Wondering wether I should just try FALSE as the branch condition to see if everything still goes down the TRUE branch...

WillowMobileSys

@1minManager wrote:

Must have not made it clear, but thats what I did last night

Uh oh! What food do they serve in the Gulag?

1minManager

Quick update before I'm off to post this as a support ticket. So this formula as the branch condition, everything goes down TRUE branch

OR(
   LEFT([Postcode],2)="CF",
   LEFT([Postcode],2)="LD",
   LEFT([Postcode],2)="LL",
   LEFT([Postcode],2)="NP",
   LEFT([Postcode],2)="SA",
   LEFT([Postcode],4)="SY16",
   LEFT([Postcode],4)="SY17",
   LEFT([Postcode],4)="SY18",
   LEFT([Postcode],4)="SY19",
   LEFT([Postcode],4)="SY20",
   LEFT([Postcode],4)="SY23",
   LEFT([Postcode],4)="SY24",
   LEFT([Postcode],4)="SY25",
AND(
   OR(
      LEFT([Postcode],3)="CH5",
      LEFT([Postcode],3)="CH6",
      LEFT([Postcode],3)="CH7",
      LEFT([Postcode],3)="CH8"
   ), 
   NOT(OR(
      LEFT([Postcode],4)="CH61",
      LEFT([Postcode],4)="CH62",
      LEFT([Postcode],4)="CH63",
      LEFT([Postcode],4)="CH64",
      LEFT([Postcode],4)="CH65",
      LEFT([Postcode],4)="CH66",
      LEFT([Postcode],4)="CH88",
      LEFT([Postcode],4)="CH89"
   ))
)
)

Despite postcodes or "WA29PH", "BS207TR", "CH435UT","LA129JU","OL43LQ","SY50TF" clearly NOT matching that rule.

So instead simplified the slice data to just these 12 records:

OR([Postcode]="WA29PH",[Postcode]="BS207TR",[Postcode]="CH435UT",[Postcode]="LA129JU",[Postcode]="OL43LQ",[Postcode]="SY50TF",[Postcode]="CH54RQ",[Postcode]="CF398RS",[Postcode]="CF448NG",[Postcode]="CF240JF",[Postcode]="CH88JD",[Postcode]="LL139TF")

Then set the Branch formula to [Postcode]="BS207TR". Result, all 12 go down the TRUE branch.

For the sake of completeness I did try both TRUE and FALSE as the branch condition, which both did work. So does this part of Appsheet have an issue with text in formulas?

Aurelien

Hi, probably a bug.

A simple expression such as:

ISBLANK([Related TABLEs])

returns always FALSE on a branching step, despite virtual column tests vary according to expectation.

@Roderick

devingu

Hey all, we are aware of the issue and currently investigating it. Thank you!

Marc_Dillon

Possibly related:

https://www.googlecloudcommunity.com/gc/AppSheet-Q-A/Need-help-trying-to-automate-sending-Email-App-...

wabrian

Issue is now fixed, thank you for reporting.

1minManager

Is this bug fix going to be mentioned in https://www.googlecloudcommunity.com/gc/Release-Notes/bg-p/appsheet-releasenotes

?

wabrian

The fix went out yesterday, we didn't it in the release notes, but we probably should have. My apologies!

Patrick_Paul

I'm having the same problem now 8 months later. I have a simple expression in a branch:

contains([Event Type],"TDP")

This always evaluates as false.