[Closed] Table and related view, search in view same form

[dreamquartz]

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

[eremmel]

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

[Villeroy]

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

Code: Select all

SELECT * FROM "View" 

may be a good workaround.

[dreamquartz]

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

sits @ fPerson->fAddressShown->lbPersonID, so the Client can identify an entry.
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

[dreamquartz]

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

Code: Select all

SELECT * FROM "View" 

may be a good workaround.

Please see also my comments to @eremmel.

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

[dreamquartz]

I consider this closed.

Will keep on looking for a solution.

dream