Record Counter

[Joemac]

Hello all.  I'm looking to create a field that counts how many associated records are in a separate table.  For example I have a client_table and jobs_table that has a one to many relationship.  I want to calculate how many total jobs each client has.  Any ideas?

[Tubby]

Hi @Joemac,

You can create an Integer field for the count and use a Task to update the record count of jobs for each client if you have some sort of Client_ID field for both tables as a reference.

I have sample here that you can refer to:

I hope this helps!

~Tubss

[NiceDuck]

You can also do the same on a report datapage  (assuming that this datapage is using the parent table as its datasource) using a calculated field with an SQL statement.

ex.

Select count(<anyfield>) from Child where Parent_ID = [@field:Parent_ID]

just replace the <anyfield> with the name of any field from the child table

[Flowers4Algernon]

Hi, I recommend that you check  out this How-To link as welll: https://howto.caspio.com/tables-and-views/triggered-actions/

[astroboy]

Hello there, since these records are coming from different tables with a relationship established between them, I would recommend creating a view to join these tables and merge the matching records. Here's a HowTo article for reference:

https://howto.caspio.com/tables-and-views/what-are-views/views/

[astroboy]

As for the calculation, you may check the function reference article to see the list of available functions that you can use to achieve your ideal output:

https://howto.caspio.com/function-reference/