Hello All!

I've been at this for a while now, so please excuse me if this post doesn't make any sense, I'm a little sleepy!

Here's what I'm trying to do.

Create an inventory tracking and value database for my wife.

-I have the DB configurated and now working on the HTML.

-JavaScripts will perform a few functions in the forms for auto input in to
relational fields. ie. 1 liter = 33.8 oz etc.

-There will be 4 users of the DB total ... 3 Authors & 1 Admin (me)

-There will be several "Date" fields on the forms and "Date" fields will be a
key search parameter.

-As a result, I'll need a "Date" DB to select from on the forms.
Have read through the forum and know how to accomplish this feat.
I don't want to go and add a date every time my wife wants to add a new
entry, nor does she want to edit multiple DB's.
In addition, I would prefer to not set up another DB dedicated to dates,
just a supplemental DB to the default.db like default.dates
The default.dates file would only have two fields, ID & Date

-Now the Trick.
I want to have a form on the add form page that will allow the user to input a
new date and submit it to the default.dates file and simply reload the add form
page in the process. This would allow the user to then select the new date(s)
during data entry.

-Now for the real Trick.
I want to do the same for "Source", "Category", and "Location" fields as well, without a different sub add_field_select_db_record or whatever the name
will be for each field.
I'd like to use the same sub for all and have the form tell the cgi which DB to
use.
Each DB will only have ID & "'Name'" fields.

-Other Requirements.
Like "sub signup" the new sub would have to check if the input already exists in
the target DB.

I have read several posts about categories but couldn't find the user add
category information in my searches. I figured since this system has so
many parts to it, it would be helpful if the whole concept was explained whether
I knew what to do or not. As a result, this post is rather long and for that
I apologize.
But I figured it would be better to post it all so the solution integrates into
the whole system thus cutting down on too many threads.

Any help would be great.
beetleman

Marcus L. Griswold

If I understand you correctly, the routine you're looking for should do this:

- on an entry form, you have several fields with dropdown lists. Values are retrieved from special files like "default.dates", "default.names".
- you want to enable users to either select an item from the list or enter a new one, by clicking on a link (for example).
- when the user has entered a new one, they should be redirected to the original entry form with the newly entered values already present.

Have I understood this correctly?
kellner

kellner,

Exactly!

And I thought I was incoherent when I was typing last night.

Just one thing, I would prefer that the "Add" link used the same routine for all
of the default.aux files. I understand that that would significantly complicate
the coding, but would allow easier user interfacing.

Thanks for the responce.
beetleman

Marcus L. Griswold

Well, if you want the "add" link to be the same, and you have several fields on the form to which this mechanism is applied, how should the script know which field in particular it is to be applied to?

You can possibly simplify the code that runs in the background by coding one uniform routine that takes the fieldname as a value and generates the link, but the links would have to be present for each field you want to do this with.
kellner

OK, the following would be what I'd do; in fact I did something very similar just yesterday ...

I refer to the database setup that you're actually in as "database1".

Note that all databases - i.e. database1 and all the "auxiliary" databases - need to share the same user/password-file.

1) It would be the easiest if you have a db-setup defined for each of the "auxiliary" databases, and if the db-setup has the same name as the field in your entry form - that way you can use dbman's built-in "add record" function; otherwise, you'd have to code your own.

Example: "default dates" are contained in "dates.db", so you have a "dates.cfg" in your dbman directory, a subdirectory to it called "dates" and all files for "dates" are contained in that subdirectory ("dates.db", "dates.log", "dates.count").

2) In html_record_form, to each field you want to print the link to, add:
print qq|<br><a href="$db_script_url?db=fieldname&uid=$db_uid&add_form=1&referrer=database1">Add fieldname</a><br>|;
Replace "fieldname" for the actual fieldname.
See 4) for "referrer=database1" - used for coding the backlink later.

This would actually be easier if you use auto_generate for your forms.
You'd then have to change build_html_record_form in db.cgi a bit, but building the link could be automatized.

3) How to build the select fields from e.g. "dates.db" when you're in "database1.db"?

