Expression Count(Select( within a Template

[Haus]

Hello, 

I'm struggling with my expression to count a value based on conditions from other tables. I don't get an error with the expression, but do get an incorrect value. After looking at the data, the expression is only counting ”Non Violent Misdemeanor” when they occur on different dates within the StephensonCourt_Tbl. For example, if there are 3 ”Non Violent Misdemeanor” on 03/01/2022 then the expression is only counting this as 1. If there is a ”Non Violent Misdemeanor” on 03/02/2022, another on 03/03/2022 and another on 03/04/2022, then they're each counted. I've rewritten this a couple of different ways and get the same result with the same problem. I have two examples of my expression below: 

<<Count(Select(StephensonVPRAIR_Tbl[ChargeCategory],AND([CourtID].[CourtDate]>=”02/01/2022”,ANY(Select(StephensonVPRAIR_Tbl[ChargeCategory],[CourtID]=[_ThisRow-1].[CourtID]))=”Non Violent Misdemeanor”)))>>

<<Count(Select(StephensonCourt_Tbl[CourtID],AND([CourtDate]>=”02/01/2022”,TEXT(ANY([Related StephensonVPRAIR_Tbls][ChargeCategory]))=”Non Violent Misdemeanor”)))>> 

How can I get the expression to count each individually and NOT count as 1 when multiple occur on the same date?

Thank you in advance for your assistance!!

[WillowMobileSys]

I think we may need to know a little more about your table structure AND where you are using this expression....but based on what I understand, it seems you may be over-complicating it.  Does this expression not work for you?

<<Count(Select(StephensonVPRAIR_Tbl[ChargeCategory],
               AND([CourtID].[CourtDate] >= ”02/01/2022”,
                   [CourtID] = [_THISROW].[CourtID],
                   [ChargeCategory]=”Non Violent Misdemeanor”)))>>

[Haus]

Hello, 

Thanks for taking a look at this. I did try your suggestion and it did not work. Here's the error I received:

Error encountered in step with name [SendProfileReport]: Error: 'ProfileProcess' task 'ProfileTask' Attachment template. Expression 'Count(Select(StephensonVPRAIR_Tbl[ChargeCategory],AND([CourtID].[CourtDate]>=”02/01/2022”,[CourtID] = [_THISROW].[CourtID],[ChargeCategory]=”Non Violent Misdemeanor”)))' is invalid due to: Unable to find column '_THISROW'.

Basically, I'm trying to count the column ChargeCategory within the StephensonVPRAIR_Tbl whos CourtDate within the StephensonCourt_Tbl is <=02/01/2022 where the value of the ChargeCategory equals "Non Violent Misdemeanor". These tables, StephensonVPRAIR_Tbl and StephensonCourt_Tbl are connected via the CourtID column. 

Thanks again for your help!

[WillowMobileSys]

Since the expression is in an attachment  template, we need to understand how you are running your Bot and how you are using the expression in the template.

[SkrOYC]

It doesn't look like there is any problem.

Count(
    Select(
        StephensonVPRAIR_Tbl[ChargeCategory],
        AND(
            [CourtID].[CourtDate]>=”02/01/2022”,
            [CourtID] = [_THISROW].[CourtID],
            [ChargeCategory]=”Non Violent Misdemeanor”
        )
    )
)

Just one question, there is a [CourtID] column on both, the table you are at the context of this expression is taking place as well as the StephensonVPAIR_Tbl table?

[Haus]

Hello, 

Thanks for looking at this. Yes, the [CourtID] column is within both tables. 

Thank you!

[Haus]

Thanks for the follow up!

The expression is as I've provided within the template. The expression is within a column of a table. 

The Bot is run on a schedule, running every Sunday evening, attaching the template. 

If there is something more specific that I can/should provide that would be helpful please let me know and I'll happily provide it. 

Thanks again!

[WillowMobileSys · Accepted solution]

---

The Bot is run on a schedule, running every Sunday evening, attaching the template. 

---

Ok.  I then assume that you are running the Bot with the "ForEachRowInTable" setting turned off??   If so, then this means there is no row information being sent to the template for [_THISROW] to reference and the expression, as I provided, will not work.  

It now makes sense why you had the original expression you did.  However, It is not clear if you are trying to get a Total count across all Court ID's or some other summed amount.

Will this expression work?  It will provide a count of ALL ”Non Violent Misdemeanor” since ”02/01/2022”

<<Count(Select(StephensonVPRAIR_Tbl[ChargeCategory],
           AND([ChargeCategory] = ”Non Violent Misdemeanor”,
               IN([CourtID], Select(StephensonCourt_Tbl[CourtID], [CourtDate] >= ”02/01/2022”))
           )
        )
  )>>

[Haus]

Hello, 

Thank you so much, this is working great! I've always struggles with using the IN() expression and will need to do some research on this. Thanks again!

[WillowMobileSys]

https://help.appsheet.com/en/articles/2357277-in