Forum Discussion

ArchiveUser's avatar
ArchiveUser
Qrew Captain
7 years ago

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

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.


Forms
Formulas and functions
Relationships
  • AlexCertificati's avatar
    AlexCertificati
    Qrew Cadet
    7 years ago
    A Uservoice suggestion for a new Summary field type Count (Unique) would get my vote
  • QuickBaseCoachD's avatar
    QuickBaseCoachD
    Qrew Captain
    7 years ago
    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.
  • QuickBaseCoachD's avatar
    QuickBaseCoachD
    Qrew Captain
    7 years ago
    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.