1 Reply Latest reply on Mar 5, 2014 6:13 AM by beverly

    ODBC Error

    sqlguy

      I am trying to pull my schedule via my SQL View (ERP System) and generate a few pop-ups based on the data pulled. I want some static fields within the report in Filemaker and some fields (pop-ups) as well. These pop-ups once pulled need to be modifyable, however, once changed they should not be pushed back to the ERP system that the data was originally pulled from. The pop-ups will be a work order and customer field.

      Want:

      1) The customer should be tied to the work order I select.

       

      Issue:

      1) When selecting the pop-up for the work order or customer I receive an error that states:

       

      ODBC Error: [Microsoft] [ODBC SQL Server Driver][SQL Server] The column reference "inserted.name" is not allowed because it refers to a base table that is not being modified in this statement.

      [Microsoft][ODBC SQL Server Driver][SQL Server] the column reference "inserted.CustomerID" is not allowed because it refers to a base table that is not being modified in this statement.

      [Microsoft][ODBC SQL Server Driver][SQL Server] Statement(s) could not be prepared.

       

      I have a feeling either there is an issue with the Value List or the security? If you have an idea let me know.

      Thanks,

        • 1. Re: ODBC Error
          beverly

          I would suggest that you IMPORT these lists (from SQL server to FMP table-s.) Then create your value lists from the imported data. You can change to your hearts content in FMP and it will not make the change in the SQL. Added bonus is that these are indexable by FMP and will save on trying to make the query to SQL each time you'd need them for the lists otherwise. It would just make them faster, more efficient.

           

          Use the same DSN to query the SQL (or set up a new source), but use the Import script step. You have the option of specifying an external source for the import. You will be give the dialog to choose the SQL table(s) and to make the SELECT statement you'd use for the import. If you need to make the same query more than once (and it seems not so), then you can script the process to import.

           

          Then you can make ExecuteSQL queries for particular pop-ups and/or use the related options to be specific for each list (as needed for a work order).

           

          You are correct that you may have other issues with the security. And you should post your query to let us see if we can debug it for you.

           

          Beverly