question re how to count items with a join table

[human]

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 February 28, 2021 by human

[comment]

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 February 28, 2021 by comment

[human]

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 February 28, 2021 by human

[comment]

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.