9 Replies Latest reply on Aug 23, 2014 1:41 PM by erolst

    Sort field values?

    benniemc

      Each record contains up to twenty formatted text fields containing data. I have looped through the records and assembled a single text field with all of the data. Is it possible to now sort this field and display the records as a list?

        • 1. Re: Sort field values?
          Stephen Huston

          Sure, though if it makes more sense to sort on specific pieces, you can also use the original fields for sorting on one or more fields.

           

          Depending on the number of records and length of the combo field your sorting may improve with indexing ON for the field(s) on which you sort.

           

          Another option for displaying this way might be to use a merge-field text block without the need for the script generated combo field, as:

          <<field1>> <<field2>> <<field3>> <<field4>> etc.

          • 2. Re: Sort field values?
            erolst

            benniemc wrote:

            Each record contains up to twenty formatted text fields containing data.

             

            You're sure these data shouldn't better be stored as related records? Can you describe your scenario in more detail?

            • 3. Re: Sort field values?
              benniemc

              Thanks for the suggestions. I'm afraid I didn't explain my problem very well.

               

              I have twenty date fields in each record. I can concatenate other data fields of the record (such as status, name, action, etc.), as well as deadlines with which to compare the date to indicate how cirtical the time remaning is for the action to be performed (status). I have created a list of all of these data strings and put it into a field called "Milestones." Now, I would like to sort the "Milestones" field (or the list itself) according to the status ("Critical", "Urgent", etc.). I cannot sort the records, since each one has several date fields. I can display the "Milestones" field in a popover, but cannot figure out how to sort it in any order except record order.

               

              Unfortunately, unless I misunderstood, the suggestions won't help me much.

              • 4. Re: Sort field values?
                erolst

                benniemc wrote:

                Unfortunately, unless I misunderstood, the suggestions won't help me much.

                 

                Well, I didn't directly make a suggestion – it was more like a semi-rhetoric question; but be assured that it will help you anyway.

                 

                20 fields with the same / a very similar function in one table normally means a non-normalized structure, the limits of which you're presently experiencing.

                 

                If each of your records can have x dates, each with its own status, then these data should be stored in related records, and not within fields in the main table. Then you can (among other things) display the child records in a portal sorted by date, event type etc. – and of course you can use that child table to create a list/report of dates/events across the parent records.

                • 5. Re: Sort field values?
                  breezer

                  I agree with erolst, it may be that your data model may be flawed, however, if you really have to keep as is, then maybe consider sorting the records using the option "custom order based on value list" where your value list will be based on your "Milestones" or type in your own sort order. If you can post a sample file, we may be able to help you better.

                  1 of 1 people found this helpful
                  • 6. Re: Sort field values?
                    Stephen Huston

                    And you can still sort based on the individual date fields, including mutliple sort-levels.

                    Sort Order:

                    1. Field1
                    2. Field2
                    3. etc, all in a single sort operation.
                      Of course, if some of those data fields are empty, including those in the sort sequence may give unintended results.
                    • 7. Re: Sort field values?
                      Stephen Huston

                      You could also have a status field update base on the last date field in the sequence to be filled in, and then test that date against today to determine a substatus, such as

                       

                           Last Milestone Date: Completed (but no date for Billed) = Completed / Billing Required

                      • 8. Re: Sort field values?
                        benniemc

                        Perhaps a sample would better illustrate my problem. I've tried using related records, but that seems to create other problems.

                        • 9. Re: Sort field values?
                          erolst

                          benniemc wrote:

                          I've tried using related records, but that seems to create other problems

                           

                          I've no idea why you would say that, and what those problems would be. See attached file.

                          1 of 1 people found this helpful