Having simultaneous and dependent columns as part of report

[Pratyay_Rakshit]

Theoretically,

There’s a search table with search criteria - from date, to date, name of project.

The search returns rows (based on a slice) from a table with user names and if they attended or not a session or not, filtered by ‘from’ and ‘to’ dates and name of project. That means a person can have multiple instances (comprising of both yes and no) within a date range. And there can be multiple persons within a date range.

I want to have a report triggered by change from the search table, that outputs in a table the unique persons within that inputted date range and their corresponding attended percentage (count of all attended records by that person / count of all records by that person within that date range).

---

I can have all the persons printed out and would make sure that one name is not repeated (since there can be multiple instances of the same person within a date range) by using the unique() function. But how can I get to count their corresponding attendance records?

[MultiTech]

Hey @Pratyay_Rakshit

You’re correct about using UNIQUE() to limit things down to single occurrences in the list.

• Alternatively, if you’re using a SELECT() to gather your list, there’s an often overlooked 3rd parameter for the select formula.
  • If you set it to true, it gives you a unique list automatically.

---

Pratyay_Rakshit:

But how can I get to count their corresponding attendance records?

The more I think on it… this would really involve a complex system of actions.

If you’re wanting individualized statistics like that, your best bet would be to create a separate table to hold those variables.

• This would make generating those “easier”
  1. You already have the name,
  2. Next you would need to run a SELECT() to find that person’s attendance records with the criteria applied
  3. Then run another SELECT() to grab ALL their attendance records.

If you’re writing these into their own column on a table, where each record corresponds to one attendee - generating these details is “easier.”

---

Then bring in looping: You’ve got what you need to do (generate the record in the table), next you need to run that action for each name in the list.

---

You can see what I mean about there being a lot involved here. And all of this would be made even easier if you brought references into the mix.

help.appsheet.com

References Between Tables

Learn about references, when to create them, and how to use them so your data makes sense.

Hope it hleps.

[Pratyay_Rakshit · Accepted solution]

MultiTech_Visions:

If you’re writing these into their own column on a table, where each record corresponds to one attendee - generating these details is “easier.”

This was the trick.

I made virtual columns in the attende table, that counts present or absent records from the attendance table against each attendee. If there are no attendance records for an attendee then the output is 0.

Then in the report a start expression with select() prints out all the records that are not 0