Hello hello one and all. I have a question for you fine people regarding the build_select_field_from_other_db subroutine posted at http://webmagic.hypermart.net/.../db/text/multi11.txt. I've used this mod before without much trouble, but what I'm curious about is if its possible (and I imagine it is, without much trouble) to build a select field in a database using TWO FIELDS from another database.

For example, I have a database filled with Faculty records in one database. This database has a field for both "First Name" (FName) and "Last Name" (LName).

I then would like to have another database for faculty publications. For the "Author" field I would like to have a select field that is built from the faculty database which combines the FName and LName field.

Anybody have any ideas on this one? It seems to me like it would be really really simple, but I haven't been able to figure it out. Many thanks in advance!

 
Are you using the relational mod? If so and your key field for the faculty database is the UserID and you are using the same password file for both databases you can simply bring those fields into the publications add record form using the switch codes.

For instance:

&switch_to_faculty;
%rec2=&get_record($rec{'UserID'});
$rec{'Name'} = $rec2{'FName'} $rec2{'LName'};
&switch_to_publications;

Then in your field definitions create a field called 'Name' in your publications .cfg file and define it in the add form.

There are other way of doing this if you are not using the relational mod, you might want to check the FAQ under "Multiple" for examples.

Hope this helps

Unoffical DBMan FAQ

http://creativecomputingweb.com/dbman/index.shtml/

>>
$rec{'Name'} = $rec2{'FName'} $rec2{'LName'}
<<

That line would cause an error. You need a string terminator and " " around the $rec's or . in the middle

Hey there... Thanks for the response LoisC. I'm actually not using the Relational Mod in the version of DBMan I'm using, and I'm not sure it'd be worth my time to go about installing it for this little problem (Mind you, I haven't done the full-on Relational Mod with DBMan yet, so I'm not sure how hard/easy it really is).

But I feel like I oughta be able to use the following subroutine to do what I want:

http://www4.addr.com/...mford/subroutine.txt



Isn't there some way that I could use

if (!(grep $_ eq $fields[$other_fieldnum $other_fieldnum2], @selectfields)) {
push (@selectfields, $fields[$other_fieldnum $other_fieldnum2]);

If I defined those in my CFG? Thoughts?

I've tried a few ways to make this happen, all to no avail. I think its just some simple piece of Perl Code that I don't know because of how heavily I rely on the geniuses here and on the FAQ .


EDIT: Not as expansive a question as I wrote before the board treated my message all screwy. But, them's the breaks, I guess. Any thoughts?

I corrected the error I made above with the code I gave you.

Within the reference you quoted above it does also state:

To make multiple "build_select_field_from_other_db" fields, instead of code:

# Field number of the "Name" field in the other database
$other_fieldnum = 5;

you would need to have a list of all the fields in the other database:

# List of fields in other database
@other_db_cols = qw|ID Name Date Address|;

Then, in the code, after code:

my ($fieldnum, $found, $i) = 0;

add code:

for ($i = 0; $i <= $#other_db_cols; $i++) {
if ($column eq $other_db_cols[$i]) {
$other_fieldnum = $i; $found = 1;
last;
}
}
if (!$found) {
return "error building select field: no fields specified!";
}

Did you by any chance try that modification to see if it would work?

Unoffical DBMan FAQ

http://creativecomputingweb.com/dbman/index.shtml/

>>
I corrected the error I made above with the code I gave you.
<<

It will still cause an error without " " or .

It has to be:

$rec{'Name'} = "$rec2{'FName'} $rec2{'LName'}";

or

$rec{'Name'} = $rec2{'FName'} . $rec2{'LName'};

I just wanted to let you all know that I went ahead and used a Javascript fix for the problem, in case anyone ever wants to do something like this. I have the database an extra "hidden" field called "FullName". I then wrote a quick and dirty javascript so that when the form was submitted it would combine FName and LName into that field. Here is the code I used (I know its not Perl, but you never know when something like this will be useful to someone else!)

First a added a field called "FullName" in the .cfg file.

In html_record_form I put the following extra snippet at the end of my form:

<input type="hidden" name="FullName">

Then down in html_add_form I put:

print qq|

<script language="JavaScript"><!--
function textBuild() {
document.addForm.FullName.value = document.addForm.FName.value + ' ' + document.addForm.LName.value;
}
//--></script>
|;

Then I changed the form call in that subroutine to the following:

<FORM ENCTYPE="multipart/form-data" action="$db_script_url" method="POST" name="addForm" onSubmit="textBuild()">

It works like a dream. Now I can use the "build_select_field_from_other_db" on the "FullName" field and it works fine. Hope this helps someone out there in the future.

Many thanks for all the suggestions here! They were very helpful in ways to look at the problem. Cheers!

For Keyword searches in the forum:

build select field two fields other db