How to count the number of occurrences in a column of type : Reference List

I want to count the number of times a word appears in a Reference List column.

I wrote this little piece of code, but it doesn’t work on some records.

def condition(x): return x==$NOM
sum(condition(x) for x in Notation.lookupRecords(CATEGORIE_NOM = $NOM, ACHETER = TRUE()).CATEGORIE_NOM)

If the word is in the second position or more, it is not counted.

It seems that the problem comes from this code.
It displays an empty value if the word you are looking for is in the second position or higher.

Notation.lookupRecords(CATEGORIE_NOM = $NOM, ACHETER = TRUE()).PRODUIT

I don’t understand the structure of your document or what you’re trying to do well enough to help you. Can you show me what your data looks like and what result you want to get? What are the types of the CATEGORIE_NOM and NOM columns?

Separate from any formula, it might be helpful to create a summary table based on the Notation table grouped by CATEGORIE_NOM and ACHETER, that should show you counts for each group.

Sorry for the lack of information, it is an oversight on my part.

Here are the types of the columns in question:

CATEGORIE_NOM : REFERENCE LIST
NOM : TEXT

To give you a little more explanation:

I created a page with the name CATEGORY containing a column NAME of type TEXT. So I enter each new category in this column.

Then I have another page with the name FILM where I have created a CATEGORY_NAME column of type REFERENCE LIST.

When I create a new record in the FILM page, the CATEGORY_NAME column allows me to select a category from the CATEGORY page.

So I can choose several categories for this record.

Then, I wanted to create a column in the CATEGORY page that would count the number of times the category is used (it is true that I can have this same result with group pages).

However, I have created another column that I don’t think I can do with group pages.

This column returns all the movies where the category appears thanks to this code:

Film.lookupRecords(CATEGORIE_NOM = $NOM).TITRE

Unfortunately, I found that if there are several categories in this field, only the first category is taken into account, which means that it is not functional.

Maybe you have a more efficient solution or simply a tip to solve my problem.

I hope I haven’t forgotten anything, thank you.

First of all, you should think of Reference and Reference List columns as containing records. CATEGORIE_NOM contains a list of records from the CATEGORIE table, not a list of names. You probably have it set to show the NOM column, but that’s just for display, not calculation. So you want something like Film.lookupRecords(CATEGORIE_NOM = rec) for your lookup to connect the records contained in CATEGORIE_NOM and the current record. In fact, renaming your column from CATEGORIE_NOM to just CATEGORIE might make this clearer.

Secondly, a normal lookup searches for records where the values are equal. Since CATEGORIE_NOM is a list of records and rec is a single record, they will never be equal. You should use Film.lookupRecords(CATEGORIE_NOM = CONTAINS(rec)).

That’s the full formula. It gives a list of records from the Film table. Leave out the .TITRE at the end. Set the type of the column to Reference List and choose TITRE from the SHOW COLUMN dropdown.

To count the number of times the category appears in the Film table, use the formula len($Films) where Films is the column with the formula above.

Most of the things in your response sound familiar.

I don’t think you got all the elements to communicate a concrete answer to this problem.

It is true that sometimes it is difficult to express a problem and this one in particular.

The best I can do is to make a mini-project that states the problem well in a simple way and share this document with you.

However, I will also explain the problem well.

In the FILM page, I have a CATEGORY_CATEGORY column that will allow me to use the lookupRecords method like this:

FILM.lookupRecords(CATEGORY_CATEGORY = $CATEGORY).TITLE

By returning the TITLE, it allows me to display all the titles that use this category. However, this does not work on sets.

For example, it is not possible to do this:

FILM.lookupRecords(CATEGORY_CATEGORY in $CATEGORY).TITLE

So I tested by turning the CATEGORY_CATEGORY column into an array, unfortunately, lookupRecords is very limited when it comes to comparison operators.

If I can achieve this and no matter what methodology, I could easily use for the column NUMBER OF USES OF THE CATEGORY use the function LEN().

With this explanation and this example, I don’t think I can give you more information.

You can access the document from this link :
https://docs.getgrist.com/vS8ftC2euXdn/SUPPORT

Thanks for your help.

I can’t access the document, try turning on public access.

Here is an example document implementing what I’ve described: Grist

1 Like

This is what I put you editor if you want to add changes.

Use your support email address to log in.

Thank you for your example. It helped me understand what the problem was.

In fact, I had almost everything right from the start, but there is just one thing I did not know about the selection lists.

It’s that once you have the indexes, you just have to put the same type of data in the calculated column to find the values and not the indexes.

All this for that, grrr (not for you), finally it works.

Thanks again for the support!
Even if we had a little trouble communicating, you didn’t give up.

Great job. :+1:

1 Like

Glad this is figured out! To recap, was “CONTAINS” that @alexmojaki suggested the key to the solution? As in here?

Film.lookupRecords(CATEGORIE_NOM = CONTAINS(rec))

This is indeed important when doing a lookup in a ReferenceList columns. CONTAINS() is documnted in https://support.getgrist.com/functions/#contains, but we’ll give some thought to documenting it better, and perhaps find other ways to guide the user when using lookup formulas.

1 Like