What is the definition for "z_Stock CF new"? Is _fk_Period a data field (number or text) or a calculation field?
This sounds like another version of the issue we've been discussing here: Slow calculations
There's a work around discussed there that might work for you here.
'z_Stock CF new' is a calculation field which sums up stock movements from 'z_stock previous period' and a number field 'Transfers to Warehouse". Transfers to warehouse is a number field which is manually entered by users. It is kind of a running balance (as given as an example) but for each of the currently 95 line items.
_fk_Period is a number field. it is in relation to the period table where the key is defined by "year"&"week".
Reading the other post, I agree it is a similar problem. Sadly, I think our approach was the solution for that problem there as we tried all the mentioned approaches before coming up with our solution. I have no speed problem despite working with almost 20'000 data sets in this table (~95 line items x 208 weeks). All Filemaker does is display the '?' after 2014.07 (for each line item). First we thought was that there was a problem with one of the factors but when clicking on a field which shows '?', Filemaker displays the correct value. This indicates that the calculation "exhausts" itself.
I would use a summary field for such a running total instead of this chain of calculations. See this thread for example of how a "transactions" table can be used to track inventory levels with a summary field computing the stock on hand in a running total: Managing Inventory using a Transactions Ledger
And if you just need the current total on hand, either a summary field, an aggregate function calculation from the products table or a script updated number field can show that value.
And the work around mentioned in the other thread--using an auto-enter feature to copy the balance from the preceding record should also be workable as past inventory transaction records aren't something that should be changed except in very rare circumstances.
Thanks for your feedback.
How would you handle it, if having a "stock at hand" wasn't enough? The table is used in a separate portal to forecast future productions and "transfers to warehouse". The portal shows in one tab the history (where I believe you are quite right that a summary field could work if we only want past data) but has a 2nd tab where we forecast into the future. The user uses the "z_Stock CF new" as indication, when a next production of the line item is needed. Production volume is entered in a future week (portal shows records +1 year) which increases "z_Stock CF new". Future sales are deducted as well by forecasting "transfers to warehouse". The later obviously changes constantly depending on sales from the warehouse to customers. Users create 6 month worth of production and transfer forecasts which are both weekly updated.
If you could explain in more detail how you think a summary field could work in this set up (I even tried it with 2 summary fields; one for history and one for future), that'd be great. I definitely still can't see a solution. Cheers!
That is why I posted a link on an "inventory ledger". The basic data model from which you start is key.
In a list or table view layout, a summary field, set up as a running total summary field can show your total on hand at the point in time where each transaction changes the amount in inventory. This works exactly like a bookkeeping "ledger" where the right hand column is a running balance calculation where you can see an account balance rise and fall with each transaction.
Great, I managed to do it for the Stock CF (which is in real life the factory stock).
Moving on to the Warehouse stock (which comes further along the supply chain) I'm facing the same problem. The reason why I decided to avoid sum fields here in the first place was that I had to work with a case formula to get the right factors in:
Past (anything which is previous to the current date):
A line item is ordered by several PCCs. The sum of all purchase orders (PO) in one week is deducted from the warehouse stock. Also, we transfer products from the factory into the warehouse, which needs to be added to the stock.
Future (anything which is post the current date)
We have a forecast for each line item, which predicts how much POs we'll get per week. BUT depending the PCC and customer, we might have already a fixed PO in the system. If there is a PO quantity available, the system needs to ignore the forecasted figure. Please note that the forecast is different to the PO quantity - we measure how accurate the forecast is and therefore we don't amend it once a PO is in.
Rest of the calculation is the same as for the past.
Currently I'm operating with this calculation to calculate "z_Stock Warehouse":
_fk_Period ≤ (Year ( Get ( CurrentDate ) )) & "." & (Right (0 & WeekOfYear ( Get ( CurrentDate )) ; 2)) ;
z_Stock Warehouse previous period + Transfer to Warehouse in CU - z_PO to PCC + Stock Warehouse Correction ;
_fk_Period > (Year ( Get ( CurrentDate ) )) & "." & Right (0 & WeekOfYear ( Get ( CurrentDate ) ); 2) ;
z_Stock Warehouse previous period + Transfer to Warehouse in CU - ( If ( z_PO to PCC > 0 ; z_PO to PCC ; Order Forecast UK) ) + Stock Warehouse Correction)
I don't see how to solve the red bit with a sum field, as the Sum of "Order Forcasts UK" will be a completely different figure (as different history) than "z_PO to PCC".
- Past (anything which is previous to the current date):
My demo file doesn't use any sum functions if memory serves correctly. It uses summary fields.
In order to "selectively sum" records in another table you usually set up a relationship that only matches to the values you want to sum. You use values in match fields in place of the boolean calcs in If or Case functions to control what group of records are summed to provide the total. This works both for Sum functions and also for summary fields defined in the related table.
With FileMaker 12 or later, you now can set up the relationship and sum the value all in one SQL statement so you have that as an additional option for computing a total for a selected set of records.
And I might log "future" or "forcasted" inventory transactions in the same table as my actual transactions but with dates and possibly a "type" field to identify them as such.
PCC = Primary Consolidation Centre. It's used by retailers before sending stock into their DCs (local distribution centres).
Apologies, I was of course referring to summary fields and not sum functions.
Thank you very much for your input, I will have to digest it first. Not quite sure what you mean by:
"And I might log "future" or "forcasted" inventory transactions in the same table as my actual transactions but with dates and possibly a "type" field to identify them as such."
I have one forecast figure per week and line item which is in the same table as the sum of all PO's for that line item and week. So the figures are basically in the same table.
I know what you mean with setting up relationships to get an additional option for computing a total. Not quite sure how to use it here but will run tests... Thanks again! Hope this works.
The main workhorse table in the Inventory ledger system to which I referred is a "transactions" table where each change in inventory is logged by creating a new record or records in this table. This would include moving inventory items from one location to another as that move reduces inventory at one location and increases it at another.
Since each transaction should be dated, you could add future or forecasted transactions to this table and they wouldn't necessarily affect the total on hand as long as you take steps to exclude such items. The fact that the date is in the future could be used if you use a field with the current date as one of the match fields or perform finds that omit the future transactions on list views of the transactions. A "type" field that identifies a transaction as "future" or "forecast" might also be useful in such.
The idea behind that is so that you can include the forecasted transactions in a view of your data to see how you current on hand totals will change if your future transactions become actual transactions and yet be able to exclude it when such "future" data is not relevant to what you need to see.