[Solved] Sort on Surname, GivenName NOT on FKPersonID

Discuss the database features
Post Reply
dreamquartz
Posts: 881
Joined: Mon May 30, 2011 4:02 am

[Solved] Sort on Surname, GivenName NOT on FKPersonID

Post by dreamquartz »

Hello All,

Something that eluded me for a long time, and trying to pick it up again.
I have 4 tables
tPerson
tAddress
tCompany
tEmployee

A Person can be an Employee @ different Addresses (more than 1 job; part time or something similar, or even representing more than one company under the same roof).
To accommodate this issue the table tEmployee has been created.
In tEmployee there is a link to tPerson and tAddress
tAddress has a link to tCompany.
I would like to be able to sort the Person based on Surname, GivenName while showing them as an Employee @ a certain Address for a Company.
I have attached a sample database for this purpose.

Can someone provide me some insight?

Dream
Attachments
Sort Names.odb
(13.35 KiB) Downloaded 181 times
Last edited by dreamquartz on Sun Jul 28, 2019 6:25 am, edited 1 time in total.
LO 7.x, HSQLDB 2.7.x & Ubuntu 22.04 LTS.
chrisb
Posts: 298
Joined: Mon Jun 07, 2010 4:16 pm

Re: Sort on Surname, GivenName NOT on FKPersonID

Post by chrisb »

1) open form for edit
2) open navigator & properties
3) in navigator hit internal form 'fEmployee'
4) hit properties 'Data' tab
5) paste the code into the sort dialogue & hit enter

Code: Select all

(select "Surname" || ',' || "GivenName" from "tPerson" where "PersonID"="FKPersonID")
open office 4.1.14 & LibreOffice 6.4.4.2 x64 using HSQL 1.8.0.10 (Embedded) and HSQL 2.6.0 (Split) on Windows 10
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Sort on Surname, GivenName NOT on FKPersonID

Post by Villeroy »

Or bind fEmployee to statement

Code: Select all

SELECT * 
FROM tEmployee 
ORDER BY (select "Surname" || ',' || "GivenName" from "tPerson" where "PersonID"="FKPersonID") ASC
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
dreamquartz
Posts: 881
Joined: Mon May 30, 2011 4:02 am

Re: Sort on Surname, GivenName NOT on FKPersonID

Post by dreamquartz »

My problem is that the listbox in question in fEmployee is used to select a person from the table tPerson and add them as an employee and/or select an excisting employee so relevant contact information can be added (therefore becoming a ContactPerson), to that specific Company Address.
The moment a person is selected, their Foreign Key FKPersonID will be added to tEmployee.
Navigating the same listbox with the use of the navigation buttons (forward, back, beginning, end) will therefore show the employees for that Company Address.

Because there could already be employees to the company, registered via fPerson, there is a listbox, the one in question, to do the selection for a ContactPerson.
There could be, however, no person who is to be considered a ContactPerson.
As a result, a person (Surnmane, GivenName) can be added to tPerson (via an independant main form, shown in the same screen of fCompany) who can then be selected from that table after a "Refresh Form".

As a side Note:
When communicating with a company, you communicate with a person.
That ContactPerson is an employee of the Address of the company.
For design purposes, the ContactPerson is normally not entered in the form fPerson, and linked there to the company in question, because that is an extra step.

My question is, how to be able to navigate the listbox and show the names of the Employees in alphabetical order, and also able to select and add a new employee.

The suggestions provide the option to order employees, but that is limited to employees.

Dream
LO 7.x, HSQLDB 2.7.x & Ubuntu 22.04 LTS.
eremmel
Posts: 1080
Joined: Tue Dec 30, 2008 1:15 am

Re: Sort on Surname, GivenName NOT on FKPersonID

Post by eremmel »

