Discussions

Expand all | Collapse all

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

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

    Top
    Contributor
    Posted 01-17-2019 03:06
    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.




  • 2.  RE: Summary of Field, count only if date is different per log

    Bronze
    Contributor
    Posted 01-17-2019 20:22
    A Uservoice suggestion for a new Summary field type Count (Unique) would get my vote


  • 3.  RE: Summary of Field, count only if date is different per log

    Bronze
    Contributor
    Posted 02-22-2019 01:17


  • 4.  RE: Summary of Field, count only if date is different per log

    Top
    Contributor
    Posted 01-24-2019 04:27
    anyone??


  • 5.  RE: Summary of Field, count only if date is different per log

    Posted 01-24-2019 12:35
    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.


  • 6.  RE: Summary of Field, count only if date is different per log

    Posted 01-24-2019 12:42
    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.