Solved: Scheduled Bot Template: Data Grouping Issue - Page 2

alhazen · 11-01-2024 01:22 AM

Hello,

I’ve configured a scheduled bot as shown in the attached image and applied a formula in the template. My expectation was for the data to be grouped by supplier, but it didn’t work as intended. Do you have any suggestions on what might be wrong? Thank you

BotTemplateError

Steve

You're using a scheduled event to generate the PDF. The event is attached to a table, not to a specific row in the table. In your template, you refer to _THISROW, which is intended to refer to a specific row. Because the event is not attached to a specific row, there is no row to which _THISROW can refer.

This:

should instead be this:

<<Start: ORDERBY(SELECT(purchase_order[id], AND(([_THISROW].[supplier] = [supplier]), ISNOTBLANK([date_raised]), ISBLANK([due_date]), ISBLANK(SELECT(purchase_order[id], AND(([_THISROW].[supplier] = [supplier]), ISNOTBLANK([date_raised]), ISBLANK([due_date], ([_THISROW].[_RowNumber] < [_RowNumber]))))))), [supplier].[company_name])>>

This expression produces a list of id values from the purchase_order table for only the first occurrence of each matching supplier value. This gives you your list of suppliers.

Then, this:

should instead be:

<<Start: ORDERBY(SELECT(purchase_order[id], AND(([_THISROW-1].[supplier] = [supplier]), ISNOTBLANK([date_raised]), ISBLANK([due_date]))), [client].[brand_name])>><<[client].[brand_name]>>

This expression gets you the purchase orders associated with the specific supplier.

View solution in original post