Relationship for external SQL data
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
To populate my FM created calc field "purchordstatus" in the PurchOrdDet table I have the following relationship
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)
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.