Summary of Field, count only if date is different per log

  • 0
  • 1
  • Question
  • Updated 6 months ago
  • Answered
Hi all,

I'm currently looking into doing a summary field, counting the number of days that a piece of particular equipment is used.

However, the summary field does not allow me to filter it based on a unique date, so I'm looking for a solution.

For example,

Log 1 - EQ1, 5th Jan
Log 2 - EQ1, 5th Jan
Log 3 - EQ1, 6th Jan
Log 4 - EQ1, 6th Jan
Log 5 - EQ1, 6th Jan
Log 6 - EQ1, 7th Jan
Log 7 - EQ1, 8th Jan

- Ideally, I should get a value of 4, because EQ1 is used on 4 different dates.

This value could go up to 100+ in the long run.

I've found another solution, but it requires creating many fields, which is troublesome. 
This is the link to the alternative solution:


Photo of Samuel Goh

Samuel Goh

  • 382 Points 250 badge 2x thumb

Posted 7 months ago

  • 0
  • 1
A Uservoice suggestion for a new Summary field type Count (Unique) would get my vote
Photo of Samuel Goh

Samuel Goh

  • 382 Points 250 badge 2x thumb
I just attempted to post a comprehensive response but the site got hung up and did not post. I will have to ty again when I have time.
Here is another shorter attempt

Make a new table with the Key field in the format EQ-Date. Also make a field on the index table called [Eq-Date exists?] with a checkbox formula of true.

Make a calculated field on the equipment usage table to calculate to the format EQ1-01-24-2019

Make a relationship based on that field to the index table. Lookup the field [Eq-Date exists?]

Make a summary report if the unique [EQ-Date] records and export to the index table to initialize. Include a filter on that report to only export if the [Eq-Date exists?] is not checked.

Export to the index table to initialize it and make formula fields in the index table to separate out the equipment name and the date into separate fields.

Make an Automation to create the index record when and equipment usage is modified and the index record does not exists.

Make an Automation to auto delete each night if the index record has no children (after adding a summary count field to t(e relationship). That will clean up where the equipment usage record was edited to a different date or equipment or was deleted.