5 Replies Latest reply on Jul 4, 2013 5:11 AM by PeterWindle

    Dirty data in a date field


      Hello everyone!


      I have a client who has a date field that sucks data in from a php web form.

      Some of the data that gets put into the date field are simply not dates.

      This date field is being used in a calcualtion and the "bad" data is mucking that up.


      My question is simple, what's the simplest, quickest and most uniform way of testing for bad data in a date field? (non-date entries).

      I've tried a number of techniques and nothing has worked well for me, any suggestions are most welcomed!!

        • 1. Re: Dirty data in a date field

          You can make this field as Auto-enter calculation :

          if(GetAsDate ( datefield)="?";"";datefield)

          • 2. Re: Dirty data in a date field

            You could use If ( not IsValid ( self ) ; "" ) as your autoenter calc to remove non-date data on entry (don't forget to uncheck Do not replace existing value). That will work for future data entry.


            To remove existing non-date data you will need to run a looping script such as:


            Go to Record/Request/Page [ First ]


            If [ IsValid ( Yourfile::dateField ) ]


            Clear [ Yourfile::dateField ] [ Select ]

            End If

            Commit Records/Requests

            Go to Record/Request/Page [ Next; Exit after last ]

            End Loop

            • 3. Re: Dirty data in a date field

              Peter, chances are that the other dates are not clean either. If the PHP formatting of the date was bad then today's date would end up as the 7th April and the other dates with >12 would be the invalid dates.


              The PHP server must have all it's error checking turned off because it should reject the values of invalid dates before they are entered...


              I've done a lot with PHP & Aussie dates so happy to look at the code for you.


              - Lyndsay

              • 4. Re: Dirty data in a date field

                This is a nice clean way to tackle the problem. The only thing I'd add is that you might want to keep the entered data as there MAY be some logic to what is supplied that you could filter at a later date.


                So grab the data into a text field, then if valid push it straight into the date field, but if not you could run something more involved to generate a date.



                • 5. Re: Dirty data in a date field

                  Looks like isvalid is going to get me out of trouble, thanks everyone!


                  Lyndsay, looks like my client has a person who can take care of the php side already. Thanks anyway!