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
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
Comment