5 Replies Latest reply on May 7, 2009 2:27 PM by philmodjunk

    Auto fill field with data from same field from most recent (date/time) preceding record

    jnew

      Title

      Auto fill field with data from same field from most recent (date/time) preceding record

      Post

      Hi everyone,

       

      Sorry, I'm not sure if the subject makes sense...  Let me try to explain a little better.  We have a client database where we create a new record -- "activity notes" -- for each client when we do some activity on their behalf.  Each activity has a date and time.  And on each client's page we see a portal list of these records (activity notes) sorted by date and time.  When we create a new activity record, it would be great if certain fields were automatically filled with the data from the last activity record; by "last record," I mean what was the most recent, date/time-wise, activity record for that client, before creating this new record.  Does that make sense? 

       

      Thanks so much in advance!

      Jesse

        • 1. Re: Auto fill field with data from same field from most recent (date/time) preceding record
          philmodjunk
            

          This can be done but to give specific advice requires more information about the stucture of your database and to more precisely define which record is the "most recent record".

           

          I think you want the "most recent" record of all those visible in the portal, not the "most recent" of all those in the table from which the portal records are drawn, correct?

           

          If so, then you can set up a self join relationship that sorts by date and use that relationship look up or auto-enter information in the way you want. I'd need to know what field or fields are used to control which records are visible in the portal before I can be more specific.

           

          Please describe the table used in your layout and the related table that supplies the records you see in the portal. Please describe the key fields used in this relationship.

          • 2. Re: Auto fill field with data from same field from most recent (date/time) preceding record
            jnew
              

            Thanks so much PhilModJunk!

             

            And I'm sorry if my explanation is a bit limited, due to my limited FM experience.

             

            Okay, yes, I'm pretty sure the record I want is the most recent visible in the portal.  Let me try to explain the structure a bit... 

             

            So, I actually oversimplified earlier.  First, there is a client table.  Then for each client (we're a legal aid nonprofit), there can be one or more case records (via a case table).  Then for each case, there are multiple activity records, via a "case history" table.  Relationships between two numerical fields--Client and Case ID--link the three tables together.  When a new activity (i.e. case history) record is created, it is assigned (via script) the same Case ID # as the case record to which it is linked.  Last, the portal, located on a case table page, is just set to show "related records" from Case History.

             

            Does that help?  

             

            Thanks again!

            Jesse

            • 3. Re: Auto fill field with data from same field from most recent (date/time) preceding record
              philmodjunk
                

              So what you need is to add a "self join" relationship to the Case History table.

               

              Enter Manage | Database | Relationships

              Drag from Case History::CaseID out a inch or two and then back to the same field. A dialog box will pop up asking you to name a new "table instance". Name this new table instance "SameCase".

              Double-Click the new relationship line linking Case History and SameCase to bring up the Edit Relationship Dialog.

              On the SameCase side of this dialog, click the sort records check box and select the date field that tells you this is the "most recent" record.

              Click Ok

              Click the Fields tab.

               

              For each field in Case History where you want to look up data from the most recent record of the same CaseID,

              Double Click it to bring up the field options dialog box

              Click the Auto-Enter tab

              Click the Looked-up value check box

              In the "lookup from related table" menu, select SameCase

              In the Copy value from field: box click the field that you want to copy from.

               

              Once you've done this for each field where you want this feature, Click OK until you've closed all the dialog boxes and try it out.

               

              • 4. Re: Auto fill field with data from same field from most recent (date/time) preceding record
                jnew
                  

                Thank you so much!  That worked perfectly! 

                 

                PS I apologize if that was something I should have been able to figure out on my own.  But, as I mentioned, I work at a small nonprofit, so I'm actually just a staff attorney, doing my best to maintain our database on the side :).

                • 5. Re: Auto fill field with data from same field from most recent (date/time) preceding record
                  philmodjunk
                    

                  No apologies needed. This wasn't a trick that just anybody can figure out.

                   

                  This forum draws together both "Newbies" and experienced developers into the same community.

                   

                  I'm just glad it worked for you.