4 Replies Latest reply on May 1, 2016 11:08 AM by AlastairMcInnes

    Create a table as a subset of another table?




      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?




        • 1. Re: Create a table as a subset of another table?

          your initial idea is ok.


          you just need to populate the small table with the record instances of the big one (typically importing una tantum all the PK's of the big one into the FK of the small one) and modifying the New Record script (you do have one, don't you ?) of the big table such that it creates a record in the small table - best implemented with a global var relationship which allows related records to be created and setting a field in the small table via this rel, which will create a new record. More on this technique is, for example, here.

          • 2. Re: Create a table as a subset of another table?

            I didn't realise you could create a script for new records!


            Where do I set it up? I only know about scripts triggered on layouts. If there's a new record script trigger that would solve my problem - thanks.

            • 3. Re: Create a table as a subset of another table?

              there's no such thing, but if new record creation happens exclusively via a new record script and an Import script, then you can undertake the right actions in order to have the small table be in sync with the big one.


              Just disable the record creation for user accounts and constrain them to use a script running with full privileges.

              • 4. Re: Create a table as a subset of another table?

                I see what you mean, but I can't see myself being popular if I tell them they can't just use File -> Import to import new data as they usually do.


                I presume the script would be pretty basic, though - just a call to ImportRecords with the full access checkbox ticked. They can then do everything else manually, and I can then loop through the found set creating the related records... That might well be the simplest way out of this.


                Thanks for taking the time to think about it for me.