2 Replies Latest reply on Jan 13, 2017 3:15 AM by fmpdude

    Return Number of Occurrences between 2 events




      To simplify I have a Table with 3 fields:






      As shown in Table View, I want to count the number of records between "Batch Out" on a on going basis (17 as shown) within the table.... I'm stuck on how best to do this....


      Thanks for any assistance.



        • 1. Re: Return Number of Occurrences between 2 events
          Johan Hedman

          Create a Calculated field called BatchCount with calculation



          IsEmpty( L10_Action ) ; 0;




          Then create a Statistic Field that do sum om that new field.


          This will give you numbers

          • 2. Re: Return Number of Occurrences between 2 events

            Isn't this a database design issue?


            I can't really be sure from your brief posting, but why not simply have something like a parent table with the serial number and a key generated for each group of events.


            So if your serial number for a batch is M012345, then you could append another key value as a "batch number" like:
            M012345_1.  Then, have a child table to hold the actual events whose foreign key points back to the parent's M012345_1 value.


            Then, between each set of "events" for the given serial number + key in the parent table, getting the count is trivial.


            You would then just need a way to increment the key in the parent for each new batch.


            That seems like a better "database way" to do what you want.


            Does that make sense?