multi-field sort, export records

[Steve Wood]

Having now shared our WAS Tracking System with a few customers they have some requests. I think I have the general direction, but wanted to check out my solutions.

1) Sort on multiple grid fields. Soln: add dialog with a few dropdown fields showing all possible sort fields. Add a Ascending/Decending field next to each. Use the info in the dialog with a xbasic sort and repaint function.

2) Export to Excel. Soln: add AdvancedButton launching a function, set the current filter (set by a session variable) and exports those records to the local C drive using file.create().

[Lenny Forziati]

RE: multi-field sort, export records

For #2, using file.create to save to the C drive will save a copy of your export on the server's C drive, probably not of much use to the web user. Take a look at the Web_Applications_Demo app where it explains a technique for generating PDF reports in the web browser - you should use the same method to save your export and serve it to the user. It is about 4 lines of Xbaisc that you'll need.

-Lenny

[Steve Wood]

RE: multi-field sort, export records

Back to the web export. I see how I can create a Report and use report.saveas() to present it to the web user; but I don't see how I can create an export (which is an Operation) and do the same. I don't see a procedure to deliver or present the results of an Operation to the web user. Report.saveas() cannot call up an export.

I tried Export.run_silent()and it will not run in a web environment - it hangs both the browser and Alpha and eventually I have to use task manager to close both.

[Selwyn Rabins]

RE: multi-field sort, export records

export.run_silent() currently is not 'thread safe' which means that it cannot be run in a background thread. so this is why it hangs the web server. (since the web server runs each request as a separate thread).

however, you should still be able to do an export.
instead of doing export.run_silent(), just run the xbasic for the export directly.

if you right click on the export operation in the control panel, and select 'show xbasic', you can easily see the appropriate xbasic.

[Steve Wood]

WAS exporting records

Selwyn;

Thanks, I was able to properly export the file (named export.csv) to the session.folder.

1) However, when I use response.direct to open the file, it opens as plain text (rather than in Excel). Its not the fault of the browser since I can take that same export.csv, move it to my corporate host, and it opens in Excel peoperly.

2) I also tried to use [http_download_bg(filename,"c:\tracking.csv")] to transfer that file from the server to the client. Unless I did it wrong, this function does not seem to copy the file anywhere (server or client).

Here's the AfterValidate code:

dim filename as c
dim exportfile as c
select
case DropDownBox1 = "Export Tracking"
exportfile = "tempexport.csv"
filename = session.session_folder + exportfile
dim a_tbl as p
a_tbl = table.open(""ADB_Path"\tracking")
query.order = "Job_No"
query.filter = "Customer=\"Secure\""
query.options = "I"
a_tbl.query_create()
export.type = 0
export.names = .T.
export.file = filename
export.options = ""
export.field_sep = ","
export.record_sep = ""CR""LF""
export.fields = 4
export.field1 = "job_no"
export.field2 = "lastname"
export.field3 = "client"
export.field4 = "spouse"
a_tbl.export()
' more case statements...
end select

if file.exists(filename)
http_download_bg(filename,"c:\tracking.csv")
response.redirect(session.session_url + exportfile)
end if

[Lenny Forziati]

RE: WAS exporting records

1) Your browser uses the MIME type reported by the server in order to determine in what application to open the file being transferred. There is no official MIME type for CSV files so the Web Application Server returns the default text/plain and your browser displays it.

As of the next patch, the Web Application Server will return the MIME type "application/vnd.ms-excel" for CSV files. This still may not workin all browsers since it is not a standard. If it does nt, your users will need to configure their browser to handle CSV files properly. You can refer to http://www.lbl.gov/ITSD/CIS/faqs/SOFTWARE_Desktop_Applications/10.html for basic instructions on how to do this.

2) http_download_bg() would run on a client and download a file from a specific URL, not a filename. Your use of it is incorrect here. An example would be something like
    

[Steve Wood]

RE: WAS exporting records

Thanks - then currently there is no way for me to produce an export and then download it to a web-only client?

[Lenny Forziati]

RE: WAS exporting records

If you cannot wait for the next patch, you can configure your browser by following the instructions at the URL I included above.

-Lenny

[Steve Wood]

RE: WAS exporting records

Sorry, your url did not lead to any solution. My browser/file types already properly handle csv files (open in Excel). The url provides info on Netscape only, I'm on IE.

[Lenny Forziati]

RE: WAS exporting records

Attached is a file, csv.a5w, that will let you override the MIME type. To use this, change your response.redirect in your event handler to go to this page and pass the file to serve as csv_file, for example

response.redirect("/csv.a5w?csv_file=c:\a5webroot\session_folders\1234567890\export.csv")

-Lenny

[Steve Wood]

RE: WAS exporting records

Lenny, not quite; and my results might be useful for your next patch...

The file opened in the browser as a result of this fix (csv.a5w) does not produce a file that *properly* opens in excel. It opens the files as one single chunk, all in cell A1. This same file, where it sits in session.folder, does open properly if dbl-clicked. So, neither file or browser are at fault.

You can try yourself at my site. Go to http://support.attorneystrust.com and use alpha/alpha to log in. Change the page from index.a5w to index_test.a5w and click the submit button. The results go into cell A1.

----

My AfterValidate code is: response.redirect("/csv.a5w?csv_file=" + session.session_folder + exportfile ) yielding the following on the browser address line:

http://atdsserver/csv.a5w?csv_file=E:\A5Webroot\session_folders\cc6dc981bc4c412e91154dafdfa4dc97\tempexport.csv

[Lenny Forziati]

RE: WAS exporting records

Steve, it looks like you created your A5W page in the HTML Editor. The result is that your A5W page is a complete and valid HTML page. Typically this is correct, but it is not what you want in this case. This causes the resulting CSV file sent to the browser to contain HTML tags that you do not need. Use the A5W page exactly as I uploaded it and you should be fine.

-Lenny

[Steve Wood]

RE: WAS exporting records

Thank you, that worked. (How do you put up with us?).

I had not noticed before that in the WAS control panel I can right-click on a file and select open-with. I had to first create csv.a5w in the HTML Editor, then open-with notepad and strip out html code automatically placed there by the editor. (I can't 'download' your example from the forum, it opens in my browser when I click on it.)

I like the total solution, I can use this same method to select any report/operation via my dropdownbox and the select case statement in the AfterValidate section.

Steve