Column Sort in browse on a set with filter

[Fred Zag]

A few months ago I did some searching and eventually posted this issue on the v8 forum. I found folks with similar problems but no solution that worked for me.

I finally decided to bite the bullet and get v9 to see if the problem would go away. Well I think we all know how that approach usually ends up. :)

So here I am, with the same problem, but with a few more facts.

If I create a browse (stand alone or imbedded) based on a set and try to sort using column sort it works as expected. If I use a filter, the column sort works on fields that belong to the parent table but not on fields from the child table.

The set has two tables, one to one, with no imbedded filter.

Any thoughts?

[Tom Cone Jr]

Re: Column Sort in browse on a set with filter

Sure, it's a beautiful day in the neighborhood. :)

If you seek corroboration, how about posting a sample with instructions for us to follow?

It's not clear to me how you're sorting the columns. I think the tools on the toolbar (quick sort, for e.g.) are designed to work only with the parent table. What's your goal? button click column headers? All columns sortable?

In my own work I find that sorting columns is useful in one or two columns only on most browses. Additional functionality would never be used. If you always need to sort on a child table field and if your set is one to one, why not reverse the order of the tables in your set?

[Fred Zag]

Re: Column Sort in browse on a set with filter

Sure, it's a beautiful day in the neighborhood. :)

It's not clear to me how you're sorting the columns. I think the tools on the toolbar (quick sort, for e.g.) are designed to work only with the parent table. What's your goal? button click column headers? All columns sortable?

In my own work I find that sorting columns is useful in one or two columns only on most browses. Additional functionality would never be used. If you always need to sort on a child table field and if your set is one to one, why not reverse the order of the tables in your set?

Thanks Tom,

I'm not so much seeking corroboration as I am looking for other folks who have had the problem to see if they have been able to resolve the issue. I believe the consensus was that Alpha was aware of the problem but users were unable to provide a sample for Alpha to work on. And I would like to know if there has been any progress on that.

To clarify:

Stand alone browse with no filter - Quick Sort (toolbar) and Column sort (right click on column header) both work as expected on parent and child fields.
Stand alone browse with filter on a parent table field - Both sorts work as expected on parent fields but unpredictable on child fields.

To elaborate, the sort on a child field might work ascending but not descending. Or not work on both. Behavior changes depending on which columns you try to sort first. Sort columnns in one sequence, close the browse and sort columns in a different sequence and the columns which do not sort correctly may change.

The common denominator is the presence/absence of a filter on a field in the parent table, eg SalesmanID="S00".

Goal? Yes, sort multiple columns via buttons. Why? A reasonable request by the user.

Turning the set "upside down" would demand a one to many relationship, eg., parent is "Customer", child is "CustomerActivity" would demand a one to many relationship.

And finally, I've kept this description to a stand alone browse, but I started with an imbedded browse and a browse on a form and the behavior is the same. Again the common denominator is the use of a filter, either at the browse properties or the form properties level.

So I'd like to know if anyone has found a fix or if Alpha would like to look at the issue.

And again thanks,

[Davidk]

Re: Column Sort in browse on a set with filter

I've got a small app that displays 4 list boxes on a form and a couple of browses. I can click on an item in any list box and have the browse filtered on that selection. In the OnInit event I sort the browse on 4 columns with this code...

FUNCTION Query_Fitness AS C ( )
	
topparent:Browse2.activate()	
dim s as p
s = set.open_session("setinventorymaint.set")
dim tbl as p
tbl = s.table_get()
query.filter = "flattenquery("+".t."+")"
query.order = "Location+Category+Manufacturer+Desc"
query.options = ""
query.description = "Saved Query:qrySortInventoryRecords"
tbl.query_create()
s.close()


END FUNCTION

When a list box item is selected I set a variable "filt" to a filter string and then run this code...

:frmFitnessInventory:browse1.BaseQueryRun(filt)

"Filt" is something like...

filt = "Location = '" + varLocation + "'" + " .and. Category = '" + varCategory + "'"  + " .and. Manufacturer = '" + varManf + "'" + " .and. Desc = '" + varDesc + "'"

So I'm running a filter against a browse and sorting the browse on 4 fields in code. I'm not sure if this is close to what you want to do. Let me know.

[Fred Zag]

Re: Column Sort in browse on a set with filter

Hi Dave,

Thanks for taking time to post the detailed reply. I read through it several times but I'm still on my first cup of coffee so my head is still a bit fuzzy, so forgive if I misread something.

