I think I got it.
The way this is set up, you'll only be able to use it for one select field, since several of the options are hard-coded into the subroutine. If you might want to use it for more than one form, I can give you that, too, but it can get pretty complicated to send all the variables to the sutroutine.
Code:
sub build_limited_multiple_select_field_from_other_db {
# --------------------------------------------------------
# Builds a SELECT field from an external database.
# Parameters are
# the column to which the value will be written -- $column
# a default value -- $value
# the height of the select field -- $size (optional)
my ($column, $value, $size) = @_;
my (@fields, $field, @selectfields, $selected, $fieldnum1, $fieldnum2,
@lines, $line, $output, $found, @values);
$size or ($size = 3);
@values = split (/\Q$db_delim\E/,$value);
# Be sure to change the following to match your database
# The name of the other .db file
$db_other_file_name = $db_script_path . "/default.db";
# The number of the field in the other .db file that holds the
# values you want in your select field
$fieldnum1 = 1;
# The number of the field in the other .db file that holds the userid.
$fieldnum2 = 2;
# End of things to edit.
for ($i = 0; $i <= $#db_cols; $i++) {
if ($column eq $db_cols[$i]) {
$found = 1;
last;
}
}
if (!$found) {
return "error building select field: no fields specified!";
}
open (DB, "<$db_other_file_name") or &cgierr("Error in build_select_field_from_other_db.
Unable to open $db_other_file_name. Reason: $!");
if ($db_use_flock) { flock(DB, 1); }
LINE: while (<DB> ) {
next if /^#/;
next if /^\s*$/;
$line = $_;
chomp ($line);
@fields = &split_decode ($line);
if ($fields[$fieldnum2] eq $db_userid) {
if (!(grep $_ eq $fields[$fieldnum1], @selectfields)) {
push (@selectfields, $fields[$fieldnum1]);
}
}
}
close DB;
$output = qq|<SELECT NAME="$column" MULTIPLE SIZE=$size><OPTION>---|;
foreach $field (sort @selectfields) {
$selected = 0;
foreach $value (@values) {
if ($value eq $field) {
$output .= "<OPTION SELECTED>$field";
$selected = 1;
}
}
unless ($selected) {
$output .= "<OPTION>$field";
}
}
$output .= "</SELECT>";
return $output;
}
To use it, add
Code:
|;
print &build_limited_multiple_select_field_from_other_db("FieldName",$rec{'FieldName'})|;
print qq|
If you want to make a different size field (in height), either change the 3 in the line
Code:
$size or ($size = 3);
or add it to the call to the subroutine. If you want to do the latter and have, for example, 5 options show in the list, you would use
Code:
|;
print &build_limited_multiple_select_field_from_other_db("FieldName",$rec{'FieldName'},5)|;
print qq|
------------------
JPD