Calculating and Recording Transaction

[ORahmo]

For sometime i wanted to create a program to track the transaction History of an Inventory Item with it's Average cost in the a table

For Example

TAC "Total Average Cost"
TTC "Total Transaction Cost"


Item Qty Ucost TTC Acost Balance TAC

x 5 10 50 10 5 50
x 5 12 60 11 10 110
x -2 11 -22 11 8 88
x 20 9 180 9.571 28 268


- Recording the Data is Simple
- Calculating Average Cost is also Simple
- When Qty is -Ve, take the previous ACost as your UCost "Unable to do it"
- Calculating Average Cost Per Transaction " Unable to figure it out"
- Calculating Balance Per Transaction "Unable to figure it out"


I don't know what kind of information you need so you could lead me to the way of completing the program, so tell me what u need from the program and i will post it

 

[Mike Lewis]

I think you will need to explain exactly what you mean by "total average cost" (total of what, exactly?) and also "balance"?

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips, training, consultancy

 

[ORahmo]

Each Transaction i want to record Balance, Average Cost, and Total Cost of Item "using Average Cost"

Meaning

Transaction 1 was 1st transaction on item so the balance after 1st transaction is 5 which is the same balance as the transaction because item balance was zero

-"Balance" Displays the Item Balance After the Transaction of Item X
-"Total Average Cost" Displays Total Item Cost Which is the current Item cost in the inventory "Average Cost x Item Balance"



P.S. Attached an Excel Sheet with formulas that explain it more clearly

 

[gjjgrewe]

Omar,

When I try to open your spreadsheet, I get an error message that says the file is in a diffrent format than specified by the extension. I tried in Excel 2007 and 2010. Can you try uploading the file again?

 

[ORahmo]

i created 3 formats
Excel 95
Excel 97-2003
Excel 2008-2010

Thank you for your efforts

 

[JRB-Bldr]

Admittedly I have not taken the time to look at the Excel formulas in your attachments...

Regardless, if you can create the formulas in Excel, why can't you do so in VFP?

Good Luck,
JRB-Bldr

 

[ORahmo]

the purpose of the program is to calculate the average cost of items

the reason why i am not using it from excel sheet because we got inhouse application but his calculations are incorrect

i know the business logic and thats why i displayed it on the excel sheet but i want it to run from the inhouse application as i am trying to find bits and pieces from the tips that u can provide me to achieve that

 

[JRB-Bldr]

the purpose of the program is to calculate the average cost of items

I assume that is what your Excel calculations were already able to accomplish. Otherwise there would have been no point in attaching them.

the reason why i am not using it from excel sheet because we got in-house application but his calculations are incorrect

I am not suggesting that you do the calculation in Excel.

But you obviously have some calculation algorithm(s) in Excel which are working for you.

Why can't you just use the same calculation in VFP?

Yes, you are not doing the calculation on 'cells' in specific 'rows' and 'columns', but you can do it on field values in records in a VFP data table.

If the math (and the associated algorithms) are accurate in Excel it should be equally accurate for your purposes in VFP.

What's the problem?

Good Luck,
JRB-Bldr