having trouble with calculating related fields

scenario: inventory bin consolidation app

Item 3AT17 is in 5 locations - 2 locations have just 1 unit, other 2 have 36, 65, 94 units. we want to move the two single units to the 36 unit bin. (see below)

 

the locations/item info is in a portal. The move to list  from the related locations field - showing all locations with the item. 

How do I calculate the new total for the "move to"  field? 

 

Layout is pulling from Item Master (IM_) with related tables Count (CO_) and location (LO_)
Relationships:
IM_ITEM=CO_ITEM
LO_Location = CO_Location & LO_ITEM = CO_ITEM
 

Field Names:
Location:  CO_LOC
item : CO_ITEM
QTY: CO_QTY
move:CO_RELOYN
Move QTY: CO_RELOQTY
Move To: CO_RELOTO
 

For the above example moving 1unit from 2 bins - into third bin need a cell for the new qty in that bin. 

so in this example new qty would be 38.

I am sure I am missing something that would help with figuring this out... 

 

 

I am not sure I understand the basic situation here. If you have decided to move a quantity from one record to another, I would expect this decision to be executed by a script, not by a calculation. A script that would load the quantity to be moved into a variable, delete the source record, go to the target record and add the value in the variable to the target's existing quantity (not necessarily in this order).

 

we are leaving the original location for record keeping, but the general thought process of the script will do exactly what I need i think.

 

Thanks

Just keep in mind that if one of the records involved in the transaction is locked by another user, you need to roll back (that's why I said "not necessarily in this order").

 

gotcha - this will be used by single users, or in the case of multiple users they handle specific locations/warehouses so that shouldnt be an issue. but I will keep it in mind for the day they decide everyone can do everything all the time

Also consider that things can happen while the transaction is in progress, ie, power failure, network failure, crash of the device, etc. If you are in the middle of a transaction then your data can get corrupted. Ie, you might end up having too many or not enough items.

One way to deal with this is to use to use a global field "source", and a global field "target" and relate these to both records, so that you move data from "source" to "target". Then when you're done you commit. If something happens during the transaction, FM automatically cancels the edit.