INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Coding and Syntax

Writing Code in Runtime? by baltman
Posted: 26 Mar 02

You can create and run code ('prg' files) in run time based on existing data or user input by building the code programmatically within one or more text fields in a table and then saving it as a prg file.

For example:

Task: Sum or Average two numerical fields in a table for each record depending on user input into a form.

Table:

ID  Balance   Payment       Bill     BalFlag    PayFlag    BillFlag
01  $100.00   $ 10.10      $89.90
02  $ 50.00    $  7.50       $42.50
03  $   3.28    $  0.01       $ 3.27
 
User input via form selects to create a summary of Balance and Bill using Check Boxes. Thus our table now looks like this:

ID  Balance   Payment       Bill     BalFlag    PayFlag    BillFlag
01  $100.00   $ 10.10      $89.90       1                              1
02  $ 50.00    $  7.50       $42.50
03  $   3.28    $  0.01       $  3.27       1                             1


We can now write our runtime code:
************************************************************
alter table tablename add column programtxt c (200)

replace programtxt with "select distinct id,sum(balance);
  as BalSum, avg(balance) as BalAvg, sum(bill) as BillSum,;
  avg(bill) as BillAvg group by 1 from paytable into;
  table temp"+alltrim(id) for balflag=1 and billflag=1

replace programtxt with "select distinct id,sum(balance);
  as BalSum, avg(balance) as BalAvg, sum(payment) as;
  PmtSum, avg(payment) as PmtAvg group by 1 from paytable;
  into table temp"+alltrim(id) for balflag=1 and pmtflag=1


*****
*note that the SQL statement must fit within the field
*unless you want to populate into two or more fields and
*concatenate them in the last step using the command:
*copy fields programtxt1,programtxt2 to runtimecode.prg type sdf.
*****

copy field programtxt to runtimecode.prg type sdf for;
programtxt#"   "

alter table tablename drop column programtxt

compile runtimecode

do runtimecode

************************************************************
You can also write multiple line code to manipulate or report on each record using the same method but additionally copying each program line 'column' to a temporary table, assigning a line order (e.g. "1,4,7" for a "use table" command, a "2,5,8" for a "count(*)" and a "3,6,9" for a "close table"), appending all of the temporary tables together and then sorting them before creating, compiling and running the prg.

I want to apologize for not following any particular programming conventions. As a business user who writes programs (i.e. not a programmer), my focus is to get things done, not to do them prettily. The basic premise in this FAQ can be used for some pretty useful stuff. For example, I have used it to create self-formatting Excel output with multiple columns, over thirty different user defined formats that can be assigned on a cell level, user defined headings and data sources and multiple summary operations.

I hope that this will make someone's life a little easier :)

Back to Microsoft: Visual FoxPro FAQ Index
Back to Microsoft: Visual FoxPro Forum

My Archive

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close