Issue with embedding templates

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

In what way isn’t it working?

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

image

The PDF received looks like this :

1 Like

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

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.

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

1 Like

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:

2 Likes

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

The other one looks fine.

1 Like

Whoops! My bad! Remove this comma:

image

Also fixed my previous post.

2 Likes

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.
image
image

The testing model looks clear too

But the report seems to ignore the second part of it

Can’t find out what’s wrong !

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

1 Like

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

1 Like