Hi All,
Does any one know how to deal with the following:
I have a form where I enter personal data, called fPerson.
I fill out Surname, GivenName (combo box [CB]) , MiddleInitial(s) (CB), Nickname (CB), and DOB (date field) in the table tPerson, located in the Main form.
I use triggers to make sure the entries are unique.
This is a requirement from the Client, because they deal with projects related to a specific person.
In related subforms I fill out address, email, phone/fax info.
Because there are people with the same Surname and GivenName, the trigger to control the entry will fire.
Now I need to find that specific person in the form, when that happens.
I would like to be able to search in the vPersonInfo, where "tPerson"."Surnmane" || ' , ' || "tPerson"."GivenName" AS "PersonID" is stored, without leaving the form fPerson, and the relevant information shows up in their specific entry fields.
At this point in time I can search on "tPerson"."Surname', but some of the Surnames are quite plentiful.
I can search from bottom up and top down in the field, but that can take a long time, especially because there are close to 15,000 people entered already.
Any advise/guidance is appreciated,
Dream
[Closed] Table and related view, search in view same form
-
- Posts: 881
- Joined: Mon May 30, 2011 4:02 am
[Closed] Table and related view, search in view same form
Last edited by dreamquartz on Sat Jun 18, 2022 7:40 am, edited 1 time in total.
LO 7.x, HSQLDB 2.7.x & Ubuntu 22.04 LTS.
Re: Table and related view, search in view same form
Hi Dream, 15,000 records is not that much for a database. Did you analyzed the queries and consider a good indexing strategy for you important queries?
You go over a view I guess, because you mentioned vPersonInfo. Such views can be very expensive when indexes are missing as well. You might craft a dedicated query to return quickly the needed data to investigate the collision.
As usual: gratefulness contributions to the red cross
You go over a view I guess, because you mentioned vPersonInfo. Such views can be very expensive when indexes are missing as well. You might craft a dedicated query to return quickly the needed data to investigate the collision.
As usual: gratefulness contributions to the red cross
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)
Re: Table and related view, search in view same form
As far as I remember, direct SQL (as in views) does not work with form/subform relations. Subforms are not filtered by their respective parent form. A parsed query like
may be a good workaround.
Code: Select all
SELECT * FROM "View"
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: Table and related view, search in view same form
eremmel wrote: ↑Fri Jun 03, 2022 5:22 pm Hi Dream, 15,000 records is not that much for a database. Did you analyzed the queries and consider a good indexing strategy for you important queries?
You go over a view I guess, because you mentioned vPersonInfo. Such views can be very expensive when indexes are missing as well. You might craft a dedicated query to return quickly the needed data to investigate the collision.
As usual: gratefulness contributions to the red cross
Still a lot for the Client.
It is all manual input, based on handwritten Registration forms, due to regulatory requirements (signature required).
Everything is indexed left-right-and centre.
It appears a basic principle from my simple line of thought.
I have a secondary form, not part of fPerson, where I can simply search a standardized concatenated representation of the Personal info.
However, it means that the Client needs to 'leave' the input form, do a search, record/write down the PersonID, go back to the input form and search based on the PersonID, which is the PK for tPerson (the table where the personal information is recorded).
I would like to have the option to search the above mentioned standardized representation in the input form.
It is even shown on the input form.
This code
Code: Select all
SELECT
CASE WHEN "tPerson"."GivenName" IS NULL
THEN
COALESCE ( "tPerson"."Surname", '' )
ELSE
COALESCE ( "tPerson"."Surname" || ',', '' )
END ||
CASE WHEN "tPerson"."Surname" IS NULL
THEN
COALESCE ( "tPerson"."GivenName", '' )
ELSE
COALESCE ( ' ' || "tPerson"."GivenName", '' )
END ||
COALESCE ( ' (' || "tPerson"."Nickname" || ')', '' ) ||
COALESCE ( ' ' || "tPerson"."MiddleInitial(s)", '' ) ||
COALESCE ( ' ' || "tPerson"."Suffix", '' ) ||
COALESCE ( ' (' || TO_CHAR( "tPerson"."DateOfBirth", 'MM/DD/YYYY' ) || ')', '' ) "Person",
"PersonID"
FROM
"tPerson"
ORDER BY
"Person" ASC
But as indicated, this might take a while, because the Client needs to go 'step' through the records manually.
Please see the attached basic layout for the form fPerson.
Because it is shown on the form, there should be a way to search, yes???
Thanks,
Dream
- Attachments
-
- Screenshot from 2022-06-03 11-46-29.png (12.4 KiB) Viewed 1106 times
-
- Screenshot from 2022-06-03 11-49-02.png (40.31 KiB) Viewed 1106 times
LO 7.x, HSQLDB 2.7.x & Ubuntu 22.04 LTS.
-
- Posts: 881
- Joined: Mon May 30, 2011 4:02 am
Re: Table and related view, search in view same form
Please see also my comments to @eremmel.Villeroy wrote: ↑Fri Jun 03, 2022 5:53 pm As far as I remember, direct SQL (as in views) does not work with form/subform relations. Subforms are not filtered by their respective parent form. A parsed query likemay be a good workaround.Code: Select all
SELECT * FROM "View"
Would that then mean I have to 'elevate, the subform to mainform level to be able to search?
It would be nice by simply having a a box that can show the concatenated information on the input form, but it simply is not recorded.
Dream
LO 7.x, HSQLDB 2.7.x & Ubuntu 22.04 LTS.
-
- Posts: 881
- Joined: Mon May 30, 2011 4:02 am
Re: Table and related view, search in view same form
I consider this closed.
Will keep on looking for a solution.
dream
Will keep on looking for a solution.
dream
LO 7.x, HSQLDB 2.7.x & Ubuntu 22.04 LTS.