Using Xbasic to create a report in RuntimePlus

[crimmelcp]

To All:
I am Using Xbasic to create a report in RuntimePlus
It works fine.

Now I need to save the report in a user directory.
I want all of the user reports to be in a directory called user_runtime
This will keep all of the user created reports separate from all of the built in program reports.

How can I save a report created in RuntimePlus to a different directory?

Thanks
Charlie Crimmel
Xbasic code to create a report below:

'a5_new_report_dialog([C type [,C prompt [,C title [,C table_set_list [,C alias_names [,L exclude_tables ]]]]]])
'that corrects an issue with the example given in the Runtime+ PDF Help file.
'The example in the runtime+ help file did not show how to properly initialize
'the table_set_list parameter in order for the logical .f. flag to exclude_tables.
'The table_set_list needs to be populated with the same tables and sets list as the alias_list.

Dim alias_file as c
Dim alias_list as c
Dim table_set_list as c
Dim Prompt as c
Dim dlg_title as c
alias_file = a5.get_exe_path() + chr(92) + "Allowed_Tables.dbf"
If file.exists(alias_file) then
alias_list = get_from_file(alias_file)
table_set_list = get_from_file(alias_file)
Else
alias_list = ""
table_set_list = ""
End if
Prompt = "Select the data source for your new report�"
Dlg_title = "New EZ Task Master Report"
a5_new_report_dialog("report",prompt,dlg_title,table_set_list,alias_list,.f.)

[Scott Emerick]

Re: Using Xbasic to create a report in RuntimePlus

Charlie,

Reports are stored in the .set and .ddd files. Unless your data source table is stored in a different directory you are stuck with what you have.

An option would be to export the report code from the data dictionary and store it as a file to be able to import it back in at a later time.

Jim Chapman had given an example of exporting and importing layouts a while back.

Scott

[Raymond Lyons]

Re: Using Xbasic to create a report in RuntimePlus

Charlie,

