10 Replies Latest reply on Jul 16, 2011 1:31 PM by FarfelKnabe

    Question about sorting inside a field?

    FarfelKnabe

      Title

      Question about sorting inside a field?

      Post

      Hi all,

      Is there a way to change, or add, a alphabetical sort order to a text field with multiple values?

      In my case I have a text field with the value for "Dates", which is a field where I add a new date to a occurance - but keeping the old values, just adding a new value when needed.

      I have imported a bunch of posts from a txt file into a table and there I have my values in the appointed field. My problem is that all the date values are added chronologically - but I would like the field to show me the last entered date first!

      thanks

      /Andreas

        • 1. Re: Question about sorting inside a field?
          njem

          This is a little confusing. You mean you have a bunch of records and you want to sort them? Each record has a date field and an appointment field? You can of course sort records by any field in up or down order. Do you need to add another field that, rather than having the date of the appointment, has the date the record was added? And then sort by that?

          • 2. Re: Question about sorting inside a field?
            FentonJones

            I can't tell what you got either. Or are you saying you have multiple dates in a text field, with other text, and you want to somehow resort it by date? If so, then I would take it out into a text editor, split it into return-separated paragraphs, then reimport into a new FileMaker table (ID, Date, Note).

            • 3. Re: Question about sorting inside a field?
              RickWhitelaw

              Hi,

              The question is not clear. Nobody seems to understand what exactly you're asking. If you want help you should post the question again in a more thought-out and clear fashion.

              RW

              • 4. Re: Question about sorting inside a field?
                FarfelKnabe

                Yes and no,

                I thought I was quite clear in my explanation, but when I read the question as you did it I see why there is a bit unclear!

                I have a table with values containing the movies in my movie collection. In that table I have the regular fields such as Title, Release Year, Time, Format and so on. I have also a field for when I have watched that particular movie. A field called Viewing Dates. In this field I have entered the dates I have watched the movie in chronologically order, e.g. first date, second date, third date (2008-11-08, 2009-07-14, 2010-03-25). I am now realizing that doing so is good enough for my purposes, the sorting is just backwards in that field. The first date in that field (list if you want) should be the last date entered, so when I scroll through the table in table view mode I see only the most current date in that field.

                Maybe my problem can be solved in another way? Is there a way to add dates from the date picker into a form of a list or table and that having that list sorted as I want it!

                The thing here is that the values in that field must be easy to sort and search for so that I can pick out a specific date within that field.

                The solution Fenton Jones gives might work for me, but I have tried to cut and paste into a table view, or copy multiple rows from the table inside Filemaker but can't seem to figure out how to! Is there a way to mark a column or a row and copy that and likewise replace it with copied values (from Excel or other programs)?

                /Andreas

                • 5. Re: Question about sorting inside a field?
                  njem

                  It's unusual to have a number of entries in a single field. More typically you would have a related table with one date per record and maybe a portal showing a list of dates that go with a given movie. But to resort or separate the dates you have now my only guess would be to use a script with a loop that steps through the field character by character and when it gets to a space (if that's what separates dates) it takes the preceding characters as a date and either copies that into a new table with one date per record, or adds it to a new date field, adding them in the reverse order. So as you read dates left to right out of the old the get inserted in the new at the start of field. Look through the text handling functions and you'll find the calls to do that.

                  • 6. Re: Question about sorting inside a field?
                    FentonJones

                    The multiple "dates watched" would be shown in a portal, based on a relationship to the "dates watched" table, based on MovieID. The portal would be sorted descending by Date_watched.

                    The following script shows how to split up your existing (text) dates, and move them to a "movies watched dates" table, where the date is a real FileMaker Date field (which would sort descending properly in a portal). You would only run this once, after backing up your file (as a mistake could wipe out your existing dates).

                    # Move Text Dates to Dates Table

                    Show Custom Dialog [ Title: "Loop to Reset Dates to Table"; Message: "Will move the multiple dates to new records in a table. It
                     will NOT check for existing dates."; Buttons: "OK", "Cancel" ]
                    If [ Get (LastMessageChoice) = 2 ]
                     Exit Script [ ]
                    End If
                    #
                    Freeze Window
                    Go to Record/Request/Page [ First ]
                    Loop
                     If [ not IsEmpty (MO__Movies::DatesWatched) ]
                       Set Variable [ $id; Value: MO__Movies::ID ]
                       Set Variable [ $cnt; Value: ValueCount (MO__Movies::DatesWatched) ]
                       Set Variable [ $dates_txt; Value: MO__Movies::DatesWatched ]
                       Set Variable [ $counter; Value: 0 ]
                       #
                       Go to Layout [ "mo_DatesWatched" (mo_DatesWatched) ]
                       Loop
                         Set Variable [ $counter; Value: $counter + 1 ]
                         Exit Loop If [ $counter > $cnt ]
                         Set Variable [ $date_txt; Value: GetValue ($dates_txt; $counter) ]
                         Set Variable [ $date; Value: Let ( [
                          lines = Substitute ( $date_txt; ["-"; ¶]; ["/"; ¶] ); // either "-" or "/" OK
                          yr = GetValue ( lines; 1);
                          mo = GetValue ( lines; 2);
                          dy = GetValue ( lines; 3)
                           ];
                           Date ( mo; dy; yr ) // real Date
                           )]
                         #
                         New Record/Request
                         Set Field [ mo_DatesWatched::MovieID; $id ]
                         Set Field [ mo_DatesWatched::Date_Watched; $date ]
                       End Loop
                       #
                       Go to Layout [ original layout ]
                       Set Field [ MO__Movies::DatesWatched; "" ] // Optional, clear Dates_txt field; backup file first
                       Commit Records/Requests [ No dialog ]
                     End If
                     #
                     Go to Record/Request/Page [ Next; Exit after last ]
                    End Loop
                    Go to Record/Request/Page [ First ]

                    -

                    You'd also need a script to create a new Date, in the "dates watched" table. It would be run by a button next to the dates portal.

                    # [] New Date
                    #
                    Freeze Window
                    Set Variable [ $id; Value: MO__Movies::ID ]
                    Go to Layout [ "mo_DatesWatched" (mo_DatesWatched) ]
                    #
                    New Record/Request
                    Set Field [ mo_DatesWatched::MovieID; $id ]
                    Set Field [ mo_DatesWatched::Date_Watched; Get (CurrentDate) ]
                    Commit Records/Requests [ No dialog ]
                    #
                    Go to Layout [ original layout ]
                    Go to Field [ mo_DatesWatched::Date_Watched ]

                    • 7. Re: Question about sorting inside a field?
                      FarfelKnabe

                      Fenton, thanks for this,

                      I haven't tested this yet, but your suggestion is for sure a much better way to use this function and the button thingie with adding the current date automagically is just super (I never thought of that myself) - if I now only can get it to work!

                      I will begin to try and get the script to run and I will post a comment when I have success :)

                      /G

                      • 8. Re: Question about sorting inside a field?
                        FarfelKnabe

                        Hi again, I have a couple of follow up questions...

                        If I get this to work, that is having a field in the main table for Viewing Date and a related table with the actual dates, and the fancy button that adds the most current date to that field and automagically fills in that date in the related table, can I then have some kind of counter connected to the first field in the main table that counts the number of added dates, so that I in an easy way can see how many times a specific title is watched?

                        Another similar question is abot the Genres! Can I in a similar way, as the first example, copy all the entries within the Genres field from the main table into a related table? And maybe have a function (Portal) where I just add the Genres that are valid.

                        And last a question about the two values I have for Country of Origin and the Spoken Language of the movie. Is there a way that I can have the choice of language automagically filled in after I have picked the Country of Origin. Example - I enter a new movie into the database, this movie is produced in the US, so the Country of Origin is USA. The Language is then almost always English. Can the Language be filled in automatically on that choice? If a movie is from France can the Language be filled in automatically to French? But the field need to be able to accept more than one language (if there is more than one Spoken Language in the movie).

                        Thanks

                        /G

                        • 9. Re: Question about sorting inside a field?
                          FentonJones

                          "If I get this to work, that is having a field in the main table for Viewing Date and a related table with the actual dates,

                          There is a unique auto-entered (serial or UUID) ID field in the main table. There is a corresponding MovieID field in the "MovieDates" child table, as foreign key to tie them to the main table's "movie" record. There is NO "date viewed" field in the main table (unless you want to calculate a "last" date).

                          There is a "Portal", viewing the Date field in the "MovieDates" table. I don't know if you know what a Portal is. Read about it; it's crucial.

                          "and the fancy button that adds the most current date to that field and automagically fills in that date in the related table"

                          It adds the current date to the related table's Date field. It's just an auto-enter option. You can change the date afterwards if desired.

                          "can I then have some kind of counter connected to the first field in the main table that counts the number of added dates, so that I in an easy way can see how many times a specific title is watched?"

                          Count (MovieDates::Date)

                          -------------------------------------------

                          "Another similar question is abot the Genres! Can I in a similar way, as the first example, copy all the entries within the Genres field from the main table into a related table? And maybe have a function (Portal) where I just add the Genres that are valid."

                          There is a difference between "reference" values and "data entry" values. "ALL" Genres are "reference" values; you mostly just read them, to use their values in data entry fields. They can be done in three ways.

                          1. A Value List of Custom values. Just type 'em in. You'll have to keep them alphabetical, if you want 'em to appear that way.

                          2. A table for the Genre values. A Value List which "uses data from a field", that table.

                          3. A Value List which reads values from a field, reading the data entry field itself. The advantage is it's dynamic; there's no maintenance. The disadvanges is that there's nothing there to start with, and there's whatever people enter afterwards, incl. junk.

                          If there's not many genres, you could just use #1, a Custom Value List. Otherwise use #2.

                          -

                          As far as data entry to the Movie itself, it's kind of a toss-up. You could bust them out into a child table, almost exactly the same as Dates; except you may not need to Sort the Portal, and could add Genres into the Portal, automatically creating the portal row's record. That way you would not need a button/script to add Genres to a movie. The relationship would have [x] Allow creation of related records on.

                          OR, you could leave the Genres in the Movie table, as a multi-line text field, and control (via script) entering or deleting a value. This is more work. Or you could format it as a Checkbox, using its Value List. This however can take up a lot of screen space, if you have lots of genres. It also has the drawback that it will not recognize modified values (hence may require maintenance).

                          Keeping it as a multi-line text field in the main table would make Finds on Genre faster. Moving date to another table makes the Finds "unstored", which is slower. But these kind of simple "straight" relationships (ID=::ID) are generally fast enough, unless you have thousands (or more) records.

                          For your purposes, I'd do the related child table. Unless you're watching thousands and thousands of movies. It is the most flexible method, as it would easier to rearrange the genre values later, if desired (still a PITA, but easier than a multi-line field).

                          • 10. Re: Question about sorting inside a field?
                            FarfelKnabe

                            Hi all!

                            Better late then never, but here is a big greatful thanks for the help from all of you who have helped me on these questions.

                            I did manage to get a portal structure working with both Viewing Dates and Genres and also a automagically count of the added number of dates into the portal.

                            Thanks!

                            /Andreas