Issue with embedding templates

[CHRISTOPHE_CHAN]

Hi,

I stucked with a report trying to set the correct start expression. I have a main “Log” table which contains working hours by employees. I want to generate a report listing all logs for a particular period.

In order to do so, I created a “report request” table. A report request contains Start date and End date columns.

I added 2 virtual columns [corresponding logs] and [corresponding employees] with an expression to select rows from the “Log” table that fit the desired report period. The list is correct :

SELECT(log[ID], AND([_THISROW].[start date] <=[date], [_THISROW].[end date] >=[date]), TRUE)

SELECT(log[Employee], AND([_THISROW].[start date] <=[date], [_THISROW].[end date] >=[date]), TRUE)

When I create the report I want for each employee, the correponding logs listed and ordered by date. I wrote :

<<Start:([corresponding employee])>>

<<[ID]>>

<<Start:SELECT([corresponding log][ID), [employee]=[_THISROW].[ID], true)>>

<<[ID]>>

<>
<>

This is not working but can’t figure out how to write the expression. If anyone could point the good direction.

Thanks

[Steve]

In what way isn’t it working?

[CHRISTOPHE_CHAN]

I have a workflow looking the report request table, sending a PDF report when add occurs. The template looks like this :

The PDF received looks like this :

[Steve]

The <<Start>> expression must produce a list of key column values. The two SELECT() expressions you gave:

CHRISTOPHE_CHANDELIE:

SELECT(log[ID], AND([_THISROW].[start date] <=[date], [_THISROW].[end date] >=[date]), TRUE)

SELECT(log[Employee], AND([_THISROW].[start date] <=[date], [_THISROW].[end date] >=[date]), TRUE)

each refer to the log table, but return lists of different column values: ID and Employee. A table can only have one key column, so one of those expressions is not returning a list of key column values.

[CHRISTOPHE_CHAN]

OK thanks. Understood. I’ll keep the 1st one returning keys from the log table.

In my report I still need to group corresponding logs by Employee (which is a column of the log table) and sorted by date (also a column of the log table). I don’t get the mecanism to do it. Is that possible ? Like :

For the list of corresponding logs

Employee 1
Log 1 1st september
Log 2 2nd september
…

Employee 2
Log 1 1st september
Log 2 2nd september
…

[Steve]

corresponding employees:

ORDERBY(
  FILTER(
    "log",
    (
      [_ROWNUMBER]
      = MAX(
        SELECT(
          log[_ROWNUMBER],
          AND(
            ([employee] = [_THISROW-1].[employee]),
            ([date] >= [_THISROW].[start date]),
            ([date] <= [_THISROW].[end date])
          )
        )
      )
    )
  ),
  [employee]
)

corresponding logs:

ORDERBY(
  FILTER(
    "log",
    AND(
      ([date] >= [_THISROW].[start date]),
      ([date] <= [_THISROW].[end date])
    )
  ),
  [date]
)

template:

<<Start:[corresponding employees]>>
...
<<Start:SELECT([corresponding logs][ID], ([employee] = [_THISROW-1].[employee]))>>
...
<<End>>
<<End>>

See also:

FAQ: FILTER(), LOOKUP(), MAXROW(), MINROW(), REF_ROWS(), and SELECT() Tips & Tricks 💡

Back-references within nested queries ([_THISROW-n]) Consider a complex App formula with “nested” SELECT() expressions (SELECT() expressions within SELECT() expressions): SELECT( table[column1], ... SELECT( table[column2], ... SELECT( table[column3], ... ) ... ) ... ) Let’s call the row for which this entire expression is evaluated as its App formula the Origin Row. Within this entire expression, we can refer to column values of the Origin Row by dere…

[CHRISTOPHE_CHAN]

Thanks a lot looks promising. The corressponding employee expression fails :

The other one looks fine.

[Steve]

Whoops! My bad! Remove this comma:

Also fixed my previous post.

[CHRISTOPHE_CHAN]

Hi @Steve

I got the point about comparing logs with logs within a single start expression. The corresponding employee expression returning one unique log key for each employee.

The report arrives but still something wrong even if both formula selection formula look fine.

The testing model looks clear too

But the report seems to ignore the second part of it

Can’t find out what’s wrong !

[Steve]

You should probably engage support@appsheet.com for further help with this.

[CHRISTOPHE_CHAN]

I will. Thanks anyway for your precious helps on this.