Help

Count records in lookup field? Going crazy trying to figure this out

Topic Labels: Data Formulas
Solved
Jump to Solution
1594 4
cancel
Showing results for 
Search instead for 
Did you mean: 
Lara_Chan-Baker
5 - Automation Enthusiast
5 - Automation Enthusiast

Hi guys!

Losing my mind trying to figure this out – I thought it would be a super simple formula but just cannot get it to work. Here's the lowdown:

I'm mapping out placements for an editorial website – there's a carousel that will feature 5 articles each week, rotating between 12 different articles. I want to be able to count how many times an article has already been used in the plan.

So, I've got a table here that's grouped by each week (ie. "1. 9 Nov"). Below is an example screenshot showing the first 3 weeks (but there are 7 in total):

Screenshot 2023-11-09 at 2.20.43 pm.png

A breakdown of each column:

  1. Name: The name of the week (so it can be referenced in "List of placements")
  2. Article: This links to another table with a long list of hundreds of articles
  3. Placement: A single select field with the name of the particular spot the article will take in the carousel
  4. Week: Single select field with the name of the week (used to group the articles as I couldn't do single select as the first column)
  5. List of placements: A lookup field that pulls all the weeks that article was featured (so the lookup source is "Article" and then it's pulling in the "Name" listings)
  6. Count: The field that's not working! I simply want this to count the number of records in the "List of placements" lookup field. So that first row should say 3, the second row should say 2, and the third row should say 1. But I can't get it to do anything other than 1 or 0. 

Does anyone know what kind of field or formula I could use to make this work? I've tried counts, lookups, rollups and all sorts of formulas but having zero luck.

Thanks so much!

Cheers,

Lara

1 Solution

Accepted Solutions

Sorry, I think I understand what you're trying to do now.

What might work is to make the "Count" field from the Article table. Counting how links from that side. Then use the lookup to display that count field.

Let me know if that works 🙂

 


Justin Ng
Programme Coordinator at Sistema Aotearoa
https://www.sistemaaotearoa.org.nz/

See Solution in Thread

4 Replies 4
Lara_Chan-Baker
5 - Automation Enthusiast
5 - Automation Enthusiast

Thanks @Sistema_Aotearo but I've tried that and unfortunately it doesn't work!

Sorry, I think I understand what you're trying to do now.

What might work is to make the "Count" field from the Article table. Counting how links from that side. Then use the lookup to display that count field.

Let me know if that works 🙂

 


Justin Ng
Programme Coordinator at Sistema Aotearoa
https://www.sistemaaotearoa.org.nz/
Lara_Chan-Baker
5 - Automation Enthusiast
5 - Automation Enthusiast

Omg! You're a genius! That worked. Thank you SO MUCH! 

kschhajed
5 - Automation Enthusiast
5 - Automation Enthusiast

I have exact same issue. However, in my case, the 'Article' link can have multiple values and the lookups can be repetitive. For example, row no. 1 can have 'Recreation' and 'Paloma' as 2 linked fields; then both these fields will have their lookups and I want count of unique values from that lookup. I tried looking/rolling up Record IDs but it didn't help. Let me know if anyone can help here. Thanks.