5 Replies Latest reply on Oct 4, 2009 9:36 AM by Orlando

    Days Job Active

    Rayban

      Title

      Days Job Active

      Post

      With the help of posts on the web I've managed to get our company Filemaker Pro 10 database to run calculation field that gives the number of days any job has been active. The calculation field is:

       

      Get ( CurrentDate )-Date Created

       

      Very simple stuff I know. The problem I have is that not all jobs are active of course so even closed jobs are returning a figure. We know if a job is active from a text field showing a checkbox set with a value list of 'Active' - if it's clicked on then it's live.

       

      Can anyone suggest how do I might tell Filemaker to look at this field, see if it's active, if so, do the above calculation and return the number of days, otherwise reply with something like 'Closed'?

       

      I've looked at the 'IF' command but the advice didn't seem to work with a checkbox.

       

      Thanks for any help.

       

       

        • 1. Re: Days Job Active
          Orlando
            

          Hi Rayban

           

          Do you have any other values ticked in your Checkbox Set? if so try the following:

           

          If ( Patterncount ( JobStatus ; "Active" ) ; Get ( CurrentDate ) - Date Created ; "Closed" )

           

          JobStatus being your checkbox field. 

           

          I hope this helps

          • 2. Re: Days Job Active
            Rayban
              

            Hi Orlando, thanks for the reply.

             

            No there only seems to be one value.

             

            Job Status field is a text one, indexed - showing as a Checkbox set - displaying Values from a list called 'Active' - with Custom Values with one entry 'Active'

             

            I'm not sure why it was done this way I would have thought there would have been another entry in the list 'Closed' but this seems to be in another field made the same way as this one.

             

            I've tried your line and it currently replies 'Closed' for all jobs.  

            • 3. Re: Days Job Active
              Orlando
                

              Not sure why they will all show as "Closed", sounds like the field is setup ok, you may want to check the actual value in the Job Status field. You can do this by making a second copy of the Checkbox on the layout and making it a standard text field. then in browse mode check the box and see what value is placed in that field.

               

              You could add a second value of Closed if you like, but it sounds like its setup to just have the value of "Active" or be Empty, and the calc in my previous reply should calculate the days if the field have "Active" in that field.

               

              Let me know how you get on with this. 

              • 4. Re: Days Job Active
                Rayban
                  

                Hi Orlando,

                 

                It's working!

                 

                What you suggested got me checking again and it turns out that there are several similar fields some of which look like they are redundant. Turns out that Job Status wasn't the key field I needed but a similar one called 'Active' - I find the similar naming that is used quite confusing. 

                 

                Anyhow it's working like a charm now. Thanks for nailing this for me.

                 

                Ray 

                • 5. Re: Days Job Active
                  Orlando
                     No worries Ray, glad I could help.