14 Replies Latest reply on Jun 25, 2010 2:30 PM by LaRetta_1

    calculation fields

    storywizard

      Title

      calculation fields

      Post

      Hello all:

       

      I am just starting work on a couple of calculation fields. I am really new at this

      .Attached is my layout. Here is a description of the calculations, one is fairly simple, the second one is a mind bender for me, anyway...

       

      1: The Lead time field, which will be the PartDueDate  minus the DateSubmitted


      2: The PartStatus field which has one of four results, each with a different colour...

      1. If DateCompleted is blank and DueDate is not passed then PartStatus will be InProcess 
      2. If DateCompleted is blank and DueDate is passed then PartStatus will be OverDue 
      3. If DateCompleted has a date that is on or before DueDate then PartStatus will be Complete 
      4. If DateCompleted has a date that  is after DueDate then PartStatus will be CompletedLate

       

      Any help as I make my way through this will be greatly appreciated as always...

       

      Storywizard




        • 1. Re: calculation fields
          philmodjunk

          The colors can be applied with conditional formats.

           

          Case ( IsEmpty ( DateCompleted ) and ( DueDate > Get ( CurrentDate ) ) ; "InProcess" ;

                       IsEmpty ( DateCompleted ) ; "OverDue" ;

                       DateCompleted < DueDate ; "Complete" ;

                       DateCompleted > DueDate ; "CompletedLate" )

           

          • 2. Re: calculation fields
            LaRetta_1

            Calculation can also be written:

             

            Case (

            NOT DateCompleted ;
            Case ( PartDueDate   ≥   Get ( CurrentDate ) ; "In Process"  ; "OverDue" ) ;
            Case ( DateCompleted   ≤  PartDueDate ; "Complete" ; "CompletedLate" )
            )

             

            As for conditional formatting, if you've never done it before, here's a jump-start. 

             

            Select your PartStatus field and enter 'Formula is' and Self= "In Process".  Then select blue text below.  Add each status exactly the same.

             

            UPDATE: BTW, I couldn't figure out how the Lead Time entered into this and maybe it doesn't.  So I just went by your example in Item #2. :smileyhappy:


            • 3. Re: calculation fields
              storywizard

              sorry, the lead time is the first calculation I  that am trying to do, and I have to say is not at easy as I thought. I want to take one date away from another, leaving me with either a positive or negative result in days.

               

              so: a DueDate of 7/10/2010 minus the DateSubmitted 7/05/2010 would give a result of 5...

              How do you do that as a formula??

               

               

              I obviously need some practice to understand all the functions of the calculation formulas...

               

              Thanks for the help so far...

               

              Storywizard

              • 4. Re: calculation fields
                raybaudi

                PartDueDate  minus the DateSubmitted

                 

                or ( in FileMakerese )

                 

                PartDueDate  -  DateSubmitted 

                 

                ( calculation with number result )

                • 5. Re: calculation fields
                  storywizard

                  Thanks for the reply:

                   

                  I tried this and got strings of numbers or decimals so long as to be of no use, I am missing something...lol

                   

                  Storywizard

                  • 6. Re: calculation fields
                    philmodjunk

                    PartDue and DateDue should be fields of type date.

                     

                    If you are trying to enter a date directly into a calculation expression, you have to use a format that fielmaker recognizes as a date and not as a division problem.

                     

                    In a calculation, 6/21/2010 means 6 divided by 21 divided by 2010.

                     

                    Date ( 6 ; 21 ; 2010 ) means June 21, 2010.

                     

                    Thus 6/21/2010 - 5/31/2010 will not give you the difference between the dates in days, but Date2Field - Date1Field will.

                    • 7. Re: calculation fields
                      raybaudi

                      PartDueDate and DateSubmitted must be real date field, NOT text fields

                      • 8. Re: calculation fields
                        storywizard

                        ahhh, so I will need to change the fields I have been putting the dates in...can I still use a drop down calendar to fill in the date??

                        I will try that in the morning...Thanks all, you guys are great!

                         

                        Storywizard

                        • 9. Re: calculation fields
                          philmodjunk

                          That's the type of field you should use with a drop down calendar.

                           

                          If you don't put dates in an actual date field you are asking for trouble.

                          • 10. Re: calculation fields
                            storywizard

                            wow..it works...!

                             

                            Thanks all...I will try the big one tomorrow...

                             

                            Storywizard

                            • 11. Re: calculation fields
                              storywizard

                              hmmm:

                               

                              I got the formula working but the Conditional Formatting has me confused.

                               

                              Do you mean I have to type in Self= or is that what the default symbol means?

                               

                              Also there are two sets of "" "" does In Process go inside a set of these??

                               

                              Sorry if this sounds confused, I am ...:-)

                               

                              almost there...

                               

                              Storywizard

                              • 12. Re: calculation fields
                                philmodjunk

                                Self is a special identifier you can use here to refer to the field being formatted with the conditional format.

                                 

                                You create one such expression for each color and text combination, just changing the text between the quotes for each color.

                                • 13. Re: calculation fields
                                  storywizard

                                  Hello...

                                   

                                  I have put these in the Conditional Formatting for my Part Status field:

                                   

                                  Formula is Self="OverDue"

                                  Formula is Self="Complete"

                                  Formula is Self="InProcess"

                                   

                                  found out that I cannot have a second capital letter in a formula name...

                                   

                                  anyway it works a treat, thanks all...

                                   

                                  Storywizard

                                  • 14. Re: calculation fields
                                    LaRetta_1

                                    "found out that I cannot have a second capital letter in a formula name..."

                                     

                                    Sure you can, Storywizard.  You can have second capital letter in field names and in data as well.  Your example would work perfectly.  Did you happen to have a space between Over and Due?  If it still doesn't work please tell us your OS and FM versions because I've never heard of that kind of bug.