Report, Multiple Tables and Slices

SecSol_Pak · 08-05-2021 02:28 AM

Hello,
I want to generate a report from multiple tables. Its kind of an attendance sheet, One table (StaffList) has all the Employee details with Status (Active and Inactive), we add their Attendances on a daily basis which has details on different tables, I want to check if Employee is not available in either of the tables then mark him P (Present) otherwise mark the status on whichever tables he’s available. For example, if the ID is available in the left table then mark his leave from the mentioned dates, if not then check the other tables.

I tried doing this on Google sheets with formula, but that is taking too much of time and making my AppSheet also slow. If I do it by attaching an excel template then the issue is with the array formula. Google Sheet and Excel has different formulas, I don’t get the results.

Thanks in advance

Aurelien

Hi @SecSol_Pak,

Your explanations look a bit confusing to me, but I think you want to perform 2 things:

Get presence of employees
As you mention “left table” in your example, then I guess you want to use actions in order to update some tables.

For the presence, you may want to use some of these expressions:

Here is an example of what you can use on your Employees Table:

IF(
 ISBLANK(
  FILTER("table1", 
   AND(
    [EmployeeID]=[_THISROW].[EmployeeID],
    [Date]=TODAY()
   )
  )
   +
  FILTER("table2", 
   AND(
    [EmployeeID]=[_THISROW].[EmployeeID],
    [Date]=TODAY()
   )
  )
 ),
 "Available",
 "Not available"
)

For the 2nd point, can you please provide more context ?

SecSol_Pak

i have a table Staff List with all the employees which I want in Report as the criteria to check in all the tables.
Now it should check if the IDs are available in Leave Table, if Yes then mark him on leave in the Report, if No then go to next table.
Which is for example Daily status table, if the ID available in that table then mark him the status given in that table, if no then go to the next table and so on.
if the IDs are not available in any of the tables then mark them present.
Pivot the dates from rows to columns.

I think I have cleared the question?

Aurelien

Ok, thank you for providing more context.
Then, you would probably want to use IFS expression as well.

So, you will probably want to use something like this:

IFS(
 ISNOTBLANK(
  FILTER("StatusTable1", 
    [EmployeeID]=[_THISROW].[EmployeeID]
  )
 ),  "status1",
 ISNOTBLANK(
  FILTER("StatusTable2", 
    [EmployeeID]=[_THISROW].[EmployeeID]
  )
 ),  "status2",
 (repeat as often as necessary...),
 true,
 "Present"
)

About that:

Pivot the dates from rows to columns.

I don’t get that point.

SecSol_Pak

Thank you for your reply, the formula/expression you mentioned. where to place that in a slice or make a new table for it. i Understand the expression but where will I place it to get the results.

I have to make a report taking data from multiple tables, for that i need the following:

All the Employees from one table (First Column)
Dates 1 till 30 All the month dates (second Column till 30)
Now in every date a result should occur by checking the data in different tables

As for the second point, when I’m saving the data it is stored in rows, I want my dates to be in columns and we can reference the dates on which we are looking for that particular ID

I hope I have cleared the question

Thanks
Sample Image if its clear

Aurelien

You are welcome.

Please use a virtual column, in your Employee Table.
More information here:

You can’t, you will have to figure out another way to proceed. A useful expression may be FILTER (please see link in my first answer), in another virtual column.