BOTS condition based on a limit in VC

[DaGi07]

Hello,

i’m using a bot that advice the manager if the VC [Limite_spesa] is exceeded (overbudget). Now, i’m setting an EVENT that occurs when is verified this condition:

SUM(
SELECT( Spese[Importo],
AND( MONTH([DATA]) = MONTH(TODAY()), YEAR([DATA])=YEAR(TODAY())
))) >
SUM(
SELECT(Obiettivi[Limite_spesa]???)
)

I would say, when the sum of some values in first part of expression (SUM(
SELECT( Spese[Importo],
AND( MONTH([DATA]) = MONTH(TODAY()), YEAR([DATA])=YEAR(TODAY())
))) is greater then any values [Limite_spesa] (blue values on screen) , send an email in which is specified which [Limite_spesa] and [Obiettivo] is exceeded .

I know that the second part of expression is wrong because i put the SUM and i have not consider the sum of both values (1.065,06 €+1.047,60 €) but only the singles values.

tks guys

[Joseph_Seddik]

Would you please rephrase your question showing more context and sharing screenshots of your tables? Thanks.

[DaGi07]

yes, these are the screen of the table that should work in my both:

When the SUM of value Spese[Importo] in current month overcame the value Obieittivi[Limite_spesa], send an email and said me in the text mail which Obiettivi[Limite_spesa] has been exceeded.

tks

[Joseph_Seddik]

Since there is no relation between your Objettivo and date, you’ll need to check every value in the Objettivi able, each time a Spece record has been created or updated, against the sum of Speces during a month.

There’s no easy way I see to do this. I see it certainly possible, but you have to develop a bit complex solution for it. This solution would involve creating a new table to record the sum per month with references to both Objettivi and Speces tables. A new Spece record would involve updating the relevant Sums table record, or automatically creating a new month if it doesn’t already exist. Each creation or update in the Sums table in turn should launch an action on all records of the Objettivi table, each will check if the objettivo has been exceeded, and in this case record the objettivo in the Sums table in a column containing a list of exceeded objettivi. In case a Spece record has been updated or removed so that the corresponding monthly sum is lower than the previously-recorded objettivo, then this objettivo should be removed from the list. The mail then should be triggered on each update of the exceeded objettivi list.

I’ve done something very similar, but in another context, using the same procedure above. Go for it and tell me if you face difficulties in the middle.