Help with automating saved queries...

[MoGrace]

Re: Help with automating saved queries...

Why not put those 45 filter definitions into a table. Embed your browse in a form with your calc total fields and use a lookup to apply the filter you want. Then you can print the data or copy the result to another table, or export to Excel, using a button.

[KeithW]

Re: Help with automating saved queries...

Tom, Stan, Al;

To all that read and made suggestions, thanx !!! In the spirit of showing what I learned the final solution and an example of the output is below.

What I learned is:...
1 You can get a list of Saved Operations (Queries) for a table.
2 You can extract the FILTER parameter from those queries.
3 You can capture the sums easily by using TABLESUM().
4 This allows for easy self maintenancing by simply adding/removing Saved Operation Queries and upon the next script run it will used whatever exists which is very much what I wanted... too not be modifying this script going forward just because new items were added to the list of things to process or deleted for that matter.
5 This demonstrates Add A New Table, with fields and Adding to the database.
6 Capturing results from the TABLESUM() and updating the newly created table.
7 Warning upon an Overwrite situation.
8 Use of the PleaseWait() function to keep user informed of script progress.

Solution Code:
'Date Created: 04-Nov-2015 10:18:16 PM
'Last Updated: 05-Nov-2015 11:02:36 PM
'Created By : KeithW
'Updated By : KeithW
dim start As C
dim finish as C
dim cnt as N
dim item as C
dim list as C
dim filter as C
dim tbl as P
dim query_balance as N

' Generate a list of all Saved Operations Queries for this table...
list = a5_get_operations("query", "summ_by_customer")
msgbox("List Of Operations",list) ' show list to user...

'In case the destination table name is stored as a relative file name, or uses an alias
'in the drive/path specification, use the filename_decode() function to convert the
'filename to an absolute filename.
destination_table = filename_decode("[PathAlias.ADB_Path]\Specific_Obligors.dbf")

if file.exists(table.filename_get(destination_table)) then
overwrite_result = ui_msg_box("Warning","'"+destination_table+"' already exists." + crlf(2) + "Would you like to overwrite it?",UI_INFORMATION_SYMBOL+UI_YES_NO)
if overwrite_result <> UI_YES_SELECTED then
END
end if
end if

' Create new table for Query Results...
fields = <<%a%
Key_Code,C,32,0
Amount,N,15,2
%a%
create_table("[PathAlias.ADB_Path]\Specific_Obligors.dbf", fields)

' Add the destination table to the database....
file_add_to_db(table.filename_get(destination_table))

' Open destination table for processing...
tbl = table.open(destination_table)

' Begin processing Obligors...
start = Time("0h:0m:0s")

for each item in list ' only process Queries starting with a "z"
if left(item.value,1) = "z" then

' Get FILTER parameter from existing Saved Operation (Query)...
filter = query_filter_get(item)

' Display PleaseWait dialog to inform user of processing status...
PleaseWait(.T., item + " ... ")

' Apply FILTER to table and sum the Total_Balance field...
query_balance = 0
query_balance = tablesum("summ_by_customer",filter,"total_balance")

' Enter mode to add new record to result table...
tbl.enter_begin(.T.)
tbl.Key_Code = item
tbl.Amount = query_balance
tbl.enter_end(.T.)

' Take down PleaseWait dialog box...
PleaseWait(.F.)

' Keep track of the quantity of FILTERED/TABLESUM()s are run...
cnt = cnt +1
End If
next
finish = Time("0h:0m:0s")
msgbox("Query Processing","Completed !!" +crlf()+str(cnt,2,0)+" items"+crlf()+start+" - "+finish)

END

'- - - - - - - - - - - - - - - - - - - - - - - -
FUNCTION PleaseWait AS C (Show = .F.,what = "" )
'DESCRIPTION: This function displays a 'Please Wait' dialog for use while processing.
'What is an optional parameter that will be displayed along with the words "Please Wait"
PleaseWait = ""
if show = .F.
if ui_modeless_dlg_exist("Processing")
ui_modeless_dlg_close("Processing")
end if
exit function
end if
text1 = what+" Please Wait"
len = len(text1)*2
textbox="{text="+alltrim(str(len))+"text1}"
box_code = <<%dlg%
{background=Pale Yellow}
{font=arial,14,bi}
{include=textbox}
%dlg%
box_event = <<%code%
1=1
'sleep(5)
%code%
ui_modeless_dlg_box("Processing",box_code,box_event)
ui_modeless_dlg_setfocus("Processing")
ui_modeless_dlg_refresh("Processing")
END FUNCTION
' - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -


