How do I programme in a reducing balance on a database

[deanwarner]

Hello

I am trying to programme so that I get a reducing balance against records with the same partno.

Example

Part Number Movement Prevpro Reducing Balance
= Prevpro - Movement

Part A 50 1000 950 (1000-50)
Part A 500 1000 450 (950-500)
Part A -550 1000 1000 (450--550)
Part B 300 2000 1700 (2000-300)
Part C 700 0 -700 (0-700)

I have been trying to crack this using this code and are getting nowhere.

I am sure this is a really common requirement but I can't get the thing right in my head.

If anyone can show me the light I thank you.

use c:\ammdata\mschco1i in 0 shared order tag partno

newpart = mschco1i.partno
chg = mschco1i.produced

scan

if newpart = partno

replace mschco1i.prodbal with mschco1i.produced-chg

else

newpart = partno
replace mschco1i.prodbal with mschco1i.produced-chg
endif

endscan

close databases

 

[TheRambler]

Hi,

See thread184-774669, rgbean's post will give you an idea. It is not exactly the same situation because you don't have a type of transaction field ("D" or "C"), but still you could use it checking whether your movement field is positive or negative.

 

[deanwarner]

Thank you Rambler, I will see if it can help and get back.

 

[TamarGranor]

In general, it's considered a bad idea to store calculated fields like this because they can get out of synch with the underlying data. You have three items there and you can compute any one of them from the other two. Store two and compute the third when you need it.

Tamar

 

[tesar]

deanwarner,
reduce your fields and use start quantity:

Part Number Movement Reducing Balance
= Prevrec - Movement
Part A -1000 1000
Part B -1000 1000
Part A 50 950 (1000-50)
Part A 500 450 (950-500)
Part A -550 1000 (450--550)
Part B 300 1700 (2000-300)
Part C 700 -700 (0-700)

and then for balance added record:
*
urn = recno()
pn = part_number
pb = 0
do while !bof()
skip -1
if part_number = pn
pb = balance
exit
endif
enddo
go urn
repl balance with pb - movement
*
Tesar

 

[deanwarner]

This may be premature but I am very happy. Tesar response and put me on the road to success. I have used the code and put it into what I am doing. When I have made sure it is doing what I need I will post it.

Thank you all so much or your help thus far.

 

[deanwarner]

People

This is what I ended up with - something I could not have done without your assistance.

Thank you for your time and support.

I now have a reducing balance in mschco1i based on the production held in produc1i. I have change the maths since the first post but this is what I needed.

Thanks again.

I only put this up in case it can help someone else.

use c:\ammdata\mschco1i in 0 shared order tag partno
delete all for partno = "NO WORK"
USE C:\AMMDATA\PRODUC1I IN 0 SHARED

SELECT PRODUC1I
SET ORDER TO TAG delpartno IN PRODUC1I ADDITIVE
SELECT mschco1i
SET RELATION TO mschco1i.partno INTO PRODUC1I


SCAN

URN = RECNO()
PN = PARTNO
PB=0

DO WHILE !BOF()

SKIP -1

IF PARTNO = PN

PB = PRODUC1I.ttlprod+mschco1i.qtychg
EXIT
ENDIF
ENDDO
GO URN
replace mschco1i.prodbal with PRODUC1I.ttlprod+mschco1i.qtychg
replace PRODUC1I.ttlprod with PRODUC1I.ttlprod+mschco1i.qtychg

ENDSCAN

close databases