If you are sorting a found set (as opposed to records in a portal or a sorted relationship), your sort order (and a sub summary layout part for that matter) can specify a field from a related table.
Thanks Phil! I tried that, but it seems to make my sort much slower when accessing the database remotely. Am I doing something wrong?
No, sorting on a related field will result in slower performance. Sorting on an unstored calculation field that copies that value is probably just as slow or slower.
You could auto-enter data from the job table to get a faster sort field.
Are you saying to create a field in lines and auto enter data from the jobs table into that field? Is there a way to get it to auto enter when the data is changed? I know about unchecking "do not replace existing value.." but I was under the impression that would only update when data within the field's table was changed.
If you are grouping by Job, you would auto-enter a job ID for grouping. Is such likely to change? An update would onlly be needed if the line item were assigned to a different job than where it originated.
I already have a field like that in place, but wouldn't the job ID field need to be first in the sort order to keep all line items from a job together? I'd like the other four sort fields to be the primary sort fields, but to still group the other line items from the job with them.
No matter whether related or in the local table, the field by which you want to group records has to come before the fields used to order the records within the group.
Yeah.. my original plan was to create calc fields combining a value based on each of the original sort fields, and the job id. But that slowed things down a lot. I also tried something similar with ExecuteSQL, but that also slowed things down.
"combining a value based on each of the original sort fields,"
Why would you you need to do that?
That was one way I could come up with to get it to work.
For example, I created this calc field in the lines table:
; "" ; "¶" ;lines::_kf_jobs_id );1)
I also tried this as a calc field in lines:
Both seem to work, but only as unstored calcs, so it makes things slower.
And then I also tried sorting by similar fields in the jobs table with the same problem, that they made things a lot slower.
That doesn't answer my question.
What problem does this solve? (Get what to work?)
I thought your report was based on lines, with the need to sort on a field in jobs, so why are you setting up a calculation to pull data from lines into jobs?
Yeah, I guess there was no reason to set the fields up in jobs. The two examples I sent were created as fields in the lines table. But I did try something similar in jobs.. I guess I just thought, since I was trying to group them by the job id it might make sense to sort from that table.
Just to clarify more, I was thinking if I created the field in jobs, I could gather the data for all the related lines, and choose which one to sort by.
Similar to the example I included in my previous post, I created a field like this in jobs:
and created similar fields for the other sort values.
That way I could use those to sort by and group the jobs together.
But once again, unstored calc = slow.
And again, that really doesn't make sense.
Makes more sense to base the layout on lines and to sort the lines records.
Then you have all these fields in a local table, not a related table and they will sort.
But maybe that isn't what you need?