Make two queries that match with the two sets that you defined.
In the two queries add 'token' to indicate an existing or a new employee (Eg. prepend surename with '= ' / '@ ')
Join the query results with UNION ALL
Sort on surname and the existing employees sort on top, new on bottom.
It's Microsoft marketing that tells you computers are qualified for non-technicians
W11 22H2 (build 22621), LO 7.4.2.3(x64)
dreamquartz
Posts: 881
Joined: Mon May 30, 2011 4:02 am

Re: Sort on Surname, GivenName NOT on FKPersonID

Post by dreamquartz »

eremmel wrote:Make two queries that match with the two sets that you defined.
In the two queries add 'token' to indicate an existing or a new employee (Eg. prepend surename with '= ' / '@ ')
Join the query results with UNION ALL
Sort on surname and the existing employees sort on top, new on bottom.
This is an idea.
I am still trying to figure out how to solve it in my situation.

Will keep you posted,

Dream
LO 7.x, HSQLDB 2.7.x & Ubuntu 22.04 LTS.
dreamquartz
Posts: 881
Joined: Mon May 30, 2011 4:02 am

Re: Sort on Surname, GivenName NOT on FKPersonID

Post by dreamquartz »

Hi eremmel,
eremmel wrote:Make two queries that match with the two sets that you defined.
In the two queries add 'token' to indicate an existing or a new employee (Eg. prepend surename with '= ' / '@ ')
Join the query results with UNION ALL
Sort on surname and the existing employees sort on top, new on bottom.
Working on this one, it turns out that there is a Data Type error.
incompatible data types in combination sql status: 42562 Error code: -5562
.
I need therefore to change a Data Type from Text [VARCHAR] to Integer [INTEGER] on the fly.

I have searching for a while now, and trying different things, but cannot seem to solve this one.

I know there must be something I can add in a Query to make it happen.

Can you please advise?

Is it still hot where you are?

Dream
LO 7.x, HSQLDB 2.7.x & Ubuntu 22.04 LTS.
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Sort on Surname, GivenName NOT on FKPersonID

Post by Villeroy »

What is wrong with the example file with foreign keys ordered by surname, forename?
Attachments
DummyPersons2.odb
Filters, soundex and ordered FK examples
(48.68 KiB) Downloaded 175 times
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
dreamquartz
Posts: 881
Joined: Mon May 30, 2011 4:02 am

Re: Sort on Surname, GivenName NOT on FKPersonID

Post by dreamquartz »

Villeroy wrote:What is wrong with the example file with foreign keys ordered by surname, forename?
Master Villeroy,

I am humbled by your expertise.
Your example from above does work.

I made some serious wrong assumptions.

I was able to modify your example for both situations I needed them for.
The following shows all the related Employees, followed by all Persons, listed in tPerson.
"ContactPerson" as Boolean, shows the selected Employee as the point of contact 1st in the listing.

Code: Select all

SELECT
	 *
FROM
	 "tEmployee"
ORDER BY
	 "ContactPerson" DESC,
	 (
		 SELECT
			 "Surname" || ',' ||
				 "GivenName"
		 FROM
			 "tPerson"
		 WHERE
			 "PersonID" = "FKPersonID"
	 ) ASC
Very similar to above, where now "InActive" as a Boolean shows those related Addresses last. The link still has to exist for historical purposes.
The order of the addresses is determined by the building location (stored in "tAddressBare") and not the complete address which could start with a unit number, which is stored in "tAddress" and represented in the view "vAddress".

Code: Select all

SELECT
	 *
 FROM
	 "tAddress"
 ORDER BY
	 "InActive" DESC,
	 (
		 SELECT
			 "AddressBare"
		 FROM
			 "vAddressBare"
 WHERE
			 "vAddressBare"."AddressBareID" = "tAddress"."FKAddressBareID"
	 ) ASC
Thank you so much and sorry for not paying attention,

Dream

PS: I consider this SOLVED
LO 7.x, HSQLDB 2.7.x & Ubuntu 22.04 LTS.
Post Reply