Jump to content

question re how to count items with a join table


Recommended Posts

I have a database with 3 tables (to simplify): keywords (keyword_text and keyword_ID), files (filename text and file_ID) and a join table (keyword_ID, file_ID). Suppose I want to have a list view that shows all filenames with their associated count of keywords attached to each file, and conversely another list view with all keywords and their associated count of files attached to each keyword. How would I go about doing that?

Edited by human
Link to post
Share on other sites

There are two ways to show a count of related records:

  1. Define a calculation field in the parent table that uses the Count() function to count the related records; 
  2. Define a summary field in the related table that counts some field that cannot be empty, then place this field on the layout of the parent table.

In the given example, you could use a single summary field defined in the join table for both of your uses. However, this requires you do not have duplicate entries in the join table.

--
P.S. As noted in your other question, you can use an unstored calculation field = Get ( FoundCount ) instead of the summary field.

 

Edited by comment
Link to post
Share on other sites

count works, thanks!

However, on a local file this is fast. But when connected to a remote server, trying to sort on that field is extremely slow, because there are over 400K entries in the join table. 

Edited by human
Link to post
Share on other sites

Sorting by an unstored calculation is bound to be slow. Unfortunately, the only cure is denormalization: add a Number field to both tables and make sure they are updated to reflect the current counts - either periodically (e.g. overnight) or on every modification of the join table.

 

Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.