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?
benniemc wrote:Unfortunately, unless I misunderstood, the suggestions won't help me much.
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.
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.
benniemc wrote:Each record contains up to twenty formatted text fields containing data.
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?
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.
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.
And you can still sort based on the individual date fields, including mutliple sort-levels.
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
Perhaps a sample would better illustrate my problem. I've tried using related records, but that seems to create other problems.
benniemc wrote:I've tried using related records, but that seems to create other problems
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.
Retrieving data ...