7 Replies Latest reply on May 26, 2011 9:24 AM by EricSchweinhagen

    Sort by date within a single field

    EricSchweinhagen

      Title

      Sort by date within a single field

      Post

      Not new to FileMaker, but have recently converted an old database from Now Up-To-Date and Contact to FileMaker Pro 11. Conversion went fairly well. However, all of the notes from each contact went into a single field. That's okay, I can manage client notes that way. My problem is that the information is in chronological order starting with the oldest entry at the top, and the newest at the bottom, or end of the text. That means I've got to scroll down a long, long way to get to the most recent information.

      Question: is there any way to have this field reverse that chronological order, so that the most recently dated info appears at the top, or beginning of the text?

        • 1. Re: Sort by date within a single field
          philmodjunk

          Since the text is all within one field, there's no simple way to do this. If each note were in a separate record with a date field to record the date the note was entered, it would simply be a matter of sorting these records either in a list/table view or from within a portal.

          If we new the exact format of the data in your notes field, we might be able to come up with a script that successfully splits your single Note entry into multiple records so that this sort order can be produced.

          • 2. Re: Sort by date within a single field
            EricSchweinhagen

            By multiple records, do mean separate fields for each note? If so I'm not sure that would work. Or is Record meaning something else?

            • 3. Re: Sort by date within a single field
              philmodjunk

              No, separate related records for each note. These notes could then be displayed in a portal that sorts by date in descending order to put the most recent note at the top.

              • 4. Re: Sort by date within a single field
                EricSchweinhagen

                Okay, so if each of my 4,000 clients is already a separate record, with many notes, sometimes hundreds (currently appearing in one large, scrollable field).

                Are you suggesting there's a way to create a separate record for each note that would then tie back to a single existing client record? For instance, on May 15 I create a note for Client 5. Create another note for Client 66, and anther one for Client 34. Then on May 16 I create a note for Client 7, one for Client 51, and a new note for Client 5. In this scenario, each of the notes is it's own record, yet stays linked to the Client record to which it applies?

                Hopefully that wasn't too confusing.

                • 5. Re: Sort by date within a single field
                  philmodjunk

                  Yes, that's basic relational database design.

                  You can see an example of this in the Contact Manager  starter solution that comes with FileMaker where notes are recorded in a portal.

                  • 6. Re: Sort by date within a single field
                    LaRetta_1

                    "Okay, so if each of my 4,000 clients is already a separate record,  with  many notes, sometimes hundreds (currently appearing in one large,   scrollable field)."

                    And this is precisely why each note should be  a record in a related table. You need a Clients table with a unique  ClientID and relate that to a notes table which also holds this  ClientID.  Your problems will disappear when this is structured  properly.  It may seem daunting to convert what you have but it is NOT;  I've converted many structures to records.

                    One of the many  problems with using one text field is that, when you want to search for  date, how do you know that the date is even properly entered?  User may  type 3/14 and not put a year.  How do you know the year it represents?   Or the User could type Sept 14.  You have no control; you cannot find  notes and Users will get frustrated as the notes FIELD becomes  unmanageable.  By having records, you can add global filters to filter  the notes down - by year, by User who created the note and so forth.  And you can also generate a report which sorts summarized by all of these factors as well.

                    • 7. Re: Sort by date within a single field
                      EricSchweinhagen

                      Clearly I've been thinking about this in 1 dimension and need to rethink. Thanks!