How have you set up your database to track inventory? There is more than one approach for this and I would need to know how you've set this up before I can recommend much here.
"how do I set up a calculation that will only grab as much as is in stock and add the remaining part of the order to a field indicating rentals?"
Something like this might work for you:
Let's say the amount ordered on the line item is entered in a number field called Qty.
You could set up two calculation fields:
Amount Purchased : Min ( Qty ; OnHand )
Amount to Rent : Qty - Amount Purchased
That assumes you can get to an inventory field, OnHand that correctly computes the current amount of this item on hand and that depends on the design of your inventory system.
Thanks so much. I do have a field named Instock which tracks on hand inventory. It is on the inventory table and calculates WeOwn minus OutOnJobs.
WeOwn is a plain number field that we update manually. OutOnJobs calculates the total of all items requested for jobs (requested field = Qty field and is in lineitems table).
I'll do good ol' trial and error test runs of your suggestion. Thanks!
With the current setup, if I have 3 items in stock and request 6 for a job, then the Amount To Rent field give me 9 as a result. The reason for this is because the Instock field is updated simultaneously and becomes -3 when the requested amount is entered into the Qty field.
I'm guessing I'd need to have a different way of tracking instock inventory but I have no idea how. Or maybe I need a different Formula? Not sure...I don't knowhow the min (field) function works...but I'll try moving things around a bit. Please let me know if you can point me in the right direction.
Thanks so much for your input. Greatly appreciated.
Min stands for minimum and returns the lesser of the two values. I assumed OnHand would never return a value less than 0 since you can't have a negative inventory. (It tends to make your auditors rather testy :smileywink: )
It also should be a looked up value that copies the current inventory level (not including this current transaction) from your inventory table so that subsequent orders don't change the results of this order by changing your current inventory counts.