Scheduled Bot Template: Data Grouping Issue 2

[alhazen]

Apologies for the confusion but I'm experiencing issues with the template formulas.

1.

- Formula 1: The emails I receive contain duplicate data. Please see the red highlights for reference.

-Formula 2: The email is delivered, but no table appears in it

[alhazen]

Hey @AleksiAlkio and @Steve Could you please help me out with this? Thank you

[AleksiAlkio]

In #1, is your 3 rows appearing as you think.. meaning the test should show you 3 rows/tables?

In #2, it sounds your first Start: & End loop gives the zero rows meaning there isn't anything to add. Is the SELECT() bringing any rows?

[MultiTech]

For the duplicate, you might try wrapping your SELECT() in UNIQUE(), it will remove duplicates - which will prevent the <<START from looping through them.

[AleksiAlkio]

It sounds like using the UNIQUE() won't help in this case because when using Start & End expression, it already generates a list of key column values, which are unique. In this case the 2nd Start & End expression is just filtering wrong rows inside of a table.. and they don't have anything to do by each other. It's just a loop inside of another loop.

[Steve]

Try this as the outer Start tag:

<<Start: ORDERBY(SELECT(purchase_order[supplier], AND(IN([po_status], {"Raised", "In progress", "Ready"}), ((HOUR([due_date] - TODAY()) / 24) = 10)), TRUE), [company_name])>>

 and this as the inner:

<<Start: ORDERBY(SELECT(purchase_order[id], AND(IN([po_status], {"Raised", "In progress", "Ready"}), ((HOUR([due_date] - TODAY()) / 24) = 10), ([supplier] = [_THISROW-1])), TRUE), [supplier].[company_name])>>

[TeeSee1]

For the outer START expression, try..

<<START: ORDERBY(
 SELECT(
  PO[id],
  AND([_RowNumber] = MIN(SELECT(PO[_RowNumber], AND([supplier]=[_THISROW-1].[supplier], #rest of restrains here#)
 ),
 [supplier].[company_name]
)>>

[_THISROW-1] is the difference. Also you do not need to apply the same restrictions to every select statement but one.

[alhazen]

I've made a few adjustments, and it's working now.