Create a table as a subset of another table?

Discussion created by AlastairMcInnes on May 1, 2016
Latest reply on May 1, 2016 by AlastairMcInnes



I have an old FM database with 347 fields in a table. Arguably, many could be culled but I inherited the thing and it works and I don't want to mess about with it more than necessary.


I would very much like to access this table from an Access database using an ODBC external data source but Access won't link to the table because it says it has too many indexes. Research elsewhere suggests that it's actually the number of fields that's the problem, rather than the actual number of indexes. The limit suggested is 32 fields so clearly I'm not even in the right vicinity.


One possibility that has occurred to me is that I only want about 6 of the fields in Access so I was wondering whether I could create a new table in FM, related to the original one, and define 6 calculation field where the calculation is a reference to the field in the other table. So, in my new table, I define a calc field called, say, HB_ISBN where the calculation is just "Book::HB_ISBN". My idea was then to link to this new table from Access and all would be happy.


The problem is that I can't figure out if it's possible to persuade Filemaker to create such a table. I set it all up, but the new table has no records. I put a field from the new table onto a layout based on the old one and filled something into that field and, of course, FM then created a record and I can see the related data. What I can't guarantee, though, is that new records will always be created in the new table - a script won't work because the records will sometimes (usually) be created using an import so no layout will be involved to have the script trigger on it.


What I really need is what, in Access, would just be a SELECT query based on the table, which I could then use as a data source in its own right, but I don't think FM has that concept.


For the moment, I'm working in FM11, and I need a solution for FM11, but I'll be moving the database to 12 in a few months.


Can anyone think of a way of making this work?