1 Reply Latest reply on May 11, 2015 7:53 AM by nicolai

    Current Population Question



      Current Population Question


      Hi, hoping you can help me with determining the number of youth in our facility on a specific date.  I have used a calculation called "current" for many years which gives me the number of youth in the facility in "real time", ie today--by using IsEmpty(Discharge Date).  However I need a way to show the number of youth in the building when I enter a date into a "global date field".  Each youth has an admit and discharge date.  In the past, I have also used a script to generate a census for a specific date  by using a script that  works something like this:  Set field (Admit Date<=Find Date and Discharge Date >= find Date;  duplicate Record/Request; Set Field "Discharge Date "=")that "finds" the # of kids when the date is entered, but I would like to be able to open a screen (like an "interface" screen); type in the date and have the #'s populate/show in a portal row.  Is this possible? 

        • 1. Re: Current Population Question

          FileMaker 12+

          For global field "Find Date" anywhere in the database, e.g. currenttable layout:

          ExecuteSQL ( "
             SELECT COUNT ( myRecordId ) FROM Youth
                 WHERE  \"Admit Date\"<=? 
                 ( AND \"Discharge Date\" =>? 
                  OR \"Discharge Date\" IS NULL)
          " ; "" ; "" ;  currenttable::Find Date  )

          Set a field to this calculation. On committing the value in the global field it should show you the number of youth on a day.