2 Replies Latest reply on Apr 27, 2017 7:05 PM by taylorsharpe

    ODBC

    TKnTexas55

      I have ODBC to my MSSQL files of my accounting software, Great Plains.  I am slowly following the 'logic' of the data structure. 

       

      I can add calculation fields  and summary fields to aid.  I cannot add text fields.

       

      With one table I wanted to add a text field to make notes on the AR Aging. 

       

      With a second table, outstanding payables, I want to add a text field as a way to select that item to be included in a special total.

       

      So my next thought was to have a second table to contain any text fields, a one to one table.  But I am not sure how to create this.  My ODBC information is dynamic increasing and decreasing as invoices are added or paid. 

        • 1. Re: ODBC
          beverly

          you can create a new FileMaker table with a "foreign key" that would match a "primary key" in the MySQL table. Then relate the two on the Relationship graph. You cannot create in the MySQL from FileMaker ODBC. But you can add fields to the FM table.

          beverly

          • 2. Re: ODBC
            taylorsharpe

            Are you using ESS or are you just using ODBC to import the data?  It is easiest to use ESS because you can make table occurrences of the MSSQL tables right in the FileMaker Relationship Graph and then use the primary key field in the MSSQL table as a one-to-one relationship with your native FileMaker table.  You can use the join relationship to find non-matching records and add those primary key values in the FileMaker table in a script pretty easy. Likewise, you may want to delete any records in your FileMaker table if they no longer match a record in the MSSQL table.