Looking up/calculating a field from the 2nd last record in a dynamic defined set
I'm still quite inexperienced at FM 12. I'm currently developing an inventory system for multiple locations. I have a strong suspicion I have done this a very inefficient and clumsy way but it's offering a useful learning curve.
The system tracks items that exist in batches or lots with different expiry dates and these are distributed to each location from a central store. I have set up each location (called a Kit) as a separate record in a table called kits
I need staff at each location to be able to perform a tally for each product at that location and I have established an audit table that has related 'content audits' for each item at that location. These are date and time stamped. A layout of each kit has a portal which displays the contents of that kit. A very similar layout displays 'content audit records' for each audit of a kit and this is where a tally value can be entered for each item. I would also like to display the value from the last tally of that item at that location.
I have a calculation field ‘last tally’ which is a partial solution to this (screen shot of the calculation included) . The table is related to itself via fields ‘kit contents ID’ and ‘kit ID’ and this relationship is sorted on date and timestamp so I can ‘filter’ the calculation results to only show the previous tally that is for that item in that kit.
Unfortunately through my structure, this field shows a dynamic result and whenever a new tally is entered the calculation changes. This means I cannot go back and look through older audits and see what the previous tally was on that occasion and I cannot base cumulative inventory calculations on the field ‘last tally’ as I would like to.
The 'Last Tally' field (which gives the tally value prior to the current tally in progress) calculation is
GetNthRecord (Audit kit contents 2::Tally; Count (Audit kit contents 2::Audit Kit Contents ID)-1)
Hopefully I have explained this effectively.
· Given the current structure is it possible to make the calculation result static or ‘stored’
· Is there a more efficient way to set up the calculation/lookup for a 2nd last record of the ‘tally’(or any other field) from a set which has the same ‘Kit ID and Kit Contents ID’ (or other field specification), other than setting up a ‘self’ relationship with the table
Thanks very much,