MS FoxPro (DOS ver.2.6) Request how to insert column!!!

[3dob]

Hello Everyone,

This is to request everyones' kind assistance to help me on how to insert one column in the programming code shown below to insert [FBAL] from table FEB11CUS.

Everyones' assistance is highly appreciated.

Thanks!
3dob


MAR11CUS:
FCUSTNO FFIRSTNAME FCOMPANY FROUTENO FREADDATE
127510 TRIPLE J BARRACK 10000 000002 2/28/2011

FREADING FMULTIPL FCLASS FBAL FCURPMT FCURAMT
81064 1 WMR 53163.39 0 936.53

***************************************************************
FEB11CUS:
FCUSTNO FFIRSTNAME FCOMPANY FROUTENO FREADDATE
127510 TRIPLE J BARRACK 10000 000002 1/31/2011

FREADING FMULTIPL FCLASS FBAL FCURPMT FCURAMT
78448 1 WMR 52226.86 0 840.78

**************************************************************

PROGRAMMING CODES:

Clear
SET PRINT ON
*? chr(15)
SET PRINT OFF
SET STATUS OFF
SET TALK OFF
SET ESCAPE OFF
SET EXACT ON
SET CONFIRM ON
SET BELL OFF

USE &LCUSFILE INDEX &LCUSRTNDX
store 'ROUTE NUMBER' TO LNDX
LN=61
STORE 0 TO LPG
STORE SPACE(60) TO LHEADING
STORE .F. TO LGOOD
STORE FROUTENO TO LROUTENO
DO WHILE .NOT. LGOOD
Clear
store '00000 000000' to LEND_RT
@ 03,01 say 'Input routeno'
@ 03,24 get lrouteno PICT 'XXXXX XXXXXX'
@ 04,01 SAY 'ENDING ROUTE:'
@ 04,24 GET LEND_RT PICT 'XXXXX XXXXXX'
@ 05,01 SAY 'HEADING'
@ 06,10 GET LHEADING
READ
SEEK LROUTENO
SET ESCAPE ON
IF .NOT. EOF()
STORE .T. TO LGOOD
ENDIF
ENDDO
STORE .T. TO LNEXT
SET DEVICE TO PRINT
STORE 0 TO LBAL_GTOT, L30_GTOT, L60_GTOT, L90_GTOT, L0_29GTOT

DO WHILE LNEXT .AND. .NOT. EOF()
IF LN>60
LPG=LPG+1
EJECT
@ 01, 01 SAY 'PAGE '
@ 01, 06 SAY LPG PICT '99'
@ 01, 20 SAY DATE()
@ 01, 35 SAY LCUSFILE
@ 02, 52 SAY 'CUSTOMERS REPORT BY '+LNDX
@ 04, 20 SAY LHEADING
@ 06, 15 SAY 'ACCOUNT'
@ 06, 25 SAY 'C O N S U M E R S'
@ 06, 54 SAY 'MULTP'
@ 06, 73 SAY 'READING'
@ 06, 84 SAY 'RECENT'
@ 06, 98 SAY 'NEW'
@ 06, 116 SAY '60-DAY'
@ 06, 127 SAY '90-DAY'
@ 07, 0 SAY 'ROUTE NUMBER'
@ 07, 15 SAY 'NUMBER'
@ 07, 23 SAY 'FIRST'
@ 07, 32 SAY 'LAST/FCOMPANY'
@ 07, 49 SAY 'CLAS'
@ 07, 54 SAY '-LIER'
@ 07, 63 SAY 'READING'
@ 07, 76 SAY 'DATE'
@ 07, 84 SAY 'PMT/ADJ'
@ 07, 94 SAY 'CHARGES'
@ 07,104 SAY 'BALANCE'
@ 07,114 SAY 'PAST DUE'
@ 07,125 SAY 'PAST DUE'
LN=9
ENDIF

