5 Replies Latest reply on Apr 1, 2015 10:54 AM by MrBond

    Count last records depending on its value

    MrBond

      Title

      Count last records depending on its value

      Post

      Hi!

      I have a one to many relationship between table A and table B (which is the child table). In table B i have a text field called "status". There could be many records and the "status" is changing between "on" and "off" (only these two values are available).

      Now I want to count the records in the layout of table A where the last related records in table B in the field "status" are "on". How I can do this? I tried to do it with a calculation field in table B without success:

      Count(status) AND GetNthRecord ( status; Get ( RecordNumber ) - 1 ) = "On"

        • 1. Re: Count last records depending on its value
          mchancevet@gmail.com

          I'm not sure this would work for very dynamic contexts (status changing frequently) and it feels like a clumsy solution to me, but you could try using a script which loops through Table A records (Parent) - interrogates the contents of the 'status' field of the last (if any) related (child) record and then changes a field (Parent::LastChildStatus) in the parent table if the requisite condition is met. You can then do a find on the value of Parent::LastChildStatus and use a summary field (count) in the parent table to give you your count.

          For this you would need to define how a child record becomes the last record - (sort on timestamp field etc).

          regards,

          Morgan

          • 2. Re: Count last records depending on its value
            philmodjunk

            To check the value of a field of the "last" related record, use the last function. So one simple option is to define a calculation field in table A with this expression: Last ( Table B::Status ) (select "Text" as the result type.)

            Perform a find on Table A specifying "on" in this calculation field and get ( FoundCount ) will then return the number of Table A records that have "On" as the last related record in Table B.

            • 3. Re: Count last records depending on its value
              MrBond

              Hi!

              Thank you for your answer! I created the calculation field in table A with  Last ( Table B::Status ). When I place this field in the layout of table A it shows "On", ok. Where do I have to put the get ( FoundCount )? When I perform a search in this field with "On", the result indicator shows the number of all records with "On".

              • 4. Re: Count last records depending on its value
                philmodjunk

                Here's the manual method:

                enter find mode.

                Type "on" into this field

                Perform the find

                The found set shown in the status tool bar is the number of records where the last related record is "on".

                But this can also be scripted and in a script, if you need to put this count into a field for a report or something, you can use get ( FoundCount) to get this count and put it into such a field.

                • 5. Re: Count last records depending on its value
                  MrBond

                  Hi!

                  Thank you very much! This solved my problem.