3 Replies Latest reply on May 20, 2012 4:32 AM by GuyStevens

    Auto Populate fields from one field.( Lookup ?? )



      Auto Populate fields from one field.( Lookup ?? )


      Hi All,

      Have been chasing my tail trying to populate several fields from the answer of another field.

      I'll keep it simple .

      Database contains three fileds

      Customer, Address and Serial Number.

      What I'm trying to achieve is when I type a serial number into the serial number field it looks up and auto populates the customer and address field with the correct information.


      Please bear in mind Im new to this, so please explain like your talking to a three year old !!. I've  tryied many examples but cant find one that populates the fields.  

      I'm assuming I will need two tables that have relationships setup and I'm guessing I should be using the LookUp Script ?

      If anybody could help me out that would be much appreciated or even send over a sample file which I could dissect and lean from.

      Thanks  Dave'O

        • 1. Re: Auto Populate fields from one field.( Lookup ?? )

          Hey Dave,

          You would be right in needing two tables.

          The idea is that you create two tables that each have their own ID field.
          This field should be a number field with the auto enter option of "Serial Number"
          Then in one of tho two fields you provide a number field to store the ID of the other table. That's called a "Foreign Id".

          So you'll have something like this:

          Customers = is the table
          ID -  is a number field with the auto enter option of "Serial Number"   So the first record is number "1" the second one "2" etc.
          You could also make a calculation field that calculates the full name.

          Then you make a second table. I don't know what you need to make exactly,
          but let's say you are a doctor and you need to create admissions for patients.

          You'll have a second table with the information for each admission. It'll look something like:

          Admissions = is the table
          ID -  is a number field with the auto enter option of "Serial Number".
          PatientIdFk  -  this is where the patient ID will come and this is the field that you need to relate to the PatientID in the Patient table.
          AdmissionDate could be another field here. This would be a date field.

          Now you can fill out the PatientIdFk fiend on your admission in different ways. The simplest way is that you just manually type the ID number of that patient. But you might not know them all by heart. So you'll probably use a dropbox. Or you'll start from a patient record and then you make a button "Create New Admission" that sets the patientID in the PatientIDFK field in the Admissions table.

          When a record in the admissions table has a PätiendId in the PatiendIdFk field it can display data from the Patients table.
          So the idea is that you just put the fields "FirstName", "LastName" etc from the Patients table on your Admissions table.
          In the beginning they will be empty, but as soon as you fill in the PatientIdFk field, magic happens :)

          Wow, that sounds rather complicated. Don't worry, it isn't. But if you want to cheat. I made a quick little example:


          This is really basic and you can take this a lot further, but this is the general Idea.

          • 2. Re: Auto Populate fields from one field.( Lookup ?? )

            Hi DaSaint

            What a Star you are... I'm reading your answer several times and its slowing sinking in. Quiet simple in a complicated way.!!

            Your attachment you drop boxed over is just the kiddy. Exactly what I'm trying to do...Time to get the knives out and cut open and dissect.

            Thankyou for your plane English explanation, much appreciated.

            Now this will keep me busy for hours.....



            • 3. Re: Auto Populate fields from one field.( Lookup ?? )

              Hehe, don't worry, you'll get it and you'll be surprised at how simple this really is.

              If you have any more questions, just let me know.