3 Replies Latest reply on May 7, 2012 1:55 PM by philmodjunk

    Look Ups from ODBC

    CoryPearson

      Title

      Look Ups from ODBC

      Post

      So needs some help please..... Posted Image

      So in my FM data base I want to enter in our job number and have it look up information from our ERP system and populate some basic information like;
      Customer Name
      CSR
      SalesPerson
      ETC.

      (Based on us putting in the job number.) Although I'm not sure how putting in number and having it populate multiple other fields is the trick?
      Most information is looked up via ODBC data connection via OpenJob table. However in OpenJob I have customer# and will need to link our Customer table to OpenJob to get the Name of the Customer.

      Can someone help please??

      Example

      Table: OpenJob - IN ERP
      JobN-99999
      Customer Number: 1212
      CSR # 111

      Table: Customer - IN ERP
      Customer Name: ABC Company
      Customer Number: 1212

      Table: CSR - IN ERP
      CSR Name: Jane Doe
      CSR # 111

      Table: Filemaker
      JobN:
      Customer Name:
      CSR Name:
      SalesPerson Name:



      Job# 99999 (the lookup from ERP System via odbc connection and poputate)
      Customer Name: ABC Company
      CSR: Jane Doe
      SalesPerson: John Smith

      Any help would be greatly appreiated

      Thanks
      Cory

        • 1. Re: Look Ups from ODBC
          philmodjunk

          It depends on what you mean by "populate". You can set up a system that copies data in to fields in your current record, or it can simply use fields from the related table to display the current values in that record.

          As far as I know, the fact that the records are from external tables via ODBC does not change this basic set up. Whether you copy or just display the data requires a valid relationship based on the Job# field.

          Here's a link you can look at to learn more: Auto Fill

          • 2. Re: Look Ups from ODBC
            CoryPearson

            Populate meaning....

            When I type in the job name I want it to (fill-in other fields that relate in our ERP system) what ever value that is in the ERP system in the above fields for. 
            Customer Name, CSR, SalesPerson

            • 3. Re: Look Ups from ODBC
              philmodjunk

              Please read the link I posted. It addresses both options.

              And what you describe can be done with both of the two methods I describe in that link. The difference is in what happens when data in the original table is modified. With one option, the changes automatically update what you see in your layouts and reports. With the other, they do not. Both approaches have their uses as sometimes a "snapshot" of the values that were current at the time the data was looked up is what you want and in other cases you always want the most recent version of the data to always appear.