So if I have this correct (as a novice VFP programmer)....
I create my "cursor" as is with the select statement. Then before I go and run the report - I run a routine similar to this that goes through the cursor and updates the records accordingly? I thought cursors were READ ONLY? Do I still use the cursor or would I be better off creating a table, a couple cursors and merging, or something.
Here is my current cursor for this report if it helps any....
Select client.clientnum,client.clientname,code.codetype, ;
code.codenum,code.codedesc,log.time, ;
sum(iif((code.codetype="Cash Receipt" or code.codetype="A/R Writeoff") and log.logdate
iif(code.codetype="Billings" and log.logdate sum(iif(code.codetype=="Billings" and ;
log.logdate>=gStartDate,log.rate,0.00)) as Billings, ;
sum(iif(code.codetype="Billings" and ;
log.logdate>=gYearBeg,log.rate,0.00))as BillingsYTD, ;
sum(iif(code.codetype="Billings Start Bal" and ;
log.logdate>=gStartDate,log.rate,0.00)) as BillingsStart, ;
sum(iif(code.codetype="Cash Receipt" and ;
log.logdate>=gStartDate,log.rate,0.00)) as Collections, ;
sum(iif(code.codetype="Cash Receipt" and ;
log.logdate>=gYearBeg,log.rate,0.00)) as CollectionsYTD, ;
sum(iif(code.codetype="A/R Writeoff" and ;
log.logdate>=gStartDate,log.rate,0.00)) as Writeoff, ;
sum(iif(code.codetype="A/R Writeoff" and ;
log.logdate>=gYearBeg,log.rate,0.00)) as WriteoffYTD, ;
sum(iif(code.codetype="Cash Receipt" or code.codetype="A/R Writeoff",log.rate*-1, ;
iif(code.codetype="Billings",log.rate,0.0))) as EndBal ;
from Timepro!client inner join timepro!log on client.clientid = log.clientid ;
inner join timepro!code on log.codeid = code.codeid ;
where client.firmid=gFirmid and log.logdate<=gEndDate;
into cursor cursARsum ;
group by client.clientname ;
Thanks
>Peter,
>
>Yuck. OK, I would forget trying to get the tightest possible query and just do something that will definitely work. How about:
>
>>SELE MySummedResults
>SCAN
> Pt = P30 + P60 + P90
>
> IF PT > 0
> PmtThisPeriod = MIN(C30,PT)
> REPLACE A30 WITH C30 - PmtThisPeriod
> PT = PT - PmtThisPeriod
> ENDIF
>
> IF PT > 0
> PmtThisPeriod = MIN(C60,PT)
> REPLACE A60 WITH C60 - PmtThisPeriod
> PT = PT - PmtThisPeriod
> ENDIF
>
> IF PT > 0
> * Do 90 days here, same as two above
> ENDIF
>
>ENDSCAN
>
>
>That's off the top of my head, so you'll probably need to debug a little. Notice particularly that it will probably bomb on credit balances. I would think with this type of approach you would add credit balances into the payments for a period. Hope this helps.
>
>>Yes and no... all payments (no matter in what period) apply to the oldest charges first. Example, if the C90=$500 and C60=$250 with no payments and the P60 is $100 then the P60 actually comes off the C90 not the C60. Based on that, the report would show the A90 as $400, the A60 as $250, etc... That is why I think I need to keep track of the payment total as well.
>
>--Snip--
>
>>>>
>>>>KEY: Ax = Aging total due
>>>> Cx = Charges for that period
>>>> Px = Payments for that period
>>>> PT = Total of payments for all periods
>>>>
>>>>90+ If PT > C90 then A90=0, PT=PT-C90 else A90=C90-PT, PT=0
>>>>60 If PT > C60 then A60=0, PT=PT-C60 else A60=C60-PT, PT=0
>>>>30 If PT > C30 then A30=0, PT=PT-C30 else A30=C30-PT, PT=0
>>>>Cur A30=C0-PT
>>>>
>>>>I use SUM in the SELECT to get the Cx & Px totals. The problem I am having is that I am not sure how to handle the double "math" that is needed for each side of the IF so that I get the right amounts as I work through all the formulas.
>>>>
>>>>Suggestions?
Peter Brama
West Pointe Enterprises
VFP is getting easier but STILL alot to learn!!