Solved: Expression Count(Select( within a Template - Page 2

Haus · 04-13-2022 01:13 PM

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

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

View solution in original post