Another alternative that I have used is to set things up so that runtime plus users can only create and edit reports whose names begin with a "Z". This at least keeps these user created layouts somewhat separate in the report/letters tabs in the control panel. By using a very generic template on the only set they would ever write a report from, I have it setup so that they must pre-save their layouts with the "Z" (downside is that these users cannot use the report genie because pre-saving is not possible). For a few users I allow them to use the report genie and warn them that they must save their layouts with the Z. In any case they can only later edit them if the layout name begins with a Z. With the pre-saved template layouts they also could get around the Z restriction by doing a Save As to something that does not begin with a Z, but if they do they know that they will never see that layout again (and won't be able to run it). One potential problem with this is that it does not 100% protect your "built in" layouts from being overwritten. Right now I cover this with always having backups. I was going to prevent this too with a custom menu Save As item but cannot get this to work in the runtime plus (it works with the full version).

And yes, I know Alpha has a way to deal with protecting selected layouts. But it too relies on a custom Save As menu item for the runtime and this simple does not work. Moreover, it does nothing to separate user created layouts in the control panel and it is also a real pain to set up and maintain (in my opinion). In my case there are so many layouts that I have to have some way of identifying and separating the the runtime plus layouts from what you call the built in layouts, the latter of which need to be protected from being messed up or overwritten. If anyone has a better approach, I'd love to hear it.

Raymond Lyons

[Bill Parker]

Re: Using Xbasic to create a report in RuntimePlus

My approach is simplified by users only needing to create reports on one set. So I duplicated the "system" version of the set as systemsetname_usercopy.set and seeded it with the "system" reports. Now the users have their own sandbox that does not affect, and is not affected by, my development.

Bill.

[Raymond Lyons]

Re: Using Xbasic to create a report in RuntimePlus

Bill,

Probably I just need a bit more elaboration, but this still mixes the reports up with your reports in the control panel, right? Also, even though you could prevent runtime users from editing or overwriting your reports, they could still use a name that was the same as the name of one of your layouts, right? Maybe there is a simple way to deal with this, but I recently had users yell at me because a report no longer worked correctly and it turned out that I had inadvertently created another layout with the same name but based on another table or set, and the print button users had always used picked the wrong layout to print (based on what, I do not know). In any case, thanks for the idea of giving them their own set. I presume that would get around my "Save As" problem.

Raymond Lyons

[Bill Parker]

Re: Using Xbasic to create a report in RuntimePlus

Raymond,

I do a couple things. For editing I add the name of the table to the layoutname, as [email protected]. That handles any possible duplicate names.

The system just presents the list of layout names from the "usercopy" set, so users don't see any duplicate names. Users don't go to control panel. But actually I have dropped the usercopy set from control panel because I don't want any possibility of sending the client an update and overwriting the usercopy set. Having usercopy detatched is another reason that the set name must be added to the layout name for opening in the editor.

I added a function that I use for this to the code archive. The version I am currently using is a bit different in that it handles detached tables better. I have to leave now but will try to get that version uploaded later today.

Bill.

[Raymond Lyons]

Re: Using Xbasic to create a report in RuntimePlus

Bill,

Thanks. I look forward to the updated version of your function. Now my big problem is that your way of dealing with the issue at hand may be sufficient reason for me to re-do my "Z" prefix way of dealing with it. My way works fine except for not being able to get a custom Save As menu item in the application to work when run from the runtime (which I believe has to be a bug, which I have reported). Plus even if the custom menu item were to work, it seems to me it would have to be setup on each workstation in a network, maybe even for each network user logon. If so, I can't live with that. Also, I too have not been able to get rid of the <SQL Data Source> item with my way of doing things, though I can live with that.

Ray

[Bill Parker]

Re: Using Xbasic to create a report in RuntimePlus

I have uploaded a revised version to the code archive.

Bill.

[Raymond Lyons]

Re: Using Xbasic to create a report in RuntimePlus

Bill

It may not be a big deal but I am not a fan of using undocumented functions, mostly because they are probably more subject to change in the future without any backward compatibility worries. So I changed your function so it uses dictionary.external_record_content_get() instead of a5_enumerate_library(). This change still allows for a table or set to be dropped from the database [I sure hope the undocumented one in question was a5_enumerate_library(), else I spent a lot of time for nothing!] . In any case, below is my modification to what you and Mike did. I am not sure it makes any sense to add it to your/Mike's post in the code archive since the only significant (??) difference is not using an undocumented function.

Raymond Lyons
EDIT: One small glitch I just noticed is the way I changed Layouttype means the wording in the xdialog at the end is Greek. Easy enough to fix but it hasn't been done in the code below.

'Date Created: 18-Aug-2006 08:57:38 PM
'Last Updated: 23-Mar-2009 08:00:30 PM
'Created By : mike reed & updated by Bill Parker in 09
'Updated By  : Raymond Lyons

p_layout_design("amort_dummy.ddd,mkt.set","","Letter")    'Just a testing script example using 2 of my files--delete for saved function

FUNCTION p_layout_design AS L (table_set_list = "", default_layoutname = "", layouttype = "", runsilent = .f.)
'Description:Edit selected layout. table_set_list = comma list (must be .ddd or .set), default_layoutname = default object to edit, layouttype (def. = report)can be report, letter, label, runsilent (def. = .f.) to let user choose object.

'if runsilent = .t., then objectname must have a value because user will not see selection dialog.
'table_set_list is a crlf() list of table and set names.  user can pick from existing layouts from these tables.
'tableset names must include the extension .dbf, .ddd, or .set
'default_layoutname - in this case a report name, sets the default value in selection dialog.
'layouttype can be report, letter, label, (only in full A5, not runtime, can also use form, browse).
'if table_set_list is blank, get layouts for all tables/sets
'Return value is .t. if function completes, .f. if it is terminated.
'  Note: there is no way to tell if a layout was actually added or edited, so .t. is returned if script reaches the "edit selected layout" section.

'Sample Usage:
'    p_layout_design()    'let user select any report in the database to edit.
'    p_layout_design("mytable.dbf", "myreport")    'let user edit any report in mytable.dbf, myreport is default selection.
'       mytable.dbf does not need to be attached to the database.  If it is in a different directory, provide the full path and name.
'    p_layout_design("mytable.dbf,myset.set","","letter")    'edit any letter in 1 table and 1 set.
'    p_layout_design("mytable.dbf", "myreport","",.t.)    'immediatly open editor for myreport.  no user selection.
    
'03-06-09 converted to function from Mike's script so table list can be controled.
'03-18-09 enumerate layouts by individual table, don't have to be attached.  so cashreceipt_usercopy can be dropped from database
'  if table is not attached and no path is provided, default is current database path.
'03-23-09 modified by Ray Lyons to not use an undocumented function. Tables and sets still do NOT have to be attached
    'as long as they are in the app folder.

'leave this for now--actually Ray needs it. a5.Get_Master_path() returns null if it is NOT a Shadow, so we want null to go ahead.
dim MasterPath_Check as c
MasterPath_Check = a5.Get_Master_Path()
if MasterPath_Check <> "" then
    ui_msg_box("NOTICE","This may only be done on the Master Database, not your Shadow version.")
    p_layout_design = .f.
    end
end if
dim shared Master_Path as c
Master_Path = a5.Get_Path()+ chr(92)

If containsi(table_set_list,"dbf")=.t.    'should check each lin for .ddd or .set--maybe later
    ui_msg_box("Stop","Your table list includes a .DBF extension. For this purpose you must list the table's dictionary using the .DDD extension. Set must use the .SET extension.",UI_ATTENTION_SYMBOL+UI_OK)
    end
end if
select
    case layouttype=""
            layouttype="rep0"    'would occur if user set value to "" in order to set runsilent parameter.
    case layouttype="report"
            layouttype="rep0"
    case layouttype="letter"
            layouttype="ltr0"
    case layouttype="label"
            layouttype="lbli"
end select

layoutname = default_layoutname

'convert a comma list to crlf()
table_set_list = stritran(table_set_list,",",crlf())
table_set_list = *for_each(line,alltrim(line),table_set_list)

'    ***** get layout names for selected tables ***********************************************
if table_set_list = ""
    report_list = eval("a5." +layouttype +"_enum()")
    ui_msg_box("Please Note","This will show only those layouts that IN this database, not any in tables or sets that are in the app folder but have been dropped.")
else
    temp_list=""
    report_list=""
    for each tablename in table_set_list
        temp_list = dictionary.external_record_content_get(Master_Path+tablename,"name","","type="+s_quote(layouttype))
        report_list = report_list +crlf()+temp_list
    next tablename
end if

originalreport_list = report_list

'remove the path only if name is unique in all layouts (not just selected layouts)
for each line in report_list
    temp_layoutname = word(line,1,"@") +"@"    'adding the @ will make entire tablename match
    if occurs(temp_layoutname, originalreport_list) = 1
        line = word(line,1,"@")
    end if
next line

report_list = sortsubstr(report_list,crlf())
report_list = "(Create a New Layout)" +crlf() +report_list    'this is optional line

'    ***** Display selection dialog ***********************************************************
'??most of above code could also be skipped if runsilent=.t., but it runs quickly and this may be easier to follow.
if runsilent = .f.
    heading_string = "Select the " +layouttype +" you wish to edit"
    footer_string = "Selected " +layouttype
    
    varC_result = ui_dlg_box("Edit Layout",<<%dlg%
{region}
{text=55,1:heading_string};
{endregion};
{region}
Select:| [.70,10layoutname^#report_list];
{endregion};
{region}
{text=55,1:footer_string};
[.80layoutname];
{endregion};
{line=1,0};
{region}
<*15&OK!OK> <15&Cancel!Cancel>
{endregion};
%dlg%)

    if varC_result <> "OK" .or. layoutname = "" then
        p_layout_design = .f.
        end
    end if
end if

'This next line updates the version number, so that the reports will pushed out to the user workstations that are shadowed. see the script below.
'script_play("ver_number_upd")
'ver_number_upd()
'(but the commands below run asynchronously, so no way to tell if anything was done)


'    ***** Edit selected layout **************************************************************
if layoutname = "(Create a New Layout)"
    a5_new_report_dialog(layouttype,"","",table_set_list)    'adds <SQL Data Source>, but could not get exclude_list to remove it.  see Code Archive.
    'also read RunEngineDocumentation.pdf.
else
    'append the path in case table_set is not attached to current DB, e.g. cashreceipt_usercopy.set
    layoutname = filter_string(originalreport_list, layoutname)    'layoutname is unique in original list
    a5_layout_design(layoutname,layouttype)
end if
'above runs in new session, so this function returns a value before layout is edited.
'calling routine should display notice to user if a drop down list must be repopulated in subsequent code with any new layout name.

p_layout_design = .t.

END FUNCTION

[Raymond Lyons]

Re: Using Xbasic to create a report in RuntimePlus

Too late for another edit. There were several issues with what I posted earlier tonight. The following deals with them. Of course there may be other problems as well.

Raymond Lyons

'Date Created: 18-Aug-2006 08:57:38 PM
'Last Updated: 23-Mar-2009 10:13:01 PM
'Created By : mike reed & updated by Bill Parker in 09
'Updated By  : Raymond Lyons

p_layout_design("mkt_users.set,amort_dummy.ddd","","report")    'Just a testing script example using 2 of my files--delete for saved function

FUNCTION p_layout_design AS L (table_set_list = "", default_layoutname = "", layouttype = "", runsilent = .f.)
'Description:Edit selected layout. table_set_list = comma list (must be .ddd or .set), default_layoutname = default object to edit, layouttype (def. = report)can be report, letter, label, runsilent (def. = .f.) to let user choose object.

'if runsilent = .t., then objectname must have a value because user will not see selection dialog.
'table_set_list is a crlf() list of table and set names.  user can pick from existing layouts from these tables.
'tableset names must include the extension .dbf, .ddd, or .set
'default_layoutname - in this case a report name, sets the default value in selection dialog.
'layouttype can be report, letter, label, (only in full A5, not runtime, can also use form, browse).
'if table_set_list is blank, get layouts for all tables/sets
'Return value is .t. if function completes, .f. if it is terminated.
'  Note: there is no way to tell if a layout was actually added or edited, so .t. is returned if script reaches the "edit selected layout" section.

'Sample Usage:
'    p_layout_design()    'let user select any report in the database to edit.
'    p_layout_design("mytable.dbf", "myreport")    'let user edit any report in mytable.dbf, myreport is default selection.
'       mytable.dbf does not need to be attached to the database.  If it is in a different directory, provide the full path and name.
'    p_layout_design("mytable.dbf,myset.set","","letter")    'edit any letter in 1 table and 1 set.
'    p_layout_design("mytable.dbf", "myreport","",.t.)    'immediatly open editor for myreport.  no user selection.
    
'03-06-09 converted to function from Mike's script so table list can be controled.
'03-18-09 enumerate layouts by individual table, don't have to be attached.  so cashreceipt_usercopy can be dropped from database
'  if table is not attached and no path is provided, default is current database path.
'03-23-09 modified by Ray Lyons to not use an undocumented function. Tables and sets still do NOT have to be attached
    'as long as they are in the app folder.

'leave this for now--actually Ray needs it. a5.Get_Master_path() returns null if it is NOT a Shadow, so we want null to go ahead.
dim MasterPath_Check as c
MasterPath_Check = a5.Get_Master_Path()
if MasterPath_Check <> "" then
    ui_msg_box("NOTICE","This may only be done on the Master Database, not your Shadow version.")
    p_layout_design = .f.
    end
end if
dim shared Master_Path as c
Master_Path = a5.Get_Path()+ chr(92)

dim shared layoutlbl as c
If containsi(table_set_list,"dbf")=.t.    'should check each lin for .ddd or .set--maybe later
    ui_msg_box("Stop","Your table list includes a .DBF extension. For this purpose you must list the table's dictionary using the .DDD extension. Set must use the .SET extension.",UI_ATTENTION_SYMBOL+UI_OK)
    end
end if
select
    case layouttype=""
            layouttype="rep0"
            layoutlbl = "Report"    'would occur if user set value to "" in order to set runsilent parameter.
    case layouttype="report"
            layouttype="rep0"
            layoutlbl = "Report"
    case layouttype="letter"
            layouttype="ltr0"
            layoutlbl = "Letter"
    case layouttype="label"
            layouttype="lbli"
            layoutlbl = "Label"
end select

layoutname = default_layoutname

'convert a comma list to crlf()
table_set_list = stritran(table_set_list,",",crlf())
table_set_list = *for_each(line,alltrim(line),table_set_list)
'ui_msg_box("tablesetlaist",table_set_list)

'    ***** get layout names for selected tables ***********************************************
if table_set_list = ""
    report_list = eval("a5." +layouttype +"_enum()")
    ui_msg_box("Please Note","This will show only those layouts that IN this database, not any in tables or sets that are in the app folder but have been dropped.")
else
    temp_list=""
    report_list=""
    for each tablename in table_set_list
        temp_list = dictionary.external_record_content_get(Master_Path+tablename,"name","","type="+s_quote(layouttype))
        temp_list = *for_each(layout, alltrim(layout) +"@" +tablename, temp_list)
        report_list = report_list +crlf()+temp_list
    next tablename
end if
'ui_msg_box("report_list 0",report_list)

originalreport_list = report_list
'ui_msg_box("Originalreport_list 0",originalreport_list)

'remove the path only if name is unique in all layouts (not just selected layouts)
for each line in report_list
    temp_layoutname = word(line,1,"@") +"@"    'adding the @ will make entire tablename match
    if occurs(temp_layoutname, originalreport_list) = 1
        line = word(line,1,"@")
    end if
next line
'ui_msg_box("report_list 2",report_list)

report_list = sortsubstr(report_list,crlf())
report_list = "(Create a New Layout)" +crlf() +report_list    'this is optional line

'    ***** Display selection dialog ***********************************************************
'??most of above code could also be skipped if runsilent=.t., but it runs quickly and this may be easier to follow.
if runsilent = .f.
    heading_string = "Select the " +layoutlbl +" you wish to edit"
    footer_string = "Selected " +layoutlbl
    
    varC_result = ui_dlg_box("Edit Layout",<<%dlg%
{region}
{text=55,1:heading_string};
{endregion};
{region}
Select:| [.70,10layoutname^#report_list];
{endregion};
{region}
{text=55,1:footer_string};
[.80layoutname];
{endregion};
{line=1,0};
{region}
<*15&OK!OK> <15&Cancel!Cancel>
{endregion};
%dlg%)

    if varC_result <> "OK" .or. layoutname = "" then
        p_layout_design = .f.
        end
    end if
end if

'This next line updates the version number, so that the reports will pushed out to the user workstations that are shadowed. see the script below.
'script_play("ver_number_upd")
'ver_number_upd()
'(but the commands below run asynchronously, so no way to tell if anything was done)


'    ***** Edit selected layout **************************************************************
if layoutname = "(Create a New Layout)"
    a5_new_report_dialog(layoutlbl,"","",table_set_list)    'adds <SQL Data Source>, but could not get exclude_list to remove it.  see Code Archive.
    'also read RunEngineDocumentation.pdf.
else
    'append the path in case table_set is not attached to current DB, e.g. cashreceipt_usercopy.set
    layoutname = filter_string(originalreport_list, layoutname)    'layoutname is unique in original list
    'ui_msg_box("layoutname",layoutname)
    a5_layout_design(alltrim(layoutname),layoutlbl)
end if
'above runs in new session, so this function returns a value before layout is edited.
'calling routine should display notice to user if a drop down list must be repopulated in subsequent code with any new layout name.

p_layout_design = .t.

END FUNCTION

[Raymond Lyons]

Re: Using Xbasic to create a report in RuntimePlus

Bill,

With both your latest version of p_layout_design() and mine, if the set/table on which a report is based has been dropped from the database (but still in the same folder), I get this

when run from a button on a form on the first try, but if I try again it works without error. If the set/table is in the database, there is no error on the first try.

How do you we get around this? I have compacted the database and even tried after rebooting and I am using the latest build (2095-3264). Also, if I run the exact same code [p_layout_design("mkt_users.set")] using an unattached set in a script from Code tab of the Control Panel, it works without error on the first try--but not when run from a button on a form.

Thanks.

Raymond Lyons

[Raymond Lyons]

Re: Using Xbasic to create a report in RuntimePlus

Bill,

With both your latest version of p_layout_design() and mine, if the set/table on which a report is based has been dropped from the database (but still in the same folder), I get this
[ATTACH]20520[/ATTACH]
when run from a button on a form on the first try, but if I try again it works without error. If the set/table is in the database, there is no error on the first try.

FYI, this is a problem only when using V9. I turned it in as a bug today. Two hours later Selwyn said it is fixed and presumably the fix will be in the next patch.

Raymond Lyons

[Raymond Lyons]

Re: Using Xbasic to create a report in RuntimePlus

Bill (& others),

I asked and Selwyn just informed me that in the next patch a5_new_report_dialog() will have a new flag allowing us to exclude the <SQL Data Source> item.

a5_new_report_dialog as p (type = "", prompt = "", title = "", table_set_list = "", alias_names = "", exclude_tables = .f., flagShowSQLDataSource = .t.)

Raymond Lyons