3 Replies Latest reply on Nov 29, 2013 9:37 AM by philmodjunk

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

    mchancevet@gmail.com

      Title

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

      Post

           G'day,

           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,

           Morgan

      Screenshot_2013-11-27_11.33.56.png

        • 1. Re: Looking up/calculating a field from the 2nd last record in a dynamic defined set
          philmodjunk

               How does the Tally field compute a value?

               Does Talley compute a value totalling all inventory counts over all your locations?

               And you might want to take a look at this thread on inventory management: Managing Inventory using a Transactions Ledger

          • 2. Re: Looking up/calculating a field from the 2nd last record in a dynamic defined set
            mchancevet@gmail.com

                 Hi PhilModJunk,

                 You've responded to several of my posts now. Thank you very much, great advice.

                 This 'tally' field is for staff to enter counts of items during an audit of a 'kit' or location. The input is an edit box via a layout of the Audit table which has a portal of related 'audit kit contents' records.

                 The 'Last Tally' field is a calculation as you've seen and I would like it to retain the value of the previous (2nd last if your working on a new record) 'count/tally' of the same item in that location.

                 Clearly my calculation does not achieve the previous tally although it does give the value of the 2nd last tally that has been performed.

                 I'll check out the thread, cheers

                 Morgan

                  

            • 3. Re: Looking up/calculating a field from the 2nd last record in a dynamic defined set
              philmodjunk

                   It would make sense to me to use these relationships to audit your first aide kits:

                   Instead of scrolling back and forth, I'll use my own names here. You'll need to subsitute yours for mine where needed:

                   Kits----<KitContents------<Item_Audit>------------Audits

                   Kits::__pkKitID = KitContents::_fkKitID
                   KitContents::_fkItemID = Item_Audit::_fkItemID
                   Audits::__pkAuditID = Item_Audit::_fkAuditID

                   For an explanation of the notation that I am using, see the first post of: Common Forum Relationship and Field Notations Explained

                   A portal to Item_Audit on the Audits layout can be used to record tallys for each item counted in the audit of a particular kit. A script can allow you to select a kit for an audit in a field with a value list of KitIDs and then use the Kits to KitContents relationship to populate that portal with one record for each item listed in KitContents for that kit. A previous tally value would then be a record in Item_Audit with the same ItemID but with the Audit ID of the previous audit. A self join to another occurrence of Item_Audit could be used to access that data.

                   Item_Audit::_fkAuditID > Item_Audit|Previous::_fkAuditID AND
                   Item_Audit::_fkItemID = Item_Audit|Previous::_fkItemID

                   Select a sort option for Item_Audit|Previous that sorts by _fkAuditID in descending order to make the record from the previous audit the first such related record and then you can refer to: Item_Audit|Previous::Tally to get the tally for that item in that kit from the previous audit.