1 2 Previous Next 15 Replies Latest reply on Jan 20, 2017 8:07 AM by philmodjunk

    Consistent date formatting


      I have a mixture of date formats in an application - some dates are shown as 'dd/mm/yyyy', others as 'dd-mm-yyyy'.  I want them all to be 'dd/mm/yyy', so what I'm proposing to do is:

      1. Replace field contents on all dates, using a calculation Substitute ( Self  ; ["-";"/"] ; [".";"/"] ),
      2. Specify the same format under Data Formatting for each of the fields on every layout. 


      This will be tedious, but no big deal.  But is there an easier way to achieve this?  And is there a better practice for keeping consistency?





        • 1. Re: Consistent date formatting

          I am not sure if this would be any better for you than doing a replace across all records, but you can think about doing an export and then import. You will need a unique ID field in your table(s) for this to work. The key to this is the usually overlooked option during export of "Apply current layout's data formatting to exported data".


          Create a special layout for the table that holds your date field(s). Place the date field(s) on this layout. Set your desired formatting on the date field(s) on this layout. With all records in your found set (or whatever is appropriate), export with your ID field and the date field(s) and turn on this option. I would just use a Merge file type so that you get field names in the first row of the data.


          Then turn around and import back into your dB matching on your ID field. Of course, tell it to not apply auto-enter options.


          As a side note, where this is really handy is if you ever needed to merge FM dB's that use different date formats. Do this exporting trick to a master date format from the different databases and then you can import back in without issue. Also works for numbers where one system might have a comma as thousands separator while another might use a decimal for this.





          1 of 1 people found this helpful
          • 2. Re: Consistent date formatting

            One proviso here; you will find you cannot use Self in a Replace Field Contents command, but will have to name the field specifically. That said, what you propose should work OK, but make sure before you actually implement it—test the calc in the Data Viewer if you have FMAdvanced, alternatively create a temporary date field and use the calc to populate that field, then when you are sure you have the result you want, transfer the data from the temporary field back to the real field and then delete the temporary field.

            Looking ahead, to maintain consistency of date entry the main thing I do is set a dropdown calendar on any date field. If you have any perverse users who will bypass that and type dates in in their own way regardless, you can set up a self-replace auto entry calc on the field (the same one you propose to use here would probably suffice) and make sure you uncheck the "Do not relate existing contents" option. That way no matter how the date is entered it will always be replaced with the format of your choice.

            1 of 1 people found this helpful
            • 3. Re: Consistent date formatting

              Thanks Doug, that's really useful advice.


              • 4. Re: Consistent date formatting

                Thanks for this advice.  The strange thing is that I thought I had set up the format correctly in the past, but somewhere along the line it got re-set to 'As entered', and I think that's where the inconsistencies crept in.

                • 5. Re: Consistent date formatting

                  If you are going to specify a custom date format, I don't quite see a reason for also doing the replace field contents operation.

                  • 6. Re: Consistent date formatting

                    My view of that is that you wold use the Replace process to fix existing records only, then the custom date format takes care of all new records.

                    • 7. Re: Consistent date formatting

                      Yes, but the custom date format will affect the display of all dates, both old and new. Thus, the only thing being "fixed" is what appears when you edit the date. I suppose that's a reason to do this, it just seems trivial.

                      • 8. Re: Consistent date formatting

                        Well, changing the format doesn't seem to affect existing records, so the display of existing data is inconsistent. I could live with that, but I may decide to waste some time getting them all the same!


                        • 9. Re: Consistent date formatting

                          Then something isn't set up right. Specifying a custom date format in the inspector will apply that format to all records on that layout. It won't affect other layouts.


                          And that was my concern here as the need for Replace didn't seem justified.

                          • 10. Re: Consistent date formatting

                            Hmmm, thanks for pointing that out.  I'll try to work out what I've done/not done.

                            • 11. Re: Consistent date formatting

                              Ahh Phil! I see now we are talking at cross purposes. I was not referring to tinkering with the date display settings but rather the format of the field content itself. So just to be clear, as with other data formatting options set in the Inspector, the date formatting options set there have no effect on the actual format of the field content. Hence you could have a date entered as 20.2.16 but with different formatting settings appear as 20/02/2016 or Fri 20 Feb, 2016, or whatever. You can always see the format of the field content itself by clicking inside the field. What I suggested earlier was aimed at standardising the field content, not the display.

                              That said, I'm curious to know whether that is the difference Dave is reporting when he says: "changing the format doesn't seem to affect existing records". If by that you mean the actual content remains the same, then that is to be expected.

                              • 12. Re: Consistent date formatting

                                And those cross purposes are shown in the original post where both a Replace And a format change is listed. If you do one of these, you don't have much need to do the other.


                                And please note that your replace doesn't keep users from using alternate formats during data entry in the future so you might just stick with a specified display format and not worry so much about what it looks like during direct editing.

                                1 of 1 people found this helpful
                                • 13. Re: Consistent date formatting

                                  Also there is the script trigger OnObjectExit that you could run to keep data in one format.

                                  • 14. Re: Consistent date formatting

                                    Sorry for the confusion, which I now realise is down to a crucial detail missing from my original post.  As well as needing to sort out the display of existing date fields, I also have a couple of calc fields based on these dates (i.e. date and times of the session in one handy string).  Obviously, these depend on the way in which the data is held, and that is why I need to do the RFC on the actual date field, to modify the calc field on which it's based.  And also why I need to use a script trigger or auto-enter to police the new data getting entered.


                                    Thanks for all the input to this, which has really helped to clarify things for me.



                                    1 2 Previous Next