4 Replies Latest reply on Feb 16, 2010 11:52 AM by Pijush

    How to retrive a field from a previous record in the same layout?

    Pijush

      Title

      How to retrive a field from a previous record in the same layout?

      Post

      I am trying to build a Data base to record pupitual Inventory for my business.

       

      InventoryID( SerialNo)                         Date 

      Begining Inventory

      + Purchase

      -Sales

      =Ending Inventory.

       

      I like to forward that Ending Inventory to my New record as a Begining Inventory. How can I accomplish that ?  

      Any help in this regards highly be appreciated.Thank you in advance. PK

        • 1. Re: How to retrive a field from a previous record in the same layout?
          philmodjunk
            

          What you are looking for is a called a Running Balance Summary field

           

          Let's change your calculation slightly:

           

          Make a field called cBalance and define it as Purchases - Sales

           

          (You might want to rename them "in" and "out" as there are other reasons besides purchases and sales that add/remove items from inventory.)

           

          Define a summary field as the "total of" cBalance and set it up as a running balance. You'll probably want to also select the "restart totals when grouped by InventoryID" also.

           

          This will enable you to set up a list view layout that resembles a ledger with your summary field showing the total on hand at each point in time where you log an inventory change. There are several threads here that discuss this Inventory Log in more detail. You can find them by clicking the Advanced search link and entering those words in the search form.

           

          (You'd set up your initial balance as the first entry with the balance on hand entered in Purchases.)

          • 2. Re: How to retrive a field from a previous record in the same layout?
            Pijush
              

            Thank you for solving the problem indirectly.

            But I was looking for a technic or command to retrive a fieldvalue from a previous record of the same table.

            Some logical solution like " If Inventory_ID=(Inventory_Id-1), then SubTotal=Begining_inventory",  if any exit.

             

            PK

            • 3. Re: How to retrive a field from a previous record in the same layout?
              philmodjunk
                

              The running total summary field will likely work better in the example you've posted.

               

              There is a function you might be able to get to work for you: GetNthRecord.

               

              Adapting an example in the help file:

               

              GetNthRecord(First Name;Get(RecordNumber)- 1) returns the contents of the First Name field for the previous record in the current table.

               

              However, this returns the previous record in the found set, not the table as the help entry appears to indicate. If you use this function, it must be unstored and you'll need to be careful to perform correct finds and sorts in order for your calculations to return accurate results. (The results and limitations are almost exactly the same as the running total field I described in my previous post except the running total won't automatically restart with each new group of item entries.)

              • 4. Re: How to retrive a field from a previous record in the same layout?
                Pijush
                   Thank you so much for the info. It solved my problem. I had to just add a Summary field "NoRecord count". It is working fine. I tried deleting, adding records and it passed the test every time. Thank you again. Pijush