I am adding a subroutine will open a specific file (an Adobe Acrobat file) in a separate window.

This works great when I have a static file name, I just have a list of links with each link looking like:

print qq!<dd><li><a href="$db_pdf_url/$rpt_barcode" target="_blank" name="Barcode" title="Districtwide Barcode Report"><b>Barcode Report</b></A><BR>! if ($per_reports);


Now I have a form, that has a select box (built from a separate DB) that lists specific sites

and a submit button that says "Select Report"

what I want to do when the form is submitted is build the url to the specific site selected by the user and then open the new window as it does above when I have a static report I'm linking too.

I can hardcode the file names in the external DB that the form uses to do the select box.

Can someone help me write the code that happens on the submit?

Example:

Select list is: School A

School B

School C

and file name is 041_scha.pdf for school A but 04a_schb.pdf for school B and 04a_schoolc.pdf for school C.

so my external db has 3 fields

District 1|School A|041_scha.pdf

District 1|School B|04a_schb.pdf

District 1|School C|04a_schoolc.pdf

Now the trick that I haven't figured out, is writing whats in field 3 into the url as a variable when the user submits the select showing the site name stored in field 2 which is based on the DB they are using (field 1).

Thanks for any help. I hope this is clear!
Lynette
Hollister, Ca

Further explanation:

db format for my select box:

**|SAMPLE SCHOOL DISTRICT|SAMPLE SCHOOL|04a_01.pdf


format for my select box:

<TR><TD ALIGN="Right" VALIGN="TOP"><$font_color>Select Site:</FONT></TD>
<TD COLSPAN="3" VALIGN="TOP">&nbsp;|; print &build_limited_select_field_from_other_db ("csitename",$rec{'csitename'},2,1); print qq|</TD></TR>


What I want subroutine from submit button to do:

select fields 2 & 3 from select box data and display this file in a new window.