If I'm correct your form is based on a two table, one-to-one browse. The browse on-init event sorts the records on Location, Category, Manufacturer and Description. The user can then filter the displayed records by those same fields? Neat.

I hope I got that right. You sort first and then vary the filter. I want to filter first and then vary the sort. If so it's a bit different from what I'm doing, but I may be able to make it work. But first one question. Those four sort fields - do they all come from the parent table? Do any of them come from the child table?

If so, it may work for me. I see that you do filter the set when you build the original query. With some changes I could put my query there and then use your technique to create the sorts.

Again, my brain is a bit caffeine deprived at the moment, so I'm not sure I'm thinking straight. Let me know about the origin of the four fields and I'll give it a shot. Meanwhile, I submitted a bug report to Alpha. Maybe I'll get lucky and they will come back with a solution.

Thanks Dave,

[Davidk]

Re: Column Sort in browse on a set with filter

Hi Fred,

Yes... your understanding is correct. Although, the form is based on a set with 2 tables in a one-to-many layout... but the many side doesn't really affect what you're trying to do.

There are 2 browses... the first is a list of inventory records. The second (the many side) is a list of associated maintenance records - 1 or more for each inventory item.

As the Inventory browse is filtered, but maintenance browse is automatically filtered because of the set.

Even thought I sort the initial browse and then let a user filter, I see no reason why it can't be done the reverse.

[Fred Zag]

Re: Column Sort in browse on a set with filter

Thanks again for the help. Unfortunately, same result. Even without a base filter on the form nor the set, as soon as I apply any filter, sort goes bonkers.

So I'm waiting to see what Alpha has to say about it. Called today to be certain they received my bug report. Now until I think of another approach I guess I just wait. Fortunately (or maybe unfortunately) there's plenty other work to do around here. :)

[Davidk]

Re: Column Sort in browse on a set with filter

Is there a way you could post your database or a database with the form and sets, tables so we could have a look?

[MikeC]

Re: Column Sort in browse on a set with filter

Fred,

waiting to see what Alpha has to say about it......I guess I just wait

:) Two offers to see if a true bug/work-around can be found (I wouldn't mind playing a bit either BTW!!). Entirely your choice of course.

[Fred Zag]

Re: Column Sort in browse on a set with filter

Thanks guys, very generous offer. Just seeing if the problem follows to a different platform would be great.

Check your email.

Thanks,

[Davidk]

Re: Column Sort in browse on a set with filter

Well this sure seems like a bug to me.

Using the browse with no filter, the child column sorts work fine. Adding a filter to the browse produced unexpected results in child column sorts - and different results a-z, z-a each time they're applied.

I created a form based on the set and added a browse with columns from the parent and child tables. Exactly the same problems.

I would pack this up and ship it off to Alpha - it shouldn't behave like this.

[MikeC]

Re: Column Sort in browse on a set with filter

I am fairly certain this issue first manifested itself in v8 quite some time ago....I do not have the thread handy but it did involve a very similar (if not the exact) scenario. I think a work-around was given then, but would have to find the thread to be certain.

[Fred Zag]

Re: Column Sort in browse on a set with filter

Yes, I remember searching v8 several weeks back and find at least a couple references to workarounds. If I remember correctly the problems described were different. Tried them anyway, but they didn't work. If I remember correctly the inverted set is what was unique to this problem.

I did try a few things with that sample I sent and noticed something strange. After sorting on the child record fields, the field that pops up next to the scroll bar had gibberish instead of a valid field value. I compacted and it went away and I didn't have time to try to reproduce it but will see if I can over the weekend.

Thanks for the help guys.

[Fred Zag]

Re: Column Sort in browse on a set with filter

I received a couple suggestions for workarounds but nothing that appears pertinent to this problem. It has also been a week since I submitted a bug report to Alpha, but no response. Not that I'm expecting a fix overnight or anything. But I would think a week should be long enough to acknowledge the report.

What has been your experience with reported bugs? I know I've had Selwyn respond on the forum to problems I've had in the past. I was very pleasantly shocked. Given the history of browse issues over the years and the frequent response that Alpha has been able to reproduce them, I would think they would be anxious to see if they have a repeatable problem. But the absence of a response as well as the lack of any of the Alpha folks chiming in here gets me wondering. Everyone's busy working on v10? Could be, I guess.

Well, fortunately there's no shortage of other work around here. :)