I suggest modifying build_select_field_from_db in db.cgi so that it doesn't build a select field from the *current* database, but from *some* database expressly specified.
You can then build the select fields from e.g. "dates.db" like this:
print &build_select_field_from_db("dates", "date", "$rec{'date'}", "date");
"dates" .......... database setup name
"date" (second parameter) .......... column name to build that select field
"$rec{'date'}" ............. if this is a modify or delete form, the actual database value will be selected
"date" ............... name of the select field to be built

Like this:
Notes: you need to specify the location of the database file if it's not in $db_script_path = dbman directory.
In my code, I assumed that database files reside in subdirectories to $db_script_path that are named after them.
Example: "names.db" would be in "/home/cgi-bin/dbman/names/names.db" if $db_script_path = "/home/cgi-bin/dbman".
Change this if you have them in another directory, but if this is to work for *any* database, your database path conventions must be consistent.

The following subroutine is used to get the database column names for any database, provided that the cfg-file is stored in $db_script_path. It's primitive and assumes that your database field names are printed at the beginning of a line between single quotes (so take care nothing else is :-)).

4) Lastly, how to build a "back to add form for database1" link when you're adding default values?
Note that in the link under 1) I added "referrer=database1".
Now, if you're in the add form to "dates.db", you can easily print this code wherever you want to print that "back to ..." link. I'd suggest printing it in the database menu/footer:
But you probably also, and most importantly, want to use that link in the "add success"-page after having added the record!
For this purpose, you need to add this code to html_record_form:
(works with or without preview mod)

Now, if you click on the link back to the database1 add form, the select field is automatically recreated. As the "auxiliary" database will have changed in the meantime, the newly added value will be there.

One drawback: If you had already filled in other fields in the database1 add form, then went to the auxiliary database to add a record and came back, all field values that had already been filled in before will be gone. I'm sure there would be a way to have them "waiting" there, writing a cookie or writing them to a temporary text file.




kellner

kellner,

Thanks for the help.

A couple of things though.

I was hoping that the Aux DBs would not be independant from the active DB,
instead would act more like the default.pass DB thus eliminating the need
for any password file sharing.

Also, I'd like the "Add" link to be a form, much like the signup form, but actually on the same page as the record add form with a select box to
determine which DB the data is to be written to. OnSubmit would reload the
"Add" page thus updating the select boxes. Obviously, it would be best if
the rountine would check for duplicates thus eliminating the need for an
ID field possibly.

Something like this I think from the form would work.

So there would need to be the routine "sub aux".

Am I making this too complicated?

Thanks again for the information, I'll continue studying it to see if I can
adapt it for my needs.

beetleman

Marcus L. Griswold

No. If you have several of these "auxiliary" databases, like "names", "dates", etc., and each of them have *only one field*, that would be simpler than what I suggested.

The build_select_field_from_db that I suggested can be used for that as well.

You'd call the link to the "aux" database like this, after every field where you want to do this:
<a href="$db_script_url?db=$db_setup&uid=$db_uid&add_form=1&aux=fieldname">Add value to list</a>

You can code the second form straight into html_add_form.

In db.cgi, you should add/change three things:

- to validate_record, straight at the beginning after the variable declarations:
- to add_record, after "if $status eq "ok"":


- And at the end, you'd probably not want to call html_add_success when a record was added to the auxiliary database, but go back to html_add_form:

kellner

kellner,

Wow!

Your profile says your level is "user", maybe a moderator should upgrade that!

I'd spent the time since my last reply fumbling over the original information
you sent but couldn't figure out how to adapt it. My problem is I have the
logic of a programer but the knowledge of a novice.

Also I'd been looking into the idea of just using JavaScripts to replace the
last option in the select field with new data. As you may already know,
JavaScript and the select field don't play well together.

But like the light at the end of the tunnel, there was your reply!

The new codes look like they'll work great for my needs.

A couple of questions though.
1. How does this work?Does it point to default.aux1, default.aux2,... or to aux1.db, aux2.db,... If the second, how would I modify to the first option. maybe -or am I missing the point?

