Problem with Lastname/Firstname order in Form

[cbauer50]

I have one more problem - and hope it will be my last ask for help here for a long time.

I have a customer form. On the left side, I have the "multistate button" (A, B, C, etc.) Next to it I inserted a Browse from the toolbox.

I have that set to a "browse" form which only has Lastname, Firstname.

When I open this form, the lastname is sorted alphabetically, but the firstname isn't (if I have more than 5 Smiths, it could be listed as:

Smith Mary
Smith Lucille
Smith Susan
Smith Ann

I can open the form, select that part of the form, click on "Query" and do a multi-sort, but how can I have it permanently sorted by Lastname, then Firstname so every time I open the form, it is sorted correctly.

I've tried everything I can think of and can't seem to get it fixed. It was this way when I used this form in v.5, and it bugged me then. I'd love to get it fixed now before I start using v.9 daily.

Thanks.

Carol

I have tried everything I can think of to

[Stan Mathews]

Re: Problem with Lastname/Firstname order in Form

I've tried everything I can think of and can't seem to get it fixed.

You have a knock off of the AlphaSports sample database, right? Did you investigate to see how the lastname sort is handled there? In this case the design notes might be of some use to you again.

[cbauer50]

Re: Problem with Lastname/Firstname order in Form

Yes, I did check the form from that database. The problem is, that form only has one field listed - Lastname - mine has 2 fields, Lastname & Firstname.

Carol

You have a knock off of the AlphaSports sample database, right? Did you investigate to see how the lastname sort is handled there? In this case the design notes might be of some use to you again.

[Stan Mathews]

Re: Problem with Lastname/Firstname order in Form

that form only has one field listed - Lastname

My copy of AlphaSports has a combo box above the browse which allows one to choose which of the table fields is shown in the browse. There is event code for the onchange event of the combo box which orders the browse by the chosen field.

Now, if you had a field in your table composed of lastname and first name and chose that field in the combo box, that would be the sort order.

You could do some fancy footwork in that onchange event to check to see if lastname was chosen. If it is change the code to order by lastname+firstname

if searchby.text = "lastname"
tbl.order(searchby.text+firstname,searchby.text+ <> \"\"")
else
tbl.order(searchby.text,searchby.text+ <> \"\"")
end if

[cbauer50]

Re: Problem with Lastname/Firstname order in Form

Looking at it more, that form is a little different than what I'm trying to do. I don't want to have a choice of selecting by a certain field. I just want the lastname/firstname listed for the customer. If I click on B, the customers whose names begin with B show up. I can scroll down to find the customer I want.

I'm including an image. If you look at the last name "Bailey" you will see they are not in the right order (Lastname/Firstname) like I want.

That is what I am trying to figure out - to have the names sorted first by lastname, and then by firstname. I can do it manually each time I open the form, but I would like to not have to do it each time.

Carol

My copy of AlphaSports has a combo box above the browse which allows one to choose which of the table fields is shown in the browse. There is event code for the onchange event of the combo box which orders the browse by the chosen field.

Now, if you had a field in your table composed of lastname and first name and chose that field in the combo box, that would be the sort order.

You could do some fancy footwork in that onchange event to check to see if lastname was chosen. If it is change the code to order by lastname+firstname

if searchby.text = "lastname"
tbl.order(searchby.text+firstname,searchby.text+ <> \"\"")
else
tbl.order(searchby.text,searchby.text+ <> \"\"")
end if

[Stan Mathews]

Re: Problem with Lastname/Firstname order in Form

Nice screenshot but a sample of your database would go a long way to getting help. All we can do at the moment is guess. How is the order of records set presently when the form opens? Do you have an order expression set in the form's properties?

If anyone else is watching, the proper code change for the onchange event of the searchby variable combo box on the AlphaSports customer information form would be

IF searchby.text = "lastname"
	tbl.order(searchby.text+"+firstname",searchby.text + " <> \"\"")
ELSE
	tbl.order(searchby.text,searchby.text + " <> \"\"")
END IF

to sort by lastname and firstname.

[cbauer50]

Re: Problem with Lastname/Firstname order in Form

I finally got it working! I clicked on the properties for the multistbtn (image below). I looked at the "OnChange" events. This is what was there:

'script for the multistate button on the Customer form in the invoice
'app



'' Filter the table
''
topparent:tables:customer.filter_expression = "left(LASTNAME, 1) = '" + mulstbtn1.text + "'"
topparent:tables:customer.order_expression = "LASTNAME"
topparent:tables:customer.query()

''
'' Get the table handle from the form
''
t = topparent:tables:customer.current()

''
'' Get the current record count
''
i = t.index_primary_get()
count = i.records_get()

''
'' If we found no records, then we put up a warning and show all records
''

if count = 0
ui_msg_box("Warning", "No records found. Showing all records")
topparent:tables:customer.filter_expression = ""
topparent:tables:customer.order_expression = "LASTNAME"
topparent:tables:customer.query()

end if

So I changed the 2 lines from:

topparent:tables:customer.order_expression = "LASTNAME"

to

topparent:tables:customer.order_expression = "LASTNAME + FIRSTNAME"

I never even looked at the multistbtn (A,B,C, etc). I thought it was in the browse sub-form that was causing the problem.

Thanks for all your help.

Carol