3 Replies Latest reply on Aug 22, 2010 8:48 AM by RoryDale

    Changing external data source mysql table



      Changing external data source mysql table


      I'm sure this is a quick solution, but I'm scratching my head about it. I have a website that inputs data to a mysql database. I have a filemaker front end to this for viewing and editing the data. Each year, I create a new mysql table and a new filemaker file, duplicating it and updating the database info, so last year was info_2010 and this year will be info_2011.

      I can't figure out the easiest way to change my filemaker file to connect to the new database using all the existing layouts and fields, just drawing data from a different table. Every thing I can come up with results in <Table Missing> or ::Field Name suggesting that one by one I have to tell each field where to pull its data from.

      There has to be an easier way that I am just not getting. I've tried Layout Setup and Show records from info_2011, but that doesn't work. 

      Anyone have a quick solution that I'm just not aware of??

      Thank you in anticipation...

        • 1. Re: Changing external data source mysql table

          I suspect the table at least has to have the same name. Could you try to rename the tables so the new one is info_2010 and see if it works?

          I'm not sure if MySQL allows to edit data in views, but if yes, a solution could be to create a view, e.g. ‘current info’:

          CREATE VIEW current_info AS SELECT * from info_2010;

          and then change it each year to point to a different table.

          • 2. Re: Changing external data source mysql table

            Thanks for the suggestion, I tried, but the problem I am having all seems to be at the Filemaker end. I go to Manage Database and click the Relationships tab. From here I can add the other table (info_2011), then I select the layout and then choose Layout Setup, Show Records From info_2011. When I click OK, the fields in the table show <Unrelated Table>. If I delete info_2010 from the Tables, and rename info_2011 to info_2010 the message is then <Table Missing>. I could manually re-associate all of the fields, but that is what I'm trying to avoid just to save time.

            • 3. Re: Changing external data source mysql table

              Ok figured it out!! Manage Database, double click the table that is in the Relationships tab, and select the different table there. Bingo!