2. Is the "aux" DB configured with a *.cfg or stand alone as a "text" file?
Explaination: will the cgi accept the file as default.aux1 or will it only get
information from a fully configured DB (aux.db, aux.cfg, aux.pass, aux.count, aux.log).

3. What is the call to the build_select_field_from_db going to be?

4. What modifications, if any, need to be done to build_select_field_from_db?

By the way, I'm glad you know what you're doing, because to be honest, I'm a litte lost right now. While I understand the codes you gave me well enough to know that's what I need, some of the small, yet very important details elude me.

Thanks for the help, my wife is really getting impatient to use her new DB.


beetleman

Marcus L. Griswold

No, you are correct. My "or maybe" code would have produced "whateveritisyouenterintheauxfield.db". To get what you want, you can use your code all right.
No, you don't need a cfg. I rewrote this to *just* use a text file. You don't need to create any field definitions and so on. I would suggest, however, *not* using "aux1", "aux2" or whatever as filenames or identifiers of filenames, but using the names for those fields you want to use them for. Makes tieing them to forms easier!
OK, I just figured out that you shouldn't use the build_select_field_from_db, because it assumes you open a database with cfg-file and all that.
Use this instead to call the sub:

print &build_select_field_from_aux("fieldname", "$rec{'fieldname'}");

and this sub:


I have not included any checks for duplicates when reading the aux-file, because we already have a duplicate check when adding to the file.

I hope you're wife's impatience can now come to an end!


kellner

kellner,

Thanks a bunch. I'll get it all put together and run a few test entries to see how it all works out. After looking through everything, I'm not too worried about the end result.

Thanks again.

Group:
I'll post a recap and the final code after I get running and fully debugged.
If you have not read the entire thread, I should inform you that I had the idea only, and the coding came from kellner.

I hope this will be useful for everyone.

beetleman

Marcus L. Griswold

kellner,


Well, a few bugs! But it does work!?

First:
When I submit a value to the "aux.db", I get:
"Add Failure"
Cannot leave ID blank
Cannot leave Name blank
---> And the value wasn't added to the "aux.db".

So I turned off the field entry required in the *.cfg.
I can now add to the "aux.db" but each time I do, a blank record is added to the "real" database. Just the ID number is listed.

That wouldn't be so bad if the information already entered was saved as well and I pointed the db.cgi sub add_record redirect from html_add_form to html_modify_form. Hopefully with the ID passed along so it automatically went to the right record.
Also, I'd really like for the ID and Name to be required for submission.

I guess, if I put an onClick in the link to the aux form to auto submit the record and further seperated the add form and the aux form it would all work as desired.

But right now if I'm adding record #25 and I go to the aux form with an onClick, record #26 (blank) would be submitted when a new value was added to aux.db.

So, how do I compleatly seperate the add form from the aux form and still be able to use the add_record routine in the cgi.


By the way, just letting you know now. I'll probably want to add a sort to the sub build_select_field_from_aux at some point in time along with the before metioned forward to html_modify_form with the ID information passed along the way.

Thanks,
beetleman

Marcus L. Griswold

OK, here we have several possibilities:

- work on the approach we already have, for which case I would like you to send the necessary subroutines so I can look at the code: html_add_form, add_record, html_add_success, html_record_form, and the build_select_from_file (or whatever you called it) which I posted last time. - Just for checking.

- change to an approach with different entry forms for main and auxiliary database. If you want to maintain the auxiliary databases *without* their own cfg-files, you can't use add_record without coding so many exceptions to the cfg-case that it won't be worth your while, so in that case I would suggest using a different sub add_aux altogether.

As I don't like wasting efforts already undertaken, I'd suggest we stick with the first possibility and see what is needed to get things work. There might always be the one magical line in your code we need to find


kellner

One bug I spotted: In the code I gave you for validate_record and add_record, there are the two lines:

my $db = "default" . $in{'aux'};# as you like it!

my $value = $in{$db};



Change the last line to:

my $value = $in{$in{'aux'}};