STORE 0 TO LTOT_PMT, L60_DAYS, L30_DAYS, L90_DAYS, L0_29DAYS, LAGE_SUM
IF FBAL <= 0
LTOT_PMT = 0
L90_DAYS = 0
L60_DAYS = 0
L30_DAYS = 0
L0_29DAYS= FBAL
ELSE
IF FCURPMT >0
LTOT_PMT = FCURPMT
ENDIF
IF F30PMT >0
LTOT_PMT = LTOT_PMT + F30PMT
ENDIF
IF F60PMT > 0
LTOT_PMT = LTOT_PMT + F60PMT
ENDIF
IF FCURAMT < 0
LTOT_PMT = LTOT_PMT - FCURAMT
ENDIF
IF F30AMT < 0
LTOT_PMT = LTOT_PMT - F30AMT
ENDIF
IF F60AMT < 0
LTOT_PMT = LTOT_PMT - F60AMT
ENDIF
IF LTOT_PMT >= F90BAL
LTOT_PMT = LTOT_PMT - F90BAL
L90_DAYS = 0
ELSE
L90_DAYS = F90BAL - LTOT_PMT
LTOT_PMT = 0
ENDIF
IF F60AMT > 0
L60_DAYS = F60AMT
ENDIF
IF F60PMT < 0
L60_DAYS = L60_DAYS - F60PMT
ENDIF
IF LTOT_PMT >= L60_DAYS
LTOT_PMT = LTOT_PMT - L60_DAYS
L60_DAYS = 0
ELSE
L60_DAYS = L60_DAYS - LTOT_PMT
LTOT_PMT = 0
ENDIF
IF F30AMT > 0
L30_DAYS = F30AMT
ENDIF
IF F30PMT < 0
L30_DAYS = L30_DAYS - F30PMT
ENDIF
IF LTOT_PMT >= L30_DAYS
LTOT_PMT = LTOT_PMT - L30_DAYS
L30_DAYS = 0
ELSE
L30_DAYS = L30_DAYS - LTOT_PMT
LTOT_PMT = 0
ENDIF
IF FCURAMT > 0
L0_29DAYS = FCURAMT
ENDIF
* CHANGE TO FCURPMT
IF FCURPMT < 0
L0_29DAYS = L0_29DAYS - FCURPMT
ENDIF
IF LTOT_PMT >= L0_29DAYS
L0_29DAYS = 0
ELSE
L0_29DAYS = L0_29DAYS - LTOT_PMT
ENDIF
ENDIF

* SUMMATION OF PAST DUES
* LAGE_SUM = LAGE_SUM+L0_29DAYS+L30_DAYS+L60_DAYS+L90_DAYS

@ LN, 00 SAY FROUTENO
@ LN, 15 SAY FCUSTNO
@ LN, 22 SAY LEFT(FFIRSTNAME,9)
@ LN, 32 SAY LEFT(FCOMPANY,16)
@ LN, 49 SAY FCLASS
@ LN, 54 SAY FMULTIPL
@ LN, 63 SAY FREADING PICT '9999999'
@ LN, 72 SAY FREADDATE PICT '@D'
@ LN, 82 SAY FCURPMT PICT '999999.99'
@ LN, 92 SAY FCURAMT PICT '999999.99'
@ LN,102 SAY FBAL PICT '999999.99'
@ LN, 113 SAY L60_DAYS PICT '999999.99'
@ LN, 124 SAY L90_DAYS PICT '999999.99'
* @ LN, 135 SAY L60_DAYS PICT '999999.99'
* @ LN, 146 SAY L90_DAYS PICT '999999.99'
* @ LN, 157 SAY LAGE_SUM PICT '999999.99'

LBAL_GTOT = LBAL_GTOT + FBAL
L0_29GTOT = L0_29GTOT+ L0_29DAYS
L30_GTOT = L30_GTOT + L30_DAYS
L60_GTOT = L60_GTOT + L60_DAYS
L90_GTOT = L90_GTOT + L90_DAYS
SKIP
LN=LN+1
IF FROUTENO > LEND_RT
STORE .F. TO LNEXT
ENDIF
ENDDO
LN=LN+2
@ LN, 49 SAY "BALANCE = "
@ LN, 63 SAY " 0-29-DAY + "
@ LN, 85 SAY "30-DAY + "
@ LN, 105 SAY "60-DAY + "
@ LN, 127 SAY "90-DAY "
LN=LN+1