what url would look like: (where $rpt_specsite = field 3 above (04a_01.pdf & $rpt_sitename=field 2 above) and $db_pdf_url is defined in the .cfg file and is the client specific subdirectory where the .pdf files live.

<a href="$db_pdf_url/$rpt_specsite" target="_blank" name="$rpt_sitename" title="$rpt_sitename Report></A>;


Translates to:

<a href="$db_pdf_url/04a_01.pdf" target="_blank" name="SAMPLE SCHOOL" title="SAMPLE SCHOOL Report></A>;


I need help making my subroutine do this! Thanks!
Lynette
Hollister, Ca

I think there are 2 parts to this problem;

1. Search on 2 fields and build a URL, which should not be too hard.

2. Rather than just providing the URL you seem to say you want to OPEN the file based on the URL. True this would save the user from clicking on the link.

"Quick and dirty" way; you could put the URL into a meta tag in the header with a refresh set to zero. Problems would include; if for some reason there were more than one match on the search, you would need to code it to only write the meta tag if hits ==1, else provide the list of links. Another problem has to do with the back button, but not a problem since you are opening in a new window.

Are you using frames or are you using Javascript to open the new window?

Replying to myself again here, maybe my thinking process will help someone else in the future.

I think I can base the building of the url I need on the &build_limited_select_from_other_db by JoeMatt. However, to get to that point, I need figure out how to:

1 - submit with a "get"? my selected sitename from the form so that I can then find the correct file name? -- This is just like running a query I guess only I'm not running on the database, I'm running it on an external db file. So I want to save the selected site to a variable and match it in the sub routine to build the url ---

So what is the value of the selected site?

I need a <form action="something" method="GET" name="form1">

and a <input type=hidden name="theselectedsite" value="????">

?? what do I put for form action?

?? what do I put for value from the select box????? for it to be the selected item????

Thanks for any and all help!
Lynette
Hollister, Ca

I'm just using target=_blank to open the new window. AND, it doesn't HAVE to go directly there, I can go to a new form with the url on it that they click on, like the export to excel mod I have implemented does.

I don't have a problem with that extra step.

For me right now I'm hung up on getting the selected site into a variable for the new subroutine to use.

I think modifying your build_limited_select_from_other_db will get me a url as output, if I understand it all.

I have made some progress, maybe on that? at least in defining the variables from the DB, what I have so far is attached (definately not complete and not working!), if you want to look at it.

Thanks for your help!
Lynette
Hollister, Ca

The "form action=" and "name =" are separate form tags.


<form action="$db_script_url" method="GET">

and

<INPUT TYPE="SUBMIT" NAME="view_records" VALUE="View Records">

The important part is the name= in the sumbit buttom as this is how dbman knows which sub to run.

Once dbman is running the sub, the sub tells it where to find the other database and what to do with it.

Then you just need to pass along the critera to the sub as hidden fields or via select boxes, and the sub should return the output.

Now to write the sub.

OK, Joe, I got that, but the questions is do a need a

<input type=hidden name="selectedsite" value="$csitename">

to pass the chosen site from the select box to the subroutine?

Thanks as always!

Lynette


PS - I put the code as I currently have it (from db.cgi and html.pl) in a text file that is attached. The build_url sub is NOT working yet. and I think I need to add one more form to the html.pl to show the url after the specific site is selected.... continuing to work on it here.... I think the last thing is to get the code working, test and debug!

Now to figure the rest of this out!

I am following the text file somewhat, so I hope I can help.

I see this line

&build_url_from_other_db(@hits);

in sub rpt_site_sroom

and so I ask you do you know at this point what @hits contains? @hits is being passed to &build_url_from_other_db
and being read by this line
my ($sitename, $filename, $value, $fnum_1, $fnum_2, $limit) = @_;

while normally (sub html_record_form) it is read by this
my (%rec) = @_;

So I think you build_url sub needs to be quite different.

BUT, I "think" all it needs to do is check the other database against the @hits and find a match.

I hope that helps, but please recall I could be really wrong.

Joe, the whole point of using rpt_site_sroom (which is cloned from other subs that use query to determine if a search was successful) was to make sure a file name was found before building the url and actually, I'd rather it did that PLUS make sure the file exists in the directory too --- but that's another story.

so I need to change the my($status,@hits) = &query("view")

to something like my($status,@hits)= &build_url_from_db("something")

so &build_url_from_db needs to have "something" to output so rpt_site_sroom knows the status eq "ok"

then it can go to html_rpt_success

and if it's not "ok" it would take the user to html_rpt_failure

Then the html_rpt_success sub actually builds the url from 2 variables defined in build_url_from_db

$pdffilename - the name of the file in field 3 of the other db that matches $District and $csitename

$sitename - is the name of the site (= $csitename)

I know the guts of build_url_from_db has to change. You seem to be saying the code I borrowed from your build_limited_select_from_other_db is not anything like what I need.

I was thinking that by adding a field number for the file name and one other field (matching on 2 fields instead of just one and selecting information from the 3rd field) that this code could be expanded to do what I need. Maybe I'm way off base??????

Anyway, I'm totally confused for the day! So I think I'll stop and start fresh on it tomorrow!

Thanks for you help!

Lynette

PS -- latest text file attached

This is the map of the process as I see it;

1. A form, which when submitted, includes values for district, school, and build_URL = "build" #OK so far.

2. This will automatically get parsed and values assigned to %in #Still OK so far.

3. %in automatically gets passed to sub main which finds $in{build_URL} = "build" and passes %in to sub build_URL. #still OK.

4. sub build_URL does some checking to see that values for $in{district} and $in{school} are not null, then loads the external database and looks for a match. #here as long as we know we are dealing with %in I think we can find a match.

5. If match is found, set status = ok, and assign the found URL to a variable, then the sub that checks if staus is ok, and finally the sub that "prints" the link based on the %in and the URL stored as a variable.

Just forget about @hits as we will not run the %in through the querry. We can just use build_url (which is really find_url) to check;

if we match district then check if we match school then ...

BTW, good morning.

That looks right, now to write the actual code!
Lynette
Hollister, Ca

This is the way I see the find_url sub.

not tested, I am not an expert by any means,

I hope you know what to do with the variables after this sub finds the url.

let us know how it works out.

Edit: almost forgot, you need to specify the next sub (the one that returns the link) else you could incorporate it at the end of this little hack.

Joe,

after looking at your code snippit, I have the following questions:

1 - where does $in{district} and $in{school} come from?

currently, I have $District in the .cfg file which gives the name of the district(for build_limited_select_from_other_db) which the other_db that I'm using for both building the select lists and supplying the file name.

and the select box carries the school name in the submit to the build_url_from_db sub.

now I think you're saying that I use the code you attached instead of some hack of the build_limited_select_from_other_db code and then just add either going to the sub that creates the link to the url or the code that creates the link.

Am I on track here? I'm going to try it and see what happens!

Thanks as always
Lynette
Hollister, Ca

Joe, do you know what causes the Unknown action error?

when I submit the selected site, I get the Error - unknown action form and the following in the address:

http://server.com/...tename=SAMPLE+SCHOOL

&build_url_from_db=Select+Report

I'm not sure how you get the $in{district} and $in{school} from this?????????

also I don't know what action it can't find??? do I need to define &build_url_from_db in db.cgi in the elsif statements???????

I'm getting confused again!

Thanks for your help though
Lynette
Hollister, Ca

they come from the form that is submitted; so you would need a hidden field for $district or $District ( I trust you will make the variable names conform ).

and so where I have $school you would need to change it to $csitename


Yes toss out the old hack and use the new.

I am still pondering how to "return" the error message, since there is nowhere to return to ( I guess it would return to the main sub that sent it there ) that's why I commented it out.

Sorry if all was not clear,

OK,

I'm still getting an Error -- unknown action

when I revised the form and select the site then submit the form, I now get the following address:

http://server.com/...b4/db.cgi?db=demorpt

&uid=demo.105294690991698

&district=SAMPLE+SCHOOL+DISTRICT

&csitename=SAMPLE+SCHOOL

&build_url_from_db=Select+Report

(separated out the & for ease of reading here - it's all one line in the address)

do we need to account for the &uid? or the "Select+Report" ??? I'm not sure what is wrong that

it's giving me the unknown action form -- any idea how can I trace through that???

Lynette

yes in the sub main ( sorry I thought you were aware of that part. )

Using my code the link would look like;
http://server.com/cgi-local/db4/db.cgi?db=demorpt&uid=demo.105294515261627&find_url=1&district=somename&school=somename

So just to be clear
&district=somename

becomes

$in{district} with a value of "somename"

same for school=somename

the sub parse at the end of db.cgi changes them into $in{district} etc.

I am assuming that unknown action is a sub that is not the right name I used find_url_from_db, did you change that?

Regarding userid, db.cgi should check userid and user rights as it would with any other submit, so I say no.

Regarding build_url_from_db=Select+Report; you can make that as complex as you want, and may need to use different things to do more than one thing later, but for now I think =1 is best.

It's quitting time here and I've to go pull weeds in the garden, but i'll check back in the morning.

OK! My Duhhhh.... Had forgotten/not processed that I needed to add:

elsif ($in{'build_url_from_db'}) { if ($per_reports) { &build_url_from_db; } else { &html_unauth; } }

in the elsif section of db.cgi's sub Main.

Now, I get the new form, it's just the creating the url that needs to happen. It's opening the right directory.

Thanks so much for your help Matt! I'm not sure if the code is working correctly yet, just starting on that part!
Lynette
Hollister, Ca

I think you were just moving too fast.

I'll trust you on the rest.

I know that I need to create the output (build the link to the file) and return it. I'm not sure how to do that though.

There should only ever be 1 match. so do I need a foreach statement?

what I really want is at the found record - what is the $filename and also what is the $csitename?

then just plug those into the generic link statement in the page like I do the generic path.

Is that possible? or am I totally on the wrong track and have no clue what I'm doing?



Thanks
Lynette
Hollister, Ca

I think you are on the right path.

Yes there is only one match (if there is more than one, match only the last match is stroed in the variable $filename), so no foreach statement would be needed.

To build the link, use the variables $in{csitename} and $filename along with the path variables, I thought you had most of that done in a previous post, quoted below. The variable names are different in the quote below, and of course you will need to add the header and footer sub.

The way I see it, the output would be a new sub, then I think you just need to call it at the end of the find_url. The new sub I would call build_url. Of course you get to decide how to name your sub(s).


Unless I'm missing something you are about there.

OK, this is so close and yet so far!!!

attached is the code for the current build_url_from_db sub (no I haven't done any renaming yet) which includes the successful submission sub (to be separated out) AND I print each variable for testing (see notfound.jpg which shows the result)

I'm getting the last record in the other db and the $found_url variable is blank as is the $filename variable.

so the loop is looping and not stopping??? and I don't understand why I'm not getting 04a_008.pdf as the filename!

Just found out nothing is going in for the district variable from the .cfg file! sheesh!

If I can fix that I think I have this licked!

Lynette

As you can tell, I'm a happy camper! IT WORKS! Thank you so much Joe. I will write this up and post the truly working final version here.

Lynette

Very COOL!

I like the way use do your comments, so if possible please write up both parts? PLEASE!

That is the build_limited_select_from_other_db and this thing we just did.

It's starting to get busy around here, since the rain stopped.