Otherwise the script will look for an input field named "default.names", and that's not what we want.
kellner

kellner,

Here are the routines you asked for +.







I did make a few modifications to the html_add_form as I didn't want to redo general layout.

In html_record_form you'll see a third value entered in the build_select... call
(Date1 and Date2) this is where $name in the sub build_select... gets its variable.

As said before, Everything works, just not quite perfect yet.

This DB is just a testing area before the real thing gets underway.
The actual DB will have about 50 fields (10 date fields) so you can probably understand why I've setup a testing area. Fewer possibly problems in debugging!

For visual reference:
Beetleman's Test DB
user/pass
kellner/kellner

Feel free to add, modify, delete, etc... To get the full picture.
No real data listed.

Thanks,
beetleman

Marcus L. Griswold

kellner.,

I made the change to sub validate_record and sub add_record

Form:
my $value = $in{'aux'};

To:
my $value = $in{$in{'aux'}};

And no values were added to the aux.db

so I changed it back.


beetleman

Marcus L. Griswold

>>my $value = $in{$in{'aux'}}; <<

That is creating a key in the %in hash using the value of $in{aux} as the key name. No value is given to that key so $value is undefined hence nothing is inserted.

In plain text it would look like:

%in = ( some_value => undefined );

PaulW,

So I should leave it as:
my $value = $in{'aux'}; ?

Unfortunatly, I got in over my head about three days ago.
kellner and I have been trying to get it working and alot of work has been put in to the compleation and it seems very close.

But like I said, I'm lost at this point in time and I think that kellner may be getting to the end of his abilities soon as well, so any help here would be great!

Thanks,

beetleman
a.k.a: clueless

beetleman

Marcus L. Griswold

Sorry for not replying so long, but for some reason I had not received your last message by e-mail, and I hadn't looked at the forum for a few days.

Leave the code with $in{'aux'} as it was. Paul misunderstood the data structure. I tested this bit, and it works as it should. If there's anything wrong, it must be something else.

If you're still at this, please post the subroutines I asked you about, I can't help you any further without looking at the code.
kellner

>>Paul misunderstood the data structure.<<

Did I?

Yes, I think so.

The code in question was:

my $value = $in{$in{'aux'}};


You wrote:

That is creating a key in the %in hash using the value of $in{aux} as the key name. No value is given to that key so $value is undefined hence nothing is inserted.

That's not correct.

The form contains an input field named "aux" and one named after the value of "aux".

So $in{'aux'} could be "default_dates" - it has a value! -, and $in{$in{'aux'}} is the value of the field "default_dates".




kellner

What I said was correct.

If you have a field...aux with a value of "foo" then:

$in{aux} = "foo";

If you then do:

$value = $in{$in{aux}};

....then you are saying, $value equals $in{foo} which has no value so $value would be undefined.

You would be correct if the foo field had a value, which I believe it does if I understood your last post right?

So I guess we were both correct (I just didn't know there was a second field )

Still seems strange. Why can't you do $value = $in{field2} instead of $in{$in{field1}}

Yes, Paul, that's why I said you *misunderstood* my code. I didn't say it wasn't correct.

The reason why I adopted this approach was simply that the name of the aux field can vary, as this may be called from different input forms. The logic was: (a) the aux field informs on the auxiliary database name, (b) the field named after the auxiliary database contains the value to be added to that database. At least that's how I understood beetleman's situation.

If you have a better idea to solve the original problem, go ahead.

beetleman, sorry I overlooked that you'd already posted the subs I asked you for. I'll take a look at them and let you know whether I find something.


kellner

OK, before this is getting too complicated, what with suggesting to modify this or that line and everyone gets confused, I'll start over on the basis of the code we already have. Plus, the problems you described show that using dbman's add_record routine won't work.
I had overlooked that: dbman of course expects a proper record to be added when add_record, but when adding a value to the auxiliary file, you don't do that, because you separated the two routines in your html_add_form - therefore it won't work.
So let's separate the two routines "add a record" and "add a value to an auxiliary database".

- Use this to build the select field:

sub build_select_field_from_aux {
#Builds a select field from an auxilary database (text file).
my $aux = shift; # name of database
my $value = shift; # value
my $name = shift;
my @select_values;
open (AUX, "<$aux") || &cgierr("Can't open $aux: $!");
while (my $line = <AUX>) {
chomp($line);
push (@select_values, $line); }
close (AUX);
my $out = qq|<select name="$name">|;
foreach my $select_value (@select_values) {
if ($select_value eq $value) {
$out .= qq|<option selected>$select_value|;}
else { $out .= qq|<option>$select_value|;}
}
$out .= qq|</select><br>|;
$out .= qq|<a href="$db_script_url?db=$db_setup&uid=$db_uid&add_aux_form=$aux">Add a value to the list</a>|;
return ($out); }

1) use this code to build the select list in html_record_form:
print &build_select_field_from_aux("test.dates", "$rec{'Date2'}", "Date2");

