Perform Add/Remove QTY

[MonkeyNetMan]

Hey guys. I'm not sure how to do calculations for adding/removing quantities to an existing quantity on another table.

See the attached FM file for more details.

All I want to do is...

1. Whenever I put a number in "Added QTY" section it will automatically add that amount of number into the "Current QTY"

2. Whenever I put a number in "Removed QTY" section it will automatically remove that amount of number into the "Current QTY"

3. If possible, I would like to have whatever "Last QTY" be the quantity it was before revised. Example:

- I had a "Current QTY" of "10" on 1/1/2007

- I add "2" in "Added QTY" on 1/2/2007

- I want it to automatically enter "10" in the "Last QTY" in the "Added" section

I hope that makes sense...

if not, that's cool.

Thanks!

partlistinventory.zip

[Brian C]

Just use one field called InputQty and one script that does the following:

IF [if(Current QTY + Input QTY ≥ 0]

SetField[Current QTY ; Current QTY + Input QTY]

ELSE

Show Custom Dialog[Alert ; "You cannot adjust the QTY to a negative amount."]

END IF

This allows you to use one field to input the change, and this allows you to use 1 script to either add or subtract from the qty.

All you have to do is input either a positive amount to add to Current Qty or a negative amount to deduct from Current Qty.

[MonkeyNetMan]

I don't think I can use a single Input QTY field, I need it to be separate for adding and removing - so I can keep record of when it was added and remove. Unless I'm missing something from that script. Thanks for your input though, Brian!

[Brian C]

OK Just finished looking at your file.

If you want to have your Current QTY linked to the related records you could:

Change your Current QTY to a calculation field that is a sum of all the adds and subtracts from each of the 2 different tables via your existing relationships.

You could then input a Starting QTY record in the add section to get things going and then anything after that would either add or subtract from the total.

To get your Last QTY to work - just setup the field with an autoenter calc that inputs the number stored in Current::Current QTY at the moment the record is created.

[MonkeyNetMan]

Hahaha, now I feel stupid. Thanks for your time, Brian, I think I got it from here now. Would be nice if I can see an example, to make things quicker for me - if not, it's cool I'll try to learn this from scratch.

[Brian C]

The only weakness of doing it this way is that until the record is comitted, the last record for each add or remove will still be the same Current Qty. This is because any changes to the record and any portal changes are still treated as temporary until the record commits.

partlilstnew.zip

Edited July 26, 2007 by Guest
Added file

[MonkeyNetMan]

Hmm. Any suggestions on how I should go about this then?

[Brian C]

I uploaded the file to my previous post. I fixed your relationships and set things up. The only flakey part is the Last Qty field picking up the current qty before it adds the record... This part will have to be scripted so that it will get it reliably.

In other words, a script should run after each record add so that the Current Qty (which is an unstored calculation) can be stored in a normal field. This field should be what the new records grab. The script would then update the field with the new number upon comitting the record.

[MonkeyNetMan]

You are a god, Brian! I didn't even notice you attached it on your previous post. This actually works fine for me. I think I'll just remove the Last QTY section, since it seems too much of a hassle to deal with anyway.

Many kudos to you dude! I think this is all the help I need!

Again, thank you for taking your time on this!

[comment]

You could make this simpler by having a single table for both types of transaction.