11 Replies Latest reply on Apr 29, 2011 8:47 AM by philmodjunk

    Get Date Script

    Terri

      Title

      Get Date Script

      Post

      This script is bringing back a ? for an answer. Can someone diagnose the problem for me? Thanks!

      If ( IsEmpty ( Catalogs::LCD Date ) ; GetAsDate ( Catalogs::PS:Press - 56 ))

      I want to fill the LCD date field, but I don't want to make it a calculation field because I want to be able to override it. So if the LCD date field is empty, then use the press date field and subtract 8 weeks to give the LCD date.

      I hope that makes sense.

      Thanks!

        • 1. Re: Get Date Script
          philmodjunk

          Check and see if Catalogs::PS:Press is a field of type date. If it's text, this calculation will return a negative number and that's an invalid date--which gives you the question mark. The same result will be returned if this field is of type date, but is empty for a given record.

          PS. if Catalogs::PS:Press is of type date, you do not need the GetasDate function.

          • 2. Re: Get Date Script
            MaxEh

            Terri,

            Not knowing all the other parts of your tables/relationships I did a quick db with just those two fields

            Both fields must be date fields.

            Enter the define field s dialogue (command-shift-D); highlight the LCD field.  If it is not a date field you will have to change it.  Click on 'options'

            and chose the auto-enter button/tab/window.

            At the bottom is 'Calculated value' and a 'Do not replace existing value (if any)' check box. Check both. Click on the 'Specify' button.

            The formula I used was: If (IsEmpty ( LCD Date ); PS Press Date - 56;LCD Date)

            What this will do is when a record is created it will automatically enter a date based on an empty LCD date.  This date can now be edited if you wish. If there is no date in the PS:Press then I too get a ?.  So what comes first the chicken or the egg?  Is a user required to make a PS:Press entry, or is a date auto entered in that field on record creation?  If so then no more ?

            Martin

            • 3. Re: Get Date Script
              Terri

              Thanks to both. This has been fixed. Both were date fields so it was my formula (I wasn't telling it what to do if the LCD was NOT empty).

              Martien Eh! - the ? is understandable and ok for records that do not yet have a press date

              • 4. Re: Get Date Script
                LaRetta_1

                "the ? is understandable and ok for records that do not yet have a press date"

                This can bite you.  If in future, you search for * to find all records with a date, any records with ? will be found as well.  Conversely, searching for empty dates using = will NOT find these records.

                The calculation should not produce a invalid value if there is not a true date in the field.  Invalid entries of any kind should be corrected.  Also, if you share data with external sources, such as MYSQL and you have invalid values it can crash them.

                • 5. Re: Get Date Script
                  Terri

                  That's a good point. I'll revise the calculation and let you know if I have any questions. Thanks!

                  • 6. Re: Get Date Script
                    Terri

                    Sorry this reply is late, but I'm hoping someone can help with this script to avoid the ? answer. The problem is:

                    If Date Field A is empty = Answer field should be blank.

                    If Date Field B is empty = Answer field should be blank.

                    But if Date Field A has a date = Answer field should be that date - 56 days.

                    Thanks!

                    • 7. Re: Get Date Script
                      philmodjunk

                      If ( date A AND date B ; Date A - 56 )

                      • 8. Re: Get Date Script
                        Terri

                        You got me on the right track. I was thinking too hard.

                        Should be If (date A AND date B; ""; Date A - 56)

                        • 9. Re: Get Date Script
                          philmodjunk

                          That will still give you a ? if Date B has a date and Date A is empty.

                          • 10. Re: Get Date Script
                            Terri

                            Luckily if A is empty, B will be empty. They both will either be populated or be empty. Thanks!

                            • 11. Re: Get Date Script
                              philmodjunk

                              Yep, need to drink more caffiene today... Embarassed