3 Replies Latest reply on Jan 17, 2012 5:16 PM by naustghoul

    Views in FileMaker

    naustghoul

      Might be the wrong subject for this, if so I apologize and will move it later, but the question I have is:

       

      If we are using FileMaker as a front end for our SQL DB and wanted to populate a FileMaker GUI with information generated by a SQL view we have created is it handled the same way as pointing a FileMaker GUI interface to create a new record (i.e. contact person would have our SQL DB as the database it conects to for all the field data and where the records are kept) or is it handled another way, since the table to point to will not itself exist until the view is called?

        • 1. Re: Views in FileMaker
          Stephen Huston

          I believe the SQL View is what is referenced in the FileMaker External Data Reference for the TO (table occurance) in FileMaker. That FM TO is essentially a shadow table of SQL via the SQL View. Posting new records should be possible via FileMakers built-in New Record when you are on a layout with the correct TO as the base table for the layout.

           

          This should also work for related record via a portal, but the relationship basis needs to be very clear. I have seen FileMaker assume that a Primary Key was one field when it should be another because SQL and FMP don't talk to each other well on that. FileMaker tends to make assumptions based on whether or not it finds one or more "unique-value" fields in the SQL View.

           

          I have seen it guess wrong, and the relationship is tenuous when that happens.

           

          Caveat: there are other people who read this forum with lots more experience using SQL with FMP than I have. I was discouraged by the lack of reliability in Key fields the last time I tested it, so haven't a lot of experience in live use.

          • 2. Re: Views in FileMaker
            beverly

            Hello, naustghoul.

             

            The "view" in SQL is a query that results in a "temporary table". It's a great way to show a join that needs to be called often or a filter to narrow a large set of SQL data. If this view has a unique primary key field, then it should be available to see in FileMaker. Check your ODBC setup to see if you can also use views, and check with your DBA to see if you have access to them. If so, then you can put it on the Relationship graph as a table occurrence (TO), just like tables. Depending on permission, you may or may not be able to EDIT the view data.

             

            If you have the "view" on a layout calling the TO, it exists when you use the layout, just like any other table from External SQL Source.

             

            Beverly Voth

            • 3. Re: Views in FileMaker
              naustghoul

               

              I did actually find what I was looking for... If I had only looked into the handling of the data source for FileMaker I would have found that there is an option under the Filter Tables section that allows you to filter IN types (tables is default) so just checking that box fixed the issue. Thank you all for your help.