10 Replies Latest reply on Sep 2, 2009 1:10 PM by philmodjunk

    Finding most recent data in a table

    truckerclock

      Title

      Finding most recent data in a table

      Post

      I have a field that is a calculation that uses Max to find the record with the most recent date and I want to do something similar with finding the username that made an entry most recently. The username is stored in the same table as the date I am retrieving and unfortunately for me Max does not work to find a username. I have a script that finds all records that have a Y for a certain field and then I also want to see the most recent contact date for that record which I am doing by using Max but I also want to see the username that made the contact. Thanks in advance. 

        • 1. Re: Finding most recent data in a table
          donjuancarlos_1
             You could create two fields that track the username and date of the last modification.  In your Field options in the Auto-Enter tab, you select the Modification check box and select Name/Date.  Is that what you mean?
          • 2. Re: Finding most recent data in a table
            mrvodka
               Have you check out Last ()?
            • 3. Re: Finding most recent data in a table
              philmodjunk
                

              Key point to keep in mind:

               

              Max() does not "find a record". It returns the largest value of a specific set of values. That's why it worked for your date field and not for your name field. Max(relatedtable::textfield) will return the value of all the referenced records that sorts alphabetically as last in order which won't have anything to do with the date in a different field of the same table.

               

              To expand on what Mr. Vodka suggested, If your relationship is sorted in ascending order by your date field (this is an option you can set in Manage | Database), last() can be used to return the value of any specified field in the record with the most recent date.

              • 4. Re: Finding most recent data in a table
                comment_1
                  

                truckerclock wrote:
                I also want to see the most recent contact date for that record which I am doing by using Max but I also want to see the username that made the contact. Thanks in advance. 

                Why don't you use a one-row portal, sorted by date, descending. Place any fields you want to view from the most recent related record in the portal.


                • 5. Re: Finding most recent data in a table
                  truckerclock
                    

                  I did not try to use a portal because I want the data to be in a report and did not think that would work well. I think the Last() may be what I need but will not be able to try it until later. To provide a little clarity to what I am looking for is I have a contact management database with a field to show whether a customer is critical care either Y/N and that field is in the contact management table. I have another table called call reports that salespeople record when they call customers to check on on them, sell other product etc. It automatically saves their username in FM and they enter a date. I have a script that performs a find to find records that critical care is yes. In the report I have a field that is a calculation to find the most recent date from the call report, which should be the most recent call and I also want the username of the person that recorded that call. Does it sound like Last() is the best solution for me?

                  • 6. Re: Finding most recent data in a table
                    philmodjunk
                       I'd try the single line portal. No reason why that won't do the job if you add it to your report layout and it should be much simpler to set up.
                    • 7. Re: Finding most recent data in a table
                      truckerclock
                         A portal can only show records from one table though right? If that is the case how do I get around that?
                      • 8. Re: Finding most recent data in a table
                        philmodjunk
                          

                        I'm not sure I understand your problem. You may need to give an example. In any case, you can show data from multiple tables in one portal if the data is from tables with valid relationships to the the table occurrence that the portal is based upon. Once you've set up the relationships, you can simply add the fields to the portal and select them from the related table occurrence instead of the portal's table occurrence.

                         

                        I haven't seen any need for that in the example discussed in this forum so you may need to explain further here.

                        • 9. Re: Finding most recent data in a table
                          truckerclock
                             I did not realize I could have data from two tables in one portal so that may solve my problem. As I said in the earlier post all of the customer info, customer number, address , phone number, etc. is stored in the default contact management table along with a field that specifies whether or not they are a critical care customer. I have another table called "call reports" that is related to the contact management table by the customer number field. When a sales rep enters a call report, also known as a contact with the customer, phone call, face to face visit, etc. they enter the date, what kind of call it was, who they talked to, comments and the username and timestamp are automatically entered into the call reports table. I have a script to show all customers that the critical care flag in the contact management database is set to Y. This script shows me the customer number and name from the contact management table plus the last date they were contacted from the call reports table using the Max function to find the most recent call date. I want this layout that shows this info when the script runs to also show the username that was automatically stored when they entered the call report that goes with the record that has the date that was found with the Max function. Hopefully this is clearer?
                          • 10. Re: Finding most recent data in a table
                            philmodjunk
                               If I traced that through correctly, it should work. Try it and see.