Counting different record per row

Hi everyone,

I am having a bit of a problem when counting data from another table.

For example,

on another table called leave_application, there was a record there that is list of leaves (vacation leave, sick leave, etc.) and status was approved, pending and rejected.

And on another table called emp_list (which the data per people is on there).
I want to count per leave that is applied and added to the leave_application.

Is there a way to do this?

What I’ve done now is, create a relation between these two table using the email address as their unique identifier and then match multiple, but what is next? Do I use a vlookup excel formula or lookup to get all the different tags, and recount is on different columns?

Thanks for the help.

Do you have another table that lists the different leave types?

If not, I would create such a table and do the calculation in that table.

  • Modify the action that opens a user details screen so that it first sets the user email in a “temporary” column in the user profile table.
  • Then create a Query column in the Leave Types table that targets the Leave Applications table, and apply the following filters:
    – Email is User Profile->Temporary Email Column
    – Leave Type is This row->Leave Type
  • You can then use a rollup column that targets the query column to count each type of leave.
1 Like

Hi @Darren_Murphy ,

I have created this one

code = Single Value from selected (entitled leave) from other source, it has multiple value, and separated by comma (,). So I get the first value and reflected on this
approved = using code as id, I query it so I get the row(s) per user.
approved_count = rollup count for approved
relation-credits = another relation to table (leave-credits), to get some info
leave_details = getting the label for the code (from leave-credits table)
entitled_num = getting the total leave (from leave-credits)
remaining = Math formula (entitled_num - approved_count)
display = template format for the leave.

Sick Leave: remaining/entitled_num

My problem now is in the approved_count. I want to get the column remaining days from other table, but I can’t get the data. I tried to use the lookup function, but there was an error. I use the relation for leave#1/approved (or the query source).


Do you know how to fix this?

My problem here is mostly I cannot get the column I want to do a sum up.

Thank you very much.

A Lookup in this context is going to return an array, which I believe is not what you want.
Try using a Single Value column instead.