First 5 Lines of Output Generated in Specified_Obligors file...
Key_Code Amount
z01_Fort Bragg 95,933.91
z02_Invista 910,248.67
z03_Wake County Board 1,524,354.34
z04_NC State Univ 197,530.93
z05_City Of Raleigh Park 429,200.77
...

This file can then be exported to Excel which is where I need the data.
I realize I can write directly to an Excel file and that is an exercise for another day.

This shaved an hour of sitting and doing each step manually to 7 minutes with no intervention !
Not a huge savings, but nice to not to have to be a slave to the original process no more.

Regards,
Keith

[Stan Mathews]

Re: Help with automating saved queries...

Never tried that and don't have Alpha available at the moment to check.

[KeithW]

Re: Help with automating saved queries...

Stan,

Do you know if you can programmatically get the code for the filter from a Saved Operation?
Could I dynamically generate the filters at run time, in other words?
I need to maintain the manual method of getting the results, too...

Keith

[Stan Mathews]

Re: Help with automating saved queries...

In Alpha, dbf functionality, browses and forms are not results to be captured. They are simply means to view records.

[KeithW]

Re: Help with automating saved queries...

Didn't say you could. Right click on saved query name in control panel. View xbasic. Copy the filter. Use that in a tablesum().

OK, one of my hopes was it would be self-maintenancing by simply adding new Saved Operations (Queries) going forward, oh well !!!
With all the functionality of A5, just cannot believe you cannot capture the Browse Result and process it.....

Thanx,
Keith

[Stan Mathews]

Re: Help with automating saved queries...

Didn't say you could. Right click on saved query name in control panel. View xbasic. Copy the filter. Use that in a tablesum().

[KeithW]

Re: Help with automating saved queries...

Tablesum() with the filter expression the same as used in your saved queries would yield sums on the same set of records displayed in the browse after such queries are run.

Stan, how do you specify the name of the Saved Operation Query in the TABLESUM() command ?

Keith

[Stan Mathews]

Re: Help with automating saved queries...

Tablesum() (or something similar) would be great to process those browse results if I could figure out how to
address them.... ????

Tablesum() with the filter expression the same as used in your saved queries would yield sums on the same set of records displayed in the browse after such queries are run.

[Al Buchholz]

Re: Help with automating saved queries...

Some additional sense.

Still sounds like summary operation(s) is/are needed.

You may want to contact IADN to get some detailed individual help.

[KeithW]

Re: Help with automating saved queries...

Hi Al,

I have a 3 million record table with a company month-end A/R summary.
I have a series of Saved Query Operations, for specific company groups, query all of the related entities for that customer group.
The query produces a browse with the matching records, which I want to total some data and write that to another table.
Many of the saved queries have 3 or 4 panels of criteria (includes & excludes) in order to get the subset I want.
I want to be able to sum up the open balance of the returned records and then go to the next query (about 45 in total for this master table)
I ran these by hand for a few months to be sure that they capture all that I desire without any out-liers and am now comfortable with the results.

I used Action Scripting to put the first Saved Operation Query into operation, QUERY.RUN(), then used: a5_get_operations("query", "summ_by_customer") to get a
list of all the queries. In this case all of these special queries start with a "z", so I only run those queries. Each one produces a Browse Result that I am trying to
figure out how to manipulate programmatically. Tablesum() (or something similar) would be great to process those browse results if I could figure out how to
address them.... ???? Make anymore sense now ?

Keith

Have you looked at a summary operation?

Or looked at what it would take to have a relationship table?

With what you've presented, it's difficult to understand the full scope of what you need to do.

[Al Buchholz]

Re: Help with automating saved queries...

Have you looked at a summary operation?

Or looked at what it would take to have a relationship table?

With what you've presented, it's difficult to understand the full scope of what you need to do.

[KeithW]

Re: Help with automating saved queries...

Tom,

No, what do you mean by a "mapped" table?

Is there no way to take a Saved Query (operation) and process its result?

Keith

[Tom Cone Jr]

Re: Help with automating saved queries...

Keith,

Have you considered using a tablesum() calc with a mapped table?

[KeithW]

Re: Help with automating saved queries...

Tom,

The table is a Customer A/R Table but unfortunately the Customer File does not have any kind of Parent/Child or Related Entity fields and these Saved Queries are just that. In a simple case think (WalMart/Sam's Club) and in a more complex situations think (Us Army or Air Force Installations) or (General Electric entities that may include things like Turbines, Appliances, Medical, Financial, etc). There is no way without developing a huge application to manage these to avoid Saved Querries.

Manually running them produces the browses to be totaled for the entities exposure to the company. I do not care about the browses per se, just need to total up the items returned from the query. Tablesum() does not provide enough functionality to do this.

Keith