Skip to main content
Question

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

  • January 17, 2019
  • 0 replies
  • 92 views
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: 
https://community.quickbase.com/quickbase/topics/create-a-summary-field-totaling-the-number-of-separ...

Thanks.


This topic has been closed for replies.

A Uservoice suggestion for a new Summary field type Count (Unique) would get my vote
  • ArchiveUser
  • Author
  • Quickbase Alumni
  • January 24, 2019
anyone??
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.
Terms & ConditionsAccessibility statement