9 Replies Latest reply on Jul 24, 2010 6:07 PM by fitch

    Pulling Fields from previous Records



      Pulling Fields from previous Records


      I am having issues figuring out how to pull information from prior records.

      What I am trying to do is pull a field from each records that has a Name = (the name I am currently working with).  In other words, I am trying to set a script that once I enter in the Name it will populate fields that are being pulled from all other records with the same name.  Like if I enter Chris, I want it to pull all the previous Phone Numbers(a field in the database) with the name Chris on the same record.  How would I go about doing this, all help is greatly appreciated.

        • 1. Re: Pulling Fields from previous Records

          Please keep in mind the risks/issues of working with names in this manner. Basically, you can have more than one person named "Chris" and you may also have alternate spellings such as "Kris" to contend with or even mispellings of their name.

          If You define this relationship:

          YourTable::NameField = YourTable 2::NameField

          You can do what you describe. YourTable 2 is a second table occurrence of YourTable, it points to the same data source table, but has a different name so that you can define the relationship you need. To create it, drag from YourTable::NameField to a blank area of the relationship graph and then back to YourTable::NameField.

          This is good for setting up a portal to YourTable 2 to display records that may be for someone with the same name. I wouldn't automatically copy the data from any of these matching records for the reasons given at the top of this post.

          A better way may well be to link two tables of information by an ID number field rather than a name. Table 1, Contacts, would have one record per individual and would store all information specific to that person such as name, address, phone, etc. Table 2 would store your other data that you link to a selected record in contacts. An example of this would be a table of invoices where you link each invoice to a specific contact record in order to disply the customer's name, address etc on the invoice form.

          • 2. Re: Pulling Fields from previous Records

            You could make another Table Occurrence and relate it by Name = Name. Then use the calculation List( related::Phone Number ).

            • 3. Re: Pulling Fields from previous Records


              Thanks for the quick responce, the I was merely using an example of Name I actually have a unique identifier for each person that comes in (same each time for the person but no possible repeats).  This is a brand new database so I can still change things however needed, so do I need to have 2 seperate tables for this to work best?  One that holds that basic person identifier and the other with all the information that is entered?  The key thing I need is for each entry to be associated with the person that comes in and the data is likely to be different each time they come in.  So for a basic example, I need to pull the date, what they bought, how much it cost and a contact number for each occurance.  This would presumably all be entered in on a single layout (but from what you are saying) different tables?  So the idea is when they come in again and I enter their name I will run a script that will pull up all past history of sales in a small report.

              Quick additional question, the List thing you gave Fitch is great I think I have figured it out a bit, how would I go about pulling each one individually though?  Like say if I wanted to capture each of the last 5 visits seperately so I could format the information as needed?  Obviously list works great but it returns $10,$20,$20,$30,$40 and then Shoes Shoes Burger Shoes Shoes or w/e and I'd like to be able to say "<<Date>>, <<What was purchases>>, <<How much it cost>>, <<contact info>>"

              • 4. Re: Pulling Fields from previous Records

                It would help to describe what you want in more detail. Also what version of Filemaker are you using?

                This sounds like you are generating sales invoices for customers. If so, then yes you will need several tables and you can work with the records from different related tables on the same layout.

                A typical invoicing set up looks like this to start and then more tables may be needed, depending on your business needs...

                Clients----<Invoices----<LineItems>----Products(or services)

                Each time you get a new customer, you record their info in Clients. Each time you make a sale, you create a new invoice record and use a portal to LineItems to list each different type of item and quantity sold. Products is the table where you have one record for each good and/or service you offer to your customers.

                If you are using a filemaker 10 or 11, you can find an invoicing Starter Solution that comes with filemaker that you can either modify or just examine to learn how this works.

                • 5. Re: Pulling Fields from previous Records

                  I have FMP11 Advanced.

                  Ok so I'll be a little more direct in what I really need I was trying to come up with a simple example.  This is being used for something more along the lines of an EMR (electronic medical records), a simple one albeit but thats the idea.  Basically I need to be able to access prior information from encounters.  I would like to be able to pull say all the Heights/Weights/etc and be able to create a growth chart with those data points, or say have a running history of their temperature on each visit etc.  So for example these are a couple of the hurdles I am tring to address.  I'd like to be able to pull the lab reports from their previous visit (not all visits just the most recent) same for something like their medication plan.  I also need to be able to pull an entire history of data points for things like Height/Wieight/etc so I can see long term trends etc.  Those are the two basic needs I have at this point, ability to pull last visit from a certain patient, and ability to pull information from all visits from that patient. 

                  Thanks so much for the help its really great.

                  • 6. Re: Pulling Fields from previous Records

                    You'll need a number of related tables for this.

                    Patients : Put name and contact info here

                    Visits : create a new record here each time patient visits the doctor and log date, time, purpose of visit etc.

                    From there, you'll need to figure out how many related tables you'll need to record medical data. Log the measurements by date and a visit ID so you can use either when reviewing the data. You may find it is easier to log multiple types of measurement in the same table (one type of measurement per record) and label them to discriminate between types of measurements for reporting purposes or you may find it works better to have separate tables.

                    Lab Reports sound like something you might store as PDF's in a table of container fields, again with additional fields logging both the date and the visit ID.

                    To view related records from the most recent visit, use relationships to the related tables that are sorted in Descending order by Visit ID and the most recent visit will first on the list. In some cases, you may need to include an extra pair of fields with the  ≠ operator in order to link to the 2nd such set of data on the list if the table may contain records from the current visit.

                    These are just general suggestions. The devil is in the details of how you choose to set them up.

                    • 7. Re: Pulling Fields from previous Records

                      In addition to List, you may want to familiarize yourself with the functions: GetValue, LeftValues, RightValues, MiddleValues, and GetNthRecord.


                      • 8. Re: Pulling Fields from previous Records

                        Good advice, though I didn't actually mention the List function in my last response. ;-)

                        • 9. Re: Pulling Fields from previous Records

                          I mentioned List in my last response.