9 Replies Latest reply on Aug 24, 2011 11:20 AM by LaRetta_1

    Date Calculation Flag

    bencraft

      Title

      Date Calculation Flag

      Post

      Hello,

      Working with filemaker pro PC

      Trying to make a flag output "DUE" for the following:

      Date Due field is calculated by 1 year of the Date Entered Field. Then an object has text of DUE based on the due date field <= (Today)

      So far I have the "DUE" flag working but it shows up even when no date is entered. How can I make it not show "DUE" even it does not have a date inputed?

      These are the fields used in the calculation.

      Field: Date Entered = Type Date "User Entered Date"

      Field: Date Due = Calculation = Date Entered + 365

      Field: Date Flag = Calculation = If(GetAsNumber(Today)>= GetAsNumber(Date Due);"DUE")
      Calculation results is Text
      Do not evaluate if all referenced fields are empty "Checked"

      Field: Today = Date
      Auto-enter - Creation Date "Checked"
      Validation - Only during data entry "Selected"
      Allow user to override during data entry "Checked"

        • 1. Re: Date Calculation Flag
          philmodjunk

          If(GetAsNumber(Date Due) > 365 and Get ( Currentdate ) >= Date Due ;"DUE")

          • 2. Re: Date Calculation Flag
            bencraft

            Sweet!

            Thank you very much works like a champ and gives me many new options.

            • 3. Re: Date Calculation Flag
              LaRetta_1

              Hi Ben,

              Today should not be an auto-enter creation field.  It will not update.  You can delete this field.

              If(GetAsNumber(Date Due) > 365   

              This will always evaluate to TRUE unless the Date Due is prior to 12/31/0001.  ????

              So far I have the "DUE" flag working but it shows up even when no date is entered. How can I make it not show "DUE" even it does not have a date inputed?

              That second sentence contains so many double negatives that I'm not sure what you mean.  If you want a calculation to evaluate even if all referenced fields are empty then you UNCHECK 'Do not evaluate if all referenced fields are empty.'  Get() functions are not considered fields.

              Dates can be subtracted from one another and dates can also be compared.  There is no need to wrap with GetAsNumber().  So try this calculation:

              If [ Get ( CurrentDate ) >= Date Due ; "DUE" )

              For this to keep updated, you must go into the calculation Storage Options and check the box 'Do not store calculation results'.  And remember that, if you want the words DUE in the field even if the Date Entered is empty, uncheck 'Do not evaluate if all referenced fields are empty.'

               


              • 4. Re: Date Calculation Flag
                philmodjunk

                @LaRetta, take a look at this calculation:

                Date Due = Calculation = Date Entered + 365

                If Date Entered is empty, then GetAsnumber ( Date Due ) = 365 and this was the value he did not want to produce the "Due" result.

                • 5. Re: Date Calculation Flag
                  LaRetta_1

                  "If Date Entered is empty, then GetAsnumber ( Date Due ) = 365 and this was the value he did not want to produce the "Due" result."

                  If Date Entered is empty then the Date Due calculation produces nothing because the default is checked 'do not evaluate if all referenced fields are empty.  And the only field in this calculation is Date Entered. 

                  Even if Date Due is calculation of GetAsNumber( DateEntered ) + 365 and the 'do not evaluate' was UNCHECKED then the result would be 12/31/0001 and not 365. But GetAsNumber() should be dropped entirely!!

                  Okay, who are you and what have you done with the real Phil?

                  • 6. Re: Date Calculation Flag
                    philmodjunk

                    Same guy, what I suggested works, without having to figure out the date equivalent of an empty date field nor having to explain that detail to the OP. It also works regardless of whether "do not evaluate" is or is not selected. That made for what seemed to me to be a simpler, safer expression for someone who may well be a novice.

                    You can't just leave out that term in the calculation nor is is it "always true", but you can, as you have pointed out, use alternative approaches that also keep empty dates from producing a "Due" result.

                    • 7. Re: Date Calculation Flag
                      LaRetta_1

                      Phil said, "...without having to figure out the date equivalent of an empty date field nor having to explain that detail to the OP."

                      Figuring out date equivalent?  Nothing of the sort is required.  I explained the day-to-day realities when working with calculations and dates.  And even new people need to understand these basics (and use them). Twisting a calculation isn't the answer ... telling the new person how to properly utilize calculations is.

                      • 8. Re: Date Calculation Flag
                        LaRetta_1

                        Your calc:  If(GetAsNumber(Date Due) > 365 and Get ( Currentdate ) >= Date Due ;"DUE")

                        I called it twisting because you are solving the problem in the Date Tag calculation.  Ben said the Date Due calc was simple Date Entered + 365.  That simply would NOT produce a value if empty.  If he WAS getting a value, it would be a date 12/31/0001 (and he would only get that if he unchecked 'do not evaluate'); it is a date field. 

                        Bottom line ... If somehow his DateDue field is producing incorrect results of 365 or 12/31/0001, fixing it in the Date Tag calculation isn't the answer.  Fix the DateDue then fix the DateTag.

                        My calc: If [ Get ( CurrentDate ) >= Date Due ; "DUE" )

                        ... understand and properly utilize the checkboxes and storage options in your calculations.  If Ben doesn't fix the DateDue field (if it truly is producing incorrect results) then the problem won't go away and will haunt him whenever he uses that date for other reasons or displays it ... until he eventually does exactly as I've suggested. ;-]  Besides, your calc requires two evaluations and mine one (and they all add up).

                        • 9. Re: Date Calculation Flag
                          LaRetta_1

                          And truth is ... a Date Tag field isn't necessary at all.  It can be straight text on a layout using conditional formatting.  It would be simple Boolean test of:  Get ( CurrentDate ) >= Date Due