I’m trying to develop a matrix box form. I want it to show the average selling price for an item/customer for a given date, so the form will be passed a filter of customer, and then show a list of items down the page, dates across the top of the matrix and then prices accordingly. So far, I’ve created a flowfield on the Item table called “Average Sell Price” which calculates an average from the Sales Invoice Line unit prices. Also, I’ve added a “Shipment Date” Flow filter to the Sales Invoice Line file and created a key with the Item Type/No and SumIndexField of unit price. Finally, I’ve created a form with Item as the source table, an embedded matrix box linking to a matrix source table Date, and the source expression for the matrix data of “Average Sell Price”. This sort of works in that it correctly calculates the average selling price for an item. The only problem is, it takes no notice of the data across the top of the matrix, so each column shows the same price. My Cal Code includes a SetDateFilter function that tries to setrange over Sales Invoice Line acording to the new Shipment date filter, and the Matrix After Get Record function calls the SetDateFilter and does a Calcfield, but to no effect. Any suggestions what I may be missing? I’m obviously not a million miles away
So far, I’ve created a flowfield on the Item table called “Average Sell Price” which calculates an average from the Sales Invoice Line unit prices.
Originally posted by SkippyKGS - 2004 Nov 11 : 10:53:52
I have noticed that as the number of our posted invoices rises this & the Sales Invoice Line is getting slower & slower. Also invoices can be purged (your situation may never need to do this) so I was curious on why you wouldn’t use the Item Ledger Entries instead?
Hi Why am I used the Sales Invoice Line rather than Item Ledger Entries? No particular reason. It just seemed like the logical place to get this information from, although I’m open to the idea of using the Item Ledger Entry table instead providing the customer is on each record as this will be needed for my criteria. What does concern me slightly is your comment about the sales Invoice Line getting slower and slower as the number of posted invoices rises. Why should this happen and why would it effect the Sales invoice table and not the Item Ledger Entry? Thanks for the input so far. Skippy
Our orders generally range in the 50 - 200 lines per order. I have found that using the Item Ledger Table works best for us. It might not be true for everyone. My sales line contains 3x as many fields as my Item Ledger Entry table. Could be the reason for the slow down. **The source number in Item legder Entries will match your Customer Number. Sorry this went a bit off topic…I just was curious[8)]
To be honest I’m just learning about the concepts of a Date based Matrix at the moment, and trying to get it to work. This scenario is similar to one I’m going have to develop a solution for so this will by no means be the finished object. What I might try doing (just for a bit of fun you understand!) is create two versions, one over each table, just to see if there are any obvious speed discrepencies.
Also, I’ve added a “Shipment Date” Flow filter to the Sales Invoice Line file and created a key with the Item Type/No and SumIndexField of unit price.
- The Sales Invoice Line key should be: “Item Type, No., Shipment Date”. 2. The “Shipment Date” Flowfilter on Sales Invoice Line is of no use. Use the “Date Filter” on Item in stead, i.e. the “Average Sell Price” CalcFormula must include “Shipment Date”=FIELD(Date Filter) BTW Item Ledger Entries is definitely the “correct” table to use.
Hi SV, Thanks for the tips. Worked them into my code and it now works perfectly. Thank you. Just had a look at the item ledger table and the only problem is it doesn’t have the unit price for the transaction (unless I’m looking in the the wrong place). Short of adding this field, I may have to stick with the Sales Invoice Lines table
Hi Skippy, Yes you’re right. In older versions, though, the Item Ledger Entries held the Unit Price - In newer versions you have to look in the Value Entries and this table only holds amounts not unit prices.
If you have a (sale) “Amount” field & a qty field you could just divide them to get the unit price
Surely this presumes that no discounts apply to the entry? Unfortunately, I’m at home so can’t see the tables but from memory I don’t think there are discount fields on the Item Ledger Entry table.