SUM() on Email Template Summary Table

[Flor_HS]

Hi! I have setup a workflow triggered by a “Report” table, every time the report table is updated, it logs the user, date and time of report request and registers a “change” that triggers a workflow … (bare with me… I will get there!)

The purpose of it is to email the company in charge of product delivery/distribution, so the workflow I am triggering sends an excel report of each open orders individually and their data for the requested date to an email (that works fine!)

But, I also need the same workflow (on Action 2) or on a separate workflow triggered by the same “report” change, to send a summary of all orders in a word (PDF) document. The document should show basically how many items of each product are to be “delivered” total in other words I am trying to create a summary table to be sent by email.

Looking in this forum I found you can use the SUM expression. I tried, I dont get any errors, the file is just simply not delivered. My “start” statement is the same as the one in my excel report which I know already works.

Here is what I have so far

[Aleksi]

If you want to read values from the whole table, then the Start: & End is not needed. You can find the reason for this probably if you “Save & Verify Data” instead of just normal “Save”.

[Flor_HS]

Sorry I submitted my response before I saw yours.
When I removed Start- End I got one column not found (the one I am using for SUM).

I was able to locate the log and I see the errors there.

[Flor_HS]

So, using this nice video, https://youtu.be/T9x7Qrwr3VA I was able to get it to work, however I get one item for each of the items in my list, and I really just need a summary, is that possible?

Here is how I have it so far:

[Flor_HS]

It worked!!!

I removed the LIST() expression from SUM. And I got it to return a value, however I need it to display only the value of rows that comply certain criteria ( the date is the same as in the form), can I use a SELECT within the SUM?

EDIT: I added a select statement but I get the following error:

Expression ‘SUM(SELECT(Ordenes Abiertas Basicos[ORD_Kits basicos],Ordenes Abiertas Basicos[fecha entrega]=[_THISROW].[ORD_fecha_entrega],TRUE))’ is invalid due to: Cannot compare List with Date in (ORDENES ABIERTAS BASICOS[Fecha Entrega] = [ID Reporte].[ORD_fecha_entrega]).",

[Aleksi]

Use something like SUM(SELECT(TableName[ColumnName],[AnotherColumnName]=“Kits Tradicionales”))

[Flor_HS]

This is what I did sorry I know is confusing! I eliminated my column names and used generics to make it a bit easier to follow.

Here is the formula I am using:

SUM
(
SELECT(
TableName[ColumnName],
[AnotherColumnName_Date]=[_THISROW].[Thedateselected]
)

Where: AnotherColumnName is a Date value
And: [_THISROW].[Thedateselected] is a date selected on the form that triggers the workflow that sends the report. This date is an ENUM value as it takes its own values from the Open Orders Table

When doing this I get the following error:
Expression is invalid due to: Cannot compare List with Date in ([AnotherColumnName_Date]= [_THISROW].[Thedateselected])."

[Aleksi]

Either your “AnotherColumnName_Date” or “Thedateselected” is a list of values… and that’s why it won’t work. You need to either cange the column type to Date or what ever that column type is OR you need to use IN() expression. Please check the correct format for that…

help.appsheet.com

IN()

Is item in list?

[Flor_HS]

I tried but the results don’t discriminate the filtered value.
Let me elaborate, I ended up creating virtual columns so it was easier to troubleshoot.

1. I have a “REPORT” table and an “OPEN ORDERS” table
2. The “REPORT” table has a field that references the OPEN ORDERS “DATE” field.
3. The user, selects the “DATE” they want to report on from a dropdown menu while on the “REPORT” form
4. Based on the SELECTED DATE, I need to SUM one of the values in the OPEN ORDERS table. The value column is called “ORD_kits basicos”

Now, I created a virtual column to make the sum, in my example I have 2 dates, to chose from (as there are open orders those dates) my dates are June 22nd and June 23rd.

When I select June 22nd my SUM comes back with 13, and when I select June 23rd, my SUM comes back with 13 too. But in my data I really have 1 for 23rd and 12 for 22nd.

SUM(
SELECT(
OpenOrdersTable[ORD_Kits basicos],
in(
[_THISROW].[DateSelected],
OpenOrdersTable[DeliveryDate]
)
)
)

What am I missing

[Aleksi]

Is the DateSelected field in Reports table an Enum or EnumList or a Ref? Is the DateSelected a from date?

[Aleksi]

I’m assuming that the DateSelected is an Enum field and it’s the start date for your report. Then you should write it like…

SUM(
SELECT(
OpenOrdersTable[ORD_Kits basicos],
[_THISROW].[DateSelected]<=[DeliveryDate]
)
)

[Flor_HS]

Aleksi:

SUM(
SELECT(
OpenOrdersTable[ORD_Kits basicos],
[_THISROW].[DateSelected]<=[DeliveryDate]
)
)

Yes, DateSelected is an ENUM field.
I tried what you sent me and I get the following error, also I need the expression to evaluate dates to be equal (=) not <=

The error says it cannot compare Date with List.

I also created another virtual column “FECHA SELECCIONADA” that refers to the value of [_THISROW].[DateSelected] but is Date type.

[Aleksi]

May I ask what is your formula at this moment?

[Flor_HS]

The error above happens when I make the formula in the format you recommended above, which will be this:

SUM(
SELECT(
Ordenes Abiertas Basicos[ORD_Kits basicos],
[_THISROW].[Fecha Seleccionada]
<=Ordenes Abiertas Basicos[fecha entrega]
)
)

So the one I have in the cell now is this one but it gives me incorrect results as in my previous POST, It sums results for both days but I need one at the time:

SUM(
SELECT(
Ordenes Abiertas Basicos[ORD_Kits basicos],
IN([_THISROW].[Fecha Seleccionada],
Ordenes Abiertas Basicos[fecha entrega]
)
)
)

[Steve · Accepted solution]

This:

SUM(
  SELECT(
    Ordenes Abiertas Basicos[ORD_Kits basicos],
    [_THISROW].[Fecha Seleccionada]
    <= Ordenes Abiertas Basicos[fecha entrega]
  )
)

should be this:

SUM(
  SELECT(
    Ordenes Abiertas Basicos[ORD_Kits basicos],
    [_THISROW].[Fecha Seleccionada]
    <= [fecha entrega]
  )
)

[Flor_HS]

Steve:

SUM(
  SELECT(
    Ordenes Abiertas Basicos[ORD_Kits basicos],
    [_THISROW].[Fecha Seleccionada]
    <= [fecha entrega]
  )
)

I can’t belive it was something so simple OMG!!! I worked all night on it until 6am when I finally gave up! and you solved it in a blink!!! Thank you Steve !