Quantity on Hand Calculation

[Lola]

Hello All,

 

I have two tables

 

Materials Table and History Table

 

In my Materials Table I Have the Following fields

 

Quantity Received

Quantity Used

Quantity in Hand

 

History Table

 

Quantity Dispensed

Quantity Returned

 

Can you please help me find the right formula on how to calculate my Material Balance.

 

For example

 

If I have a 20 Kg Received ; then they Dispensed 5 Kg, Then My Quantity return will give me 15 Kg ;

 

The formula that I'm using to find my Quantity on Hand is

 

If ( IsEmpty ( Sum ( History::Units In ) - Sum (History::Units Out ) ) ; 0 ; Sum ( History::Units In ) - Sum ( History::Units Out ) )

 

The result is not matching with my Quantity received when I add it.

 

Any help is appreciated.

 

Thank you.

[Wim Decorte]

This is the kind of thing you normally do not want to do through calculations but by setting the "quantity in hand" through the workflows that influence it (receiving and using).  Those would be scripted workflows.

Leaving this as calculations is going to be extremely slow for searching and reporting

[Lola]

Hi Wim,

 

Thank you.

 

Please find the copy of the file that Im working

Warehouse Management Copy1.fp7

Edited October 12, 2015 by Lola

[ggt667]

Or calculation done in a number field.

As pr example; usually demands less resources in a WAN deployment; not sure if my example suits your needs, I'm sure it can be tweaked to fit your purpose.

Warehouse Management Copy1.fp7.zip

Edited October 12, 2015 by ggt667

[Lola]

Hi ggt667,

Thank you for the file. This is my purpose and the use of my Material History. I want to use my Material History to track The Lot No. of a certain material to which it is used. and the number of quantities they use.

If I issue an Inventory Card to them  they will fill out the Units Out and The Quantity of the Material they are Returning ( The Units in)

I want those figures to appear to my Material Unit on Hand and Material Quantity Used.

I would appreciate if you can modified the file that you posted.

 

Thank you so much.

[Rosalie]

Whats the Acct Name and PW?

Edited October 13, 2015 by Rosalie

[webko]

Default settings:

 

User: Admin

Password:

(ie, no password)

[Lola]

Sorry,

The User is Admin ; Password is blank