@ LN, 44 SAY LBAL_GTOT PICT '9,999,999.99'
@ LN, 62 SAY L0_29GTOT PICT '9,999,999.99'
@ LN, 79 SAY L30_GTOT PICT '9,999,999.99'
@ LN, 99 SAY L60_GTOT PICT '9,999,999.99'
@ LN, 121 SAY L90_GTOT PICT '9,999,999.99'
*@ LN, 99 SAY LBAL_GTOT PICT '9,999,999.99'
*@ LN, 121 SAY L90_GTOT PICT '9,999,999.99'
*@ LN+1, 112 SAY L60_GTOT PICT '999,999.99'

EJECT
SET DEVICE TO SCREEN
SET PROC TO
CLOSE DATABASES
RETURN

 

[Dan Freeman]

You know what value to insert and you have the code. You need to figure out the column coordinates for the insert.

I don't see the problem here, or what question you're asking.

 

[3dob]

Danfreeman,
Thank you for your prompt reply. The snapshot shown below is for the programming codes posted earlier which is okay to pull data from the current month MAR11CUS.DBF. However, I want to insert/include another column/codes [FBAL] from previous month FEB11CUS.DBF between [READING DATE] and [RECENT PMT/ADJ] as the BEGIN BALANCE for the current month. Both FEB11CUS.DBF and MAR11CUS.DBF are in the same directory.

I hope this clarifies what I'm trying to accomplish.

Again, thank you so much.

3dob

****************************************************************
PAGE 1 03/20/11 C:\KAJUR\CDATA\MAR11CUS

CUSTOMERS REPORT BY ROUTE NUMBER

ACCOUNT C O N S U M E R S MULTP
ROUTE NUMBER NUMBER FIRST LAST/FCOMPANY CLAS -LIER
10000 000002 127510 TRIPLE J BARRACK WMR 1


READING RECENT NEW 60-DAY 90-DAY
READING DATE PMT/ADJ CHARGES BALANCE PAST DUE PAST DUE
81064 02/28/11 0.00 936.53 53163.39 1244.85 50141.23
****************************************************************

 

[Dan Freeman]

OK, so you need to open a table, read a value, and then add the necessary @...SAY commands to output that value, doctoring all other output commands to move them to the right making room for the new column.

What part of that do you not know how to do?

 

[JRB-Bldr]

I believe that Dan is saying (and Dan, please correct me if I am wrong!) that you can count the columns of your report as well as we can and your counting can let you know which column you want to begin your new 'column'.

NOTE - that inserting a 'new' column also means that ALL 'columns' to the right of your new one on the same line will also need to change where they start.

Just so that you can interpret the FP code yourself, here is how to interpret the line:
[Code
@ LN, 79 SAY L30_GTOT PICT '9,999,999.99'
[/Code
You can look into your FP Help file for more info on the command.

Obviously the LN represents the Line number where the info is written.

The 79 in the above example code represents the 'column' value (counted in characters) where the printed field 'column' will begin printing.

And the L30_GTOT is the name of the variable/field which will be printed at that location.

Now once you have done as Dan suggests above and acquired the value of the new 'column' from the data table and put that value into a variable, you can create your own:

@ Row, Column FieldName

By using the optional PICT you can apply a Format to how the field value appears when printed.

If you are still having difficulty understanding what to do and how to do it I might suggest that you hire a FP/VFP consultant to make the modifications you need.

Good Luck,
JRB-Bldr

 

[TamarGranor]

I'm wondering if your question is how to make sure you get the right item from the second table on the right line of the report. The answer to that is to set a relation between the two tables. Based on what you've shown us, if you have an index for FEB11CUS on FCUSTNO, you should be able to do something like this:

SELECT FEB11CUS
SET ORDER TO FCUSTNO

SELECT MAR11CUS
SET RELATION TO FCUSTNO INTO FEB11CUS

You'll have to modify to fit your exact situation.

Tamar

 

[stesvet1]

If I good understood, you need only type:

1.
modify structure

2.
and then to insert your new column manually

3. and it should work

4.
after 1st-time running of the programm add * to your programm

* modify structure && note - use this command only for the 1st time

Stefan

 

[JRB-Bldr]

While it is not clearly stated, the original poster's question is now referring to inserting a new column in a data table, but instead into the report which is being printed by all of the @..,.. SAY commands.

They want to figure out the new column's Row/Column values and all of the new Row/Column values of the 'fields' to the right of the new one, based on the input that we have already provided above.