3 Replies Latest reply on Apr 9, 2014 6:05 AM by philmodjunk

    auto-populate when importing into another table



      auto-populate when importing into another table



           I'd like to populate a table with client-id's and their last-activity at the moment I import a file called "deliveries" which contains all id's and all their last-activities. (could be thousands of lines). 

           I know how to use the function "Max" in a calculation to find out which activity is the latest for the client.

           What I want to do is: create a table with only unique client-id's (PID) an their latest activity (max). 

           I appologize for maybe not being totally clear; i am dutch and try to be as precise as i can be. 



           In the screenshot you see six PID's (Person-id's, client-id's: 164062,164039 etc...) with their activities. 

           I'd like a tabel that only contains six id's (PID's) and the latest activity. 

           164062:22-01-2014 09:30

           164039:10-03-2014 09:00




           OOoooorrrr...: how can I import only UNIQUE PID's with it's latest activity into my deliveries-table? (so: don't import if it is not the latest client-activity) WOW, if you could show me that, ...would be the best! tnx


        • 1. Re: auto-populate when importing into another table

               I don't think that you need another table nor do you need to "import" the data into deliveries table. It's possible to set up a relationship that automatically shows the most recent record for a given PID. The relationship can match by PID and be sorted in descending order by activity_Date.

               And if this requires a "self-join" (linking the table to an occurrence of itself), the relationship can be modified to keep a record from matching to itself.

               Assuming that you have two tables:

               Deliveries::PID = Activities::PID

               Then, in Manage | Database | Relationships, double click the relationship line and select a sort order for activities that sorts by Activity_Date in descending order. IF you place fields from Activities on the Deliveries layout, they'll show data from the most recent activity record.

          • 2. Re: auto-populate when importing into another table

                 Hi Phil, tnx. I'm not sure if I did it exactly as you described, it works fine in my layout indeed, BUT: when I perform a search, where I want to search for ONLY the latest activities it returns also those that are NOT the latest activity. Any thoughts on how to perform a search (preferrably via script eventually) within only the latest activities?

            • 3. Re: auto-populate when importing into another table

                   Please describe what you are doing when you "perform a search" (exact steps) and please describe the layout on which you performed that search.