I need serious help. No, I mean with FileMaker

I have a relationship based upon numeric-date range key Products::cNumDteRange = LineItems::cNumDteShip AND Products::ProductID = LineItems::ProductsID

Request: On each Product, look backwards one month from today (only counting workdays). Get sum total of quantity sold from two fields: Quantity (positive number) and ReturnedQty(negative number). Divide sum sold by workdays to determine (on average) how many are per workday (based upon the past month). Look at our Inventory and project out the DATE we will run out of product.

I have calc called cSoldPerDay1Mo (number) of:

Div(Sum(LineItems::Quantity) + Sum(LineItems::ReturnedQty) ; cWorkDays)

I have field called cProjectDteOut (date) of:

Get(CurrentDate) + Div(cSumInventory ; cSoldPerDay1Mo)

I get a question mark in my final date field! And is there a way improve this thinking? It feels very clunky to me. I dislike calcs that work on calcs but I don't know how else to approach it.

LaRetta

I get a question mark in my final date field!

I'll bet that one of the Div's are dividing with zero, which doesn't make the OS crash as you would have had it if attempting the same in c++, however; filemaker is polite, knowing that such errors occures among laymen, frequently and shows a ? instead.

I dislike calcs that work on calcs but I don't know how else to approach it.

Yes you do, make a series of let statements in one single calcfield!!!

--sd

Good morning, Soren!

Okay, I'll try to figure what is wonky with my Div()s.

make a series of let statements in one single calcfield!!!

But I need two fields - one showing number sold per day and the other showing the projected date we'll run out. But I agree. It feels convoluted and I'll focus on a alternate attack. Thanks!

LaRetta