Create a query from specific data in multiple tables
I am using FMPro 12 Advanced and I am trying to create a portal list that is filtered to show only records based on certain field's specific data from multiple tables.
I have "Table 1" that has the fields "client name" and "status" (open, closed, etc) and I have "Table 2" that contains fields of products this client has purchased as well as fields indicating what the product cost is and if a payment was received for it. These tables have a relationship with eachother.
Here is the results I am looking for: Table 1 has several Open status records based on the value of the "status" field. Table 2 that is tied to Table 1 has a "cash amount" field for that client with a currency value entered and a "payment received" check box. I am trying to filter out the clients that are: "Open", has a value entered in "Cash Amount" (not null), and where the "Payment Received" check box is not checked, thus indicating that this open client has not made payment yet on their purchase.
So ultimately my portal list should only show the Open status clients (from Table 1) where a payment is still due (from Table 2).
In Microsoft Access, I'm able to make Queries that combines data from multiple tables and can create "If" conditions so as to list only those requirements, however I cannot seem to make this work in FMP.