6 Replies Latest reply on Sep 11, 2009 7:56 AM by SYako

    Relationship for external SQL data

    SYako

      Title

      Relationship for external SQL data

      Post

      Having issues with multi-criteria relationship of external data. I have a Construction schedule system in Filemaker that I want to display purchase order details from our SQL based purchasing system however I want to limit the purchase order detail records that display in a Filemaker portal based upon criteria.

       

      Here are some details:

      Filemaker Table name: "Job" match fields of "jobno", "purchordstatus" and "varcode" Note "pruchordstatus" and "varcode" are text globals created strictly to match data values coming from SQL data tables.

       

      I wish to display Purchase Order Line item detail records from our SQL system that reside in the PurchOrdDet table.

      However match fields are in two different SQL tables. The line item table holds a variance code which is one of the values I wish to relate against and the second is the Purchase Order status which is maintained in a header table named PurchOrd.

       

      Basically want to display all line item detail records that have been entered with a variance code however if the PO itself gets cancelled then I don't want the records to display because they are irreleavent since the entire Purchase Order was cancelled.

       

      SQL Tabename: "PurchOrdDet" match fields "projectid", "varcode" and "PurchOrdStatus" (Note "PurchOrdstatus" is an FM Calc field I added in FM Fields tab since the purchase order status lives in a different SQL table)

      SQL Tablename: "PurchOrd" match field "status"

       

      I have created external SQL tables into the Filemaker relationship graph and created the following realtionships

       

      Job::Jobno=PurchOrdDet::projectid

      and Job::varcode=PurchOrdDet::varcode 

      and Job::purchordstatus=PurchOrdDet::purchordstatus

       

      To populate my FM created calc field "purchordstatus" in the PurchOrdDet table I have the following relationship

      PurchOrdDet::purchordstatus=PurchOrd::status

       

      When I populate my filemaker global fields JobNo::purchordstatus and JobNo::Varcode with the values I want to display in my portal, I receive no data records.

       

      If I alter my main relationship graph and focus only on the varcode relationship: (remove the third qualifier PO Status) 

       

      Job::Jobno=PurchOrdDet::projectid

      and Job::varcode=PurchOrdDet::varcode 

       

      I get data records however it includes data records for canceled purchase orders which is what I am trying to filter out using the purchordstatus match field. Note that my FM created "PurchOrdStatus" field that I added to the SQL PurchOrdDet table populates and displays correctly in my portal.

       

      Am I doing something that is not legitmate?

      Is it the FM calc field I am rying to add to the SQL purchorddet table through a relationship?

      If this cannot be done maybe I should import data into FM table so I have all my match fields in one table and refresh?

       

      Any thoughts help much appreciated.

       

      Steve Yako

       

        • 1. Re: Relationship for external SQL data
          mrvodka
            

          It wont work like this as using a calc based on a ESS field can not be stored.

           

          The easiest remedy for this is to create a view on the SQL end that includes "projectid", "varcode" and the status field from the PurchOrd table.

           

          Then use that view in your relationships within FileMaker.

           

           

          There is another option, where you can filter the relationship from one table and return the IDs in a multiline calc using the List fucntion. Then related that to the other table, but performance-wise going with option 1 is much better if you can.

          • 2. Re: Relationship for external SQL data
            SYako
              

            Thanks mr_vodka.

             

            I was feeling that I probably needed to address on the SQL side however my SQL skills are week outside of standard query select and update statements but what the hey knowledge never hurts. I'll work on creating a view and let you know how it works out.

             

            Thanks Again.

             

            • 3. Re: Relationship for external SQL data
              SYako
                

              New problem. I have created the sql view but when I try to bring into FM it wants me to pick a unique key field.

               

              Is there a way around this because the data fields I have brought into the SQL view have no combination that would ensure  unique value.

               

              Thanks

              • 4. Re: Relationship for external SQL data
                mrvodka
                  

                Creating a view is very simple. Most SQL GUIs have a quick way to create them. If not, you can just run a SQL statement using CREATE VIEW.

                 

                 

                • 5. Re: Relationship for external SQL data
                  mrvodka
                     How did you get PurchOrdDet into FileMaker then?
                  • 6. Re: Relationship for external SQL data
                    SYako
                      

                    Creating the view with the SQL 2005 wizard was easy.

                     

                    As far as how I got the purchorddet table in yesterday it did not give me any hassles.

                     

                    Difference today is that I am using a different DB so maybe that is the issue. Yesterday I was reading data from an entire copy of the application DB. Since I was new to the view thing I didn't want to be trying to educate myself using a production system Database. So I created a new SQL DB and imported only the purchord and purchorddet tables into it and then created the view.

                     

                    I'll get myself a copy of the entire db restored that I can play with and go from there.

                     

                    Once again thanks mucho for your help.