AnsweredAssumed Answered

Looking up/calculating a field from the 2nd last record in a dynamic defined set

Question asked by on Nov 26, 2013
Latest reply on Nov 29, 2013 by philmodjunk


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.

     Two questions

     ·         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,