[Solved] Sort on Surname, GivenName NOT on FKPersonID
-
- Posts: 881
- Joined: Mon May 30, 2011 4:02 am
[Solved] Sort on Surname, GivenName NOT on FKPersonID
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
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 182 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.
Re: Sort on Surname, GivenName NOT on FKPersonID
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
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
Re: Sort on Surname, GivenName NOT on FKPersonID
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
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
-
- Posts: 881
- Joined: Mon May 30, 2011 4:02 am
Re: Sort on Surname, GivenName NOT on FKPersonID
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
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.
Re: Sort on Surname, GivenName NOT on FKPersonID
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.
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)
W11 22H2 (build 22621), LO 7.4.2.3(x64)
-
- Posts: 881
- Joined: Mon May 30, 2011 4:02 am
Re: Sort on Surname, GivenName NOT on FKPersonID
This is an idea.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.
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.
-
- Posts: 881
- Joined: Mon May 30, 2011 4:02 am
Re: Sort on Surname, GivenName NOT on FKPersonID
Hi eremmel,
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
Working on this one, it turns out that there is a Data Type error.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.
.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.
Re: Sort on Surname, GivenName NOT on FKPersonID
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 176 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
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
-
- Posts: 881
- Joined: Mon May 30, 2011 4:02 am
Re: Sort on Surname, GivenName NOT on FKPersonID
Master Villeroy,Villeroy wrote:What is wrong with the example file with foreign keys ordered by surname, forename?
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
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
Dream
PS: I consider this SOLVED
LO 7.x, HSQLDB 2.7.x & Ubuntu 22.04 LTS.