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

    calculation fields



      calculation fields


      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...



        • 1. Re: calculation fields

          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

            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

              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...



              • 4. Re: calculation fields

                PartDueDate  minus the DateSubmitted


                or ( in FileMakerese )


                PartDueDate  -  DateSubmitted 


                ( calculation with number result )

                • 5. Re: calculation fields

                  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



                  • 6. Re: calculation fields

                    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

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

                      • 8. Re: calculation fields

                        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!



                        • 9. Re: calculation fields

                          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

                            wow..it works...!


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



                            • 11. Re: calculation fields



                              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...



                              • 12. Re: calculation fields

                                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



                                  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...



                                  • 14. Re: calculation fields

                                    "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.