Gossamer Forum
Home : Products : DBMan : Discussions :

Last try: Select field also pulls related data

Quote Reply
Last try: Select field also pulls related data
Most kindly I have been referred to the FAQ regarding this question, but it just seems as if there isn't any example that touches exactly this specific question and my knowledge of PL is quite limited.

I'll try to explain it the easy way:

This is my database (example):

xxxxx;School;City,Country;xxxxxxx

I would like to have a select-box (here it is):

&build_select_field_from_db("School", "$rec{'School'}")

Now, the select-box pulls out the School's name (so, we can say that this specific school exists in the database).

That school also has a related city and a country in the same record.

When $rec{'School'} pulls the school's name I want the script to also pull the city and the country of that specific school.

Then all the three would be filled in the School and the City and the Country fields automatically and the user that chose the school wouldn't have to fill in a city called Seatle when there is already a city callede Seattle in the database.

Anyone who can help me other than referring me to the FAQ, please reply!

CSky


Quote Reply
Re: Last try: Select field also pulls related data In reply to
CSky, I haven't worked with &build_select_field_from_db however, using the info you've provided, it appears you are pulling up the School Name by using the $rec{'School'} fieldname for displaying. Have you tried something like this?

&build_select_field_from_db("School", "$rec{'School'} $rec{'City'} $rec{'Country'}")

Quote Reply
Re: Last try: Select field also pulls related data In reply to
Thank you, Karen, a good try, but that's not possible.

And &build_select_field_from_db("School", "$rec{'School'},$rec{'City'},$rec{'Country'}") separated by commas isn't possible either.

If you have any more suggestions, please don't hesitate to share them.

CSky


Quote Reply
Re: Last try: Select field also pulls related data In reply to
In Reply To:
When $rec{'School'} pulls the school's name I want the script to also pull the city and the country of that specific school.
CSky, are you meaning that when a record is added, you want the scripts to identify the City & Country associated with a particular School already in the db?

In Reply To:
Then all the three would be filled in the School and the City and the Country fields automatically and the user that chose the school wouldn't have to fill in a city called Seatle when there is already a city callede Seattle in the database.
I don't know how your db(s) is/are set-up however, as a usual, each database record contains a value for each needed field - although many records may have the same value for a given field, without the value being entered for the particular record, the record is incomplete.

The only way I see around that would be to have "Schools" as a separate (relational) database - Each school would then have fields populated with the specifics of the school.

Perhaps I'm misunderstanding what you're attempting to do. Sorry :-(



Quote Reply
Re: Last try: Select field also pulls related data In reply to
Karen, thank you for your concern

Answer to your first question: YES!

Answer to your second : NO! A separate database is out of question, because that would require that I'd enter 6000 schools and their addresses all by myself.

And of course: You said: "without the value being entered for the particular record, the record is incomplete."

Note: I became aware of this problem when a friend of mine spelt every entry with small letters while I spelt every entry with a capital first letter. We didn't turn up together in searchresults although we live in the same city.


Here some in-depth explanation for you Karen (and for everyone interested):

What I have now (example):
---------------------------------

When signing up, the add form shows up requiring that you enter

- School
- City
- Country

At the top of the form I have three select fields. They contain School, City and Country -information that have already been entered by a one or a few other users that signed up before.

In order to make people spell their school the same way as others did before (if already existing in the database because somebody else enterd it already), I want them to give the possibility to choose an existing spelling of the school, else the new user might spell "Saint Peter school" and an older entry was spelled "St Peter School" which would be the SAME school spelled in two different ways.

So now they can choose an already existing School, City and Country in three different select-boxes.

print &build_select_field_from_db("School",$rec{'School'});
print &build_select_field_from_db("City",$rec{'City'});
print &build_select_field_from_db("Country",$rec{'Country'});

But, they have to make a selection in each and every one of them. When they have made their (existing!) selections, they click on a button that I call "Copy!". Then the entire Add-form reloads (I call the add_form once more, see code below) with the selected data in the required fields. So, the formerly empty fields now contain School=St Peter School, City=St Petersburg, Country=Russia.

<input type="hidden" name="db" value="$db_setup">
<input type="hidden" name="uid" value="$db_uid">
<input type="hidden" name="add_form" value="1">
<INPUT TYPE="SUBMIT" NAME="" VALUE="Copy!">



Now, what i would like to have:
---------------------------------

When users sign up, facing the Add-form, I would like to give them the possibility to choose from only ONE select box at the top of the form instead of three, because if they choose St Peter School, it is obvious that the city of that school is St. Petersburg and the country is Russia.

print &build_select_field_from_db("School",$rec{'School'}); # OR SOME ROUTINE LIKE THAT ONE

So, when the script pulls the data from an existing record who's owner goes to the St Peter School, it might as well pull the name of the city and the country and then (after "Copy!") fill it in the three fields called School, City and Country.



Is this understandable?


/CSky


Quote Reply
Re: Last try: Select field also pulls related data In reply to
I have not seen what you are wanting to do done before. I have a feeling it would really slow down the function of your database if it had to look and compare several fields. It would be much quicker to just have people enter the city and country.

What might help is to include instructions within your html_record_form so people will know for instance not to use abbreviations. Show an example of how you want the name of the school field filled in (to prevent dups with different spellings).

For instance above the field for the school name include something like this:
"Please do not use abbreviations for your school name, each school name should be spelled out in full (i.e. St. Peters would be entered as Saint Peters)"

I have found if you provide instructions and examples users are likely to follow your examples and help to keep things consistent within the database.

You can also use the following:

Make every word in a field Upper Case

$rec{'FieldName'} =~ s/(\w+)/\u\L$1/g; ## change words to start in upper case

Place this at the beginning of html_record form, and html_record or html_record_long.

This would prevent the problem with having people enter in all lowercase and having the entries being duplicated due to the case.

Hope this helps a little

Unoffical DBMan FAQ
http://webmagic.hypermart.net/dbman/
Quote Reply
Re: Last try: Select field also pulls related data In reply to
Thank you, Lois, I have now the feeling that finally somebody (exactly) understands what I mean.

I too have thought of the possibility that it may slow down the database, but then: All the &build_select_field_from_db calls slow down the script a bit.

What I had in mind isn't an ordinary, often to use routine. With that I mean that a database search as the one I want, would not slow down more than any other build_select_field_from_db-search. The field to be searched for is given, it's the field called School, called by &build_select_field_from_db("School", "$rec{'School'}"); - a quite slowing-down routine but still a standard routine in DBMan.

The next search (which is the "Copy!"-search according to my description, must be even quicker than the build_select_field_from_db search, because one field is given (example: St Peter School), so the script would have only to look for St Peter School in the School-column and return two more values from the same record, the City and the Country!

Lois, you wrote: "Hope this helps a little". Yes, thank you, Lois, I am grateful, but like you say: Just a little.

Now, if you know the programmer that writes that special code for me, please let her/him contact me for that challenge. I am of course willing to pay for that service if it isn't challenge enough to provide this for free by somebody who reads this.

/CSky