2) in db.cgi, sub main, add these two lines to all the elsif lines you see there:
elsif($in{'add_aux_form'}) { if ($per_add) { &html_add_aux_form;} else {&html_unauth;}}
elsif($in{'add_aux'}) { if ($per_add) { &html_add_aux;} else {&html_unauth;}}

2) add this sub:

sub html_add_aux_form {
my $aux = $in{'add_aux_form'};
$page_title = "Add a value to $aux";
&html_page_top;
print qq|<form action="$db_script_url" method="post">
<input type="hidden" name="db" value="$db_setup">
<input type="hidden" name="uid" value="$db_uid">
Please enter the value to add to the auxiliary file:<br>
<input type="text" name="$aux" size="30" maxlength="50">
<input type="hidden" name="aux" value="$aux">
<br>
<input type="submit" name="submit" value="submit"></form>|;
&html_page_bottom;
}

sub html_add_aux {
my $db = $in{'aux'};
my $value = $in{$db};
open (FILE, ">>$db") || &cgierr("can't write to $db in html_add_aux: $!");
print FILE "$value\n";
close (FILE);
$page_title ="Value added to $aux";
&html_page_top;
The value "$value" was successfully added to the auxiliary database "$aux".<br<
Please click <a href="$db_script_url?db=$db_setup&uid=$db_uid&add_form=1">here</a> to return to the original database add form.<br>|;
&html_page_bottom;
}

And, lastly, revert add_record, html_add_form etc. to how they were before the earlier modifications.

This should basically do the trick. Let me know whether it works.
Note that modifying and deleting records from the auxiliary database is not yet coded, nor is the duplicate check, but let's first see whether this works.
kellner

Hey guys,

A lot of new posts yesterday... Thank You!

I'll look over everthing and give it a shot. I've been logging all changes to the script, so I can post the entire project when completed, it also helps in finding bugs and returning to the correct area for additional modification not to mention a way to get back to the original code when needed. I've played with it a bit but as I said, I'm in well over my head and my fate rests in your hands.

kellner, sorry that you didn't get the notification, I must have replied to Paul when I should have replied to you.

Anyway, it's Christmas Eve and I celebrate so it'll be a few days before I make the changes. My advice to you, log off for a couple of days and enjoy one of the holidays if that's your thing or just relax. For some of us its been a rough year and we could all use a break.

Happy Holidays.

Logging Off For Christmas:
beetleman

Marcus L. Griswold

Kellner,

Yes I'm still alive!

Didn't mean to leave you hanging! Had a large computer crash. After 6 years of use my IBM Aptiva finally called it quits. Motherboard is fried. So I've spent the past several weeks trying to shop for, purchase, and assemble a new PC. Its wonderful, I forgot how useful having a computer at home could be.
The computers at the library just aren't up to date but the connection to the web is quick. Well, I'm still transfering 6 years of data from my old hard drive so I don't have full access to all of my old database info.

My database project will have to wait until my PC is a little more usable, ie. all of my DB and Text editors reinstalled. Switched OS's and the change over to XP isn't nearly as easy as MicroSoft would have you believe.
beetleman

Marcus L. Griswold