1 Reply Latest reply on Apr 1, 2009 6:23 AM by Orlando

    Displaying number of records in a found set



      Displaying number of records in a found set




      I have looked through the forum and can't see any previous posts on this question.


      In the UK, property owners who rent property must have a gas safety check each year. I have developed a straight forward database where you enter the date of the last check carried out, then OnLayoutOpen finds records where the next check is due within 30 days as a reminder.


      It would be nice to have a summary screen on a main menu which can find the number of records in this found set and display them. E.g. "you have [insert number of gas safety checks due] due within the next 30 days"


      Any thoughts on how to do this? It is most likely with this being a summary page, the table will have no records and no relationships.


      Many thanks for any assistance that can be offered.



        • 1. Re: Displaying number of records in a found set

          Hi Adrian


          This can be achieved one way using globals in your summary table and a relationship to your owners table, this will also give you the opportunity to list all the found records in a portal so you can select and view them quickly.


          What you need to do first is create two global Date fields in your SUMMARY table, gDateStart and gDateEnd, and you make them globals by clicking on the 'Options...' button after creating them and going to the Storage tab and clicking the box label 'Use global storage'.


          Now go to your relationship graph and create a new table occurrence for OWNERS, or use your main OWNERS table, and link it to the SUMMARY table with the following relationships:


          gDateStart DateNextCheck
          gDateStart DateNextCheck 


          and once that is done create the calculation that will display the number of records in the SUMMARY table. ( Summary_OWNERS is teh name of the TO I have linked to SUMMARY )


          CountChecksDue ( Calculated Text )
          Let ( 

          num = Count ( Summary_OWNERS::DateNextCheck )

          ; // Start of Calculation

          case ( num = 0 ; "no records found" ;
          num = 1 ; "There is only 1 record" ;
          num > 1 ; "There are " & num & " records'

          ) // End of Let function 


          And that should do for defining the fields and relationships


          Now you just need to amend the OnLayoutOpen script on your summary layout to set the two global fields to the current date and the current date plus 30


          SO it woudl be:


          Set Field [ SUMMARY::gStartDate ; Get ( CurrentDate ) ]
          Set Field [ SUMMARY::gEndDate ; Get ( CurrentDate ) + 30 ]
          Commit Record/Request 


          I hope this helps and let me know if anything is unclear