1 2 Previous Next 18 Replies Latest reply on Jun 11, 2012 1:31 PM by tracylynn1212

    Create a query from specific data in multiple tables

    tracylynn1212

      Title

      Create a query from specific data in multiple tables

      Post

      Hello, 

      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.

       

      Thank you!

        • 1. Re: Create a query from specific data in multiple tables
          GuyStevens

          I think your "Status" field shouldn't be in the first table.

          Also I have a feeling you are creating multiple records in your first table while your first table should be yout "Contacts" table that contains one record for each contact.

          Then you probably also need a "Products" table where you have all your products but only one record per product.

          Then you will need at least one table to create something like an invoice.
          And you might need a line items table for in case one client buys multiple products on one invoice.

          This is a pretty classic structure.

          If you want you could open the Invoice Starter Solution to have a look and see how it's done.

           

          Getting information to show in a portal requires that you set up correct relationships.
          But I think we need ta adress your structure first before moving on to displaying information in the portals.

           

          This file is a really nice example of a basic invoice structure:
          http://fmforums.com/forum/topic/63425-auto-fill-one-field-with-text-from-two-fields/#entry300150

          • 2. Re: Create a query from specific data in multiple tables
            tracylynn1212

            Thank you for your response. I will take a look at the Invoice feature as that may be an alternative to what I'm trying to accomplish.

            However, I didn't go into too much detail about the Tables in my previous post. Maybe it will make more sense after elaborating.

            Table 1 is our main table that contains all of our Client's information. This table has over 100 fields such as Client Name, Address, Phone Number, Email, Web Address, etc, etc as well as a Status drop down field (Open, Closed, Pending, etc). Table 2 (the Products table) has a relationship with Table 1 (the Client table) because each record/Client in Table 1 could have multiple Products. Therefore a Table 2 portal was added to the Table 1 layout so that we can add multiple products to the individual record as needed.

            Table 2 (Products table) has multiple fields such as Product Type, Serial #, Lease Amount, Cash Amount, etc etc that lists specifics for that individual product that the individual Client has applied to their account. If we add additional products to that individual client, then the data in the Product table's fields could vary depending on the circumstances (ex: Client ABC has previously purchased Product A for $500 and is now purchasing Product B for $100). The Product portal list on each client record keeps a running list of all purchases the particular Client/record has made from past to present.

            In FMP Pro 12, the relationship is set as: Table 1 ID = Table 2 ID and "Allow creation of records in this table.." is checked for both Table 1 and Table 2 and "Delete related records in this table when a record is deleted in the other table" is checked for Table 2 only (since if we delete a Client record from Table 1, we don't need its corresponding Table 2 data anymore).

            In Microsoft Access, I was able to take make a Query by combining the data from Table 1 with Table 2 (since they have a relationship) and setting criteria where the Query will only show records based on: The Client status field value is "Open" or "Pending" (from Table 1), the Cash Amount field from Table 2 "is not null" and the Payment Recieved check box from Table 2 is set to "No" (meaning the check box was not checked). This would bring up list of only Open or Pending Clients, that have a value listed in the Cash Amount field where a payment was not made yet due to the Payment Received checkbox not being checked.

            I will still look into Invoicing, but would like to know if anyone has any thoughts on an alternative.

             

            Thank you!

             

            • 3. Re: Create a query from specific data in multiple tables
              philmodjunk

              These tables have a relationship with eachother.

              It would be helpful to know the details of that relationship.

              I'd have a relationship like this:

              Table1::ClientID = Table2::ClientID

              or possibly a chain of related tables due the need for invoice and lineitem tables:

              clients::ClientID = Invoices::ClientID
              Invoices::InvoiceID = LineItems::INvoiceID

              You've asked for a portal listing clients. That requires a layout based on a different table occurrence than your existing clients table. What other data outside of the portal do you need to show? (It might help to visualize a Portal as being Filemaker's version of a "subForm".)

              I suspect that you can get what you need pretty easily with an extra table occurrence or two and the right portal filter.

              • 4. Re: Create a query from specific data in multiple tables
                GuyStevens

                The simple explanation is that in Filemaker in stead of setting criteria you create relationships. And the portal shows the records it can see according to the relationship. You can create extra table occurences of a table just to create different relationships.

                If you want to see a client layout with a portal to show this client's invoices you would have the following structure:

                ClientTable
                Id  -  A number field with an auto enter serial number
                Name
                Adress
                ...

                InvoiceTable
                Id  -  A number field with an auto enter serial number
                ClientIdFk  -  A number field that holds the client ID - Also called the Foreign Key
                InvoiceDate
                PayementStatus
                ...

                Your relationship would be:
                ClientTable::Id-------<InvoiceTable::ClientIdFk

                Now, on a layout based on the client table, you create a portal based on the invoice table.
                And there you'll see all the invoices of that client.
                You see all the fields where the Id of the client table is equal to the Id in the ClientIdFk field in the Invoice table.

                If you want to restrict the portal further. Say to show only unpayed invoices you need to add a relationship.

                One where the value is the same in the Invoice table ass well as the client table.

                If we take Payement Status, the values could be "Paid" or "Unpaid"

                So we need to set a field in the customer table that also has this value of either "Paid" or "Unpaid"

                A good idea would be a text field with one of the two values. called "Paid_Unpaid"

                A better idea would be to put this field on your layout and use a radio button set to be able to switch between "Paid" or "Unpaid".

                That way you can choose what you see in your portal.

                So you add this relationship:

                ClientTable::Paid_Unpaid----------[=]-------------InvoiceTable::PayementStatus

                That way, in the portal you'll see the invoices that belong to this specific client, that also have the same payement status as the "Paid_Unpaid" field.

                I hope this makes sense.

                 

                That said, I think you have a problem with your relationship. Both tables should have their own ID fields.

                And table 2 needs a second Id field to store the Id of the record from Table 1 it's linked to. (foreign Id)

                • 5. Re: Create a query from specific data in multiple tables
                  GuyStevens

                  And I always forget, because I always use relationships. But as PhilModJunk says, you could also use portal filters to restrict data like for instance paid or unpaid invoices.

                  • 6. Re: Create a query from specific data in multiple tables
                    philmodjunk

                    And if the list of records you are trying to bring up in the portal is the only data you want on this layout, it may be far simpler to perform a find than use relationships, portal filters, etc to control which clients appear in your list.

                    A key difference between Access and Filemaker is that forms/reports in access are set up with a specific SQL query to produce the recordset used with that form or report. In FileMaker, the layout refers to a specific table occurrence (box in Manage | Database | relationships), and through the relationships defined that link to it, to data in other tables.

                    You or any user (unless you take specific and fairly drastic steps to prevent it) can perform a find at any time to modify the foundset of records accessible via that layout. This can be done manually or via scripts. Thus, a foundset is much more dynamic than the SQL query on which a form/report is based. In Access, pulling up a new recordset for a given form or report requires using VBA to modify the SQL, then requery the database. In FileMaker, you can script the equivalent operation or just enter find mode and start entering criteria into fields right on the layout...

                    • 7. Re: Create a query from specific data in multiple tables
                      tracylynn1212

                      My current relationship is this:

                      Table1: ClientID = Table2:ProductID

                      I was thinking the same thing with the Portal, that it's a "SubForm" of a layout, as that's how I have it in Access. Except in a Access you can state the source of the data in your Subform (Portal) to a Query or a Table, and in the case of our existing Database in Access, my subform is sourced to the Query I had mentioned in my previous post (a combination of two tables with criteria met for certain fields).

                      I created a Dashboard layout that has multiple portals on it with each portal having their own unique list of records (list of clients, important notices, tasks, etc). This new portal I want to add to the Dashboard layout is intended to show data from 4 fields from Table 1 (Client table) and 5 fields from Table 2 (Product table).

                       

                      As an example of the fields in the portal, it would look like this:

                      Table1:Field1 | Table1:Field2 | Table1:Client Name(Field3) | Table1:Status(Field4) | Table2:Cash Amount(Field1) | Table2:PaymentReceived(Field2)

                      (due to nature of our business, the true table and field names are replaced with sample names for security reasons)

                       

                      The portal list will not show all client records, but rather only show records if the following criteria is met:

                      "Table1:Status" value is "Open" or "Pending", "Table2:CashAmount" is not null (has a value in the field) and "Table2:PaymentReceved" is not checked (meaning payment has not been received).

                       

                      Do you suggest I create another Table (Table3 let's say) specifically for this portal? If so, how do I combine data from two tables into a third table where it retains the records from the first two (ie: how do I assign Table 3's fields)?

                       

                      Thanks again!!!

                       

                       

                       



                      • 8. Re: Create a query from specific data in multiple tables
                        tracylynn1212

                        Sorry Phil and DaSaint...I did not realize you have posted responses as I was posting mine. I am reading them now. However, please read my most recent post to see if it sheds any light.

                        Thank you both!

                        • 9. Re: Create a query from specific data in multiple tables
                          philmodjunk

                          My current relationship is this:

                          Table1: ClientID = Table2:ProductID

                          That makes no sense. Why would the value in a field named ClientID match the value in a field named ProductID? (In SQL you would be unlikely to define an Inner Join matching fields with two such dissimilar fields either.)

                          You didn't answer this question:

                          You've asked for a portal listing clients. That requires a layout based on a different table occurrence than your existing clients table. What other data outside of the portal do you need to show?

                          It doesn't sound like you need a portal at all here, but rather a list view of a found set where a performed find pulls up the data you want to see. However, sometimes the desired format of your layout, such as placing the list inside a tab control's tab panel, may still require using a portal.

                          Except in a Access you can state the source of the data in your Subform (Portal) to a Query

                          Yep, that's a key difference. You can think of the equivalent SQL for a reference to the table occurrence in Layout Setup | Show Records FRom and also in Portal Setup | Show Related Records From as Select * queries with no "WHERE" clause and (In the case of layouts), no OrderBy clause.

                          Other settings, performed finds, portal filters etc then limit the records in a manner functionally similar to the WHERE and ORDERBY clauses.

                          New Question: On your Dashboard layout, on what table is the layout based? (What table occurrence is listed in Show Records From when you enter layout mode and select layout setup...?) In FileMaker, the relationship between the Layout's table occurrence and the portal's table occurrence are the first step in controlling what records appear in the portal just as "join" clauses are an initial step in a SQL query.

                          • 10. Re: Create a query from specific data in multiple tables
                            tracylynn1212

                            My current relationship is this:

                            Table1: ClientID = Table2:ProductID

                            That makes no sense. Why would the value in a field named ClientID match the value in a field named ProductID? (In SQL you would be unlikely to define an Inner Join matching fields with two such dissimilar fields either.)

                            I see what you are saying, but I may be a bit confused or I may have my database all jacked up! In the actual Tables themselves, Table 1 has a number field named ClientID. Table 2 has a number field named ProductID. Both fields have the option: indexed, Auto-Enter Serial, Can't Modify Auto, Required Value, Numeric Only and generates a new incrementing number per record commit. In the Relationship tab, Table 1 and Table 2 are linked together as "=" relationship, where Table1:ClientID = Table2:ProductID. I had set it this way assuming that this is what links the appropriate product to the appropriate client, rather than a random client. I had wondered about your argument previously, so to double-check, I just now did a quick layout in Table View that includes Client ID field from my Client table (Table1) and product ID field from Product table (Table2) as well as the product type field. I have 2 test client records that I entered that have 3 products that apply to each of them (so total of 6 Product records). In this table view it shows Client ID 1 has Product ID 1 (3 lines of those) and Client ID 2 has Product ID 2 (3 lines of those). So, from what I am seeing, it looks like the right products are matching with the right clients where the Product ID matches its Client's corresponding ID. This is how i had set the relationships in my Access database so that if I pull up just the Products records, let's say, I know what Client each product belongs to based on the product ID that it has for that product record since it will match the Client ID.

                            To clarify, the Client table is the main table in this database and any other table added has to link directly to that specific Client ID as the data entered on the additional tables solely belong to that Client record. I also have a separate Comments table that has the same direct relationship so that I know what comments (which can be numerious) goes to what Client. The ID fields in the other tables were not meant to really increment on their own, but rather be a field to join with the Client ID field.

                            Hope that makes sense!

                            New Question: On your Dashboard layout, on what table is the layout based? (What table occurrence is listed in Show Records From when you enter layout mode and select layout setup...?) In FileMaker, the relationship between the Layout's table occurrence and the portal's table occurrence are the first step in controlling what records appear in the portal just as "join" clauses are an initial step in a SQL query.

                            The new portal will be on a Tab Panel on the Dashboard layout. The layout's Show Records From is set to the "Client" table (Table 1). However, there are several other portals on this Dashboard as well that will each list different things from the Client table (such as pending tasks, pending payments, current alerts, etc). It was intended to be an at-a-glance, one-stop-shop type of window for quick viewing.

                            • 11. Re: Create a query from specific data in multiple tables
                              philmodjunk

                              Sorry, but what you have makes no sense in terms of relational database design.

                              A field defined as an auto-entered serial number is used in FileMaker as the primary key for that table. It is intended to uniquely identify each record in that table. clients::ClientID is identifying each client record. Products::productID is identifying each product record.

                              As specified, A record with client ID = 1 can only be linked to records in products where ProductID is 1. That wouldn't make sense in Access any more than it makes sense here. You wouldn't link the primary key of table 1 to the primary key of table 2 in Access would you? (that would only work if you have a one to one relationship--one record in table 1 matches to exactly 1 record in table 2.)

                              In a typical one to many relationship, you'd define the parent record's primary key field as an auto-entered serial number and then link it to a simple number field in the child table.

                              You need to figure out how you need this relationship to work. I suspect that you need a third table serving as the Join between clients and Products as I would assume that a given client Record can link to many different products and at the same time, a given product record can be linked to any number of clients. (Client 1, bought apples, peaches and pears. Client 2 bought peaches, pears and strawberries...)

                              The layout's Show Records From is set to the "Client" table

                              Any portal you add to this layout must be based on a different table occurrence than "Client". It can be a different occurrence of client, but it can't be client. That's because the relationship between two ocurrences in Manage | database | Relationships define the "joins" needed to control the basic set of records that will appear in the portal. This doesn't mean that you can't get a list of every record in Clients in a portal on this layout and then use a filter to reduce the records shown to only those meeting specific criteria, it just means that you have to use the right combination of relationships and table occurrences.

                              You may find this tutorial on table occurrences helpful in understanding how they are used in Filemaker: Tutorial: What are Table Occurrences?

                              • 12. Re: Create a query from specific data in multiple tables
                                tracylynn1212

                                You need to figure out how you need this relationship to work. I suspect that you need a third table serving as the Join between clients and Products as I would assume that a given client Record can link to many different products and at the same time, a given product record can be linked to any number of clients. (Client 1, bought apples, peaches and pears. Client 2 bought peaches, pears and strawberries...)

                                How would I go about creating a 3rd table that contains the fields, and their data, from the Clients table and the Products table? That would seem to be logical choice so that I can source the portal to that 3rd table.

                                Additionaly, I went ahead and changed my ProductID in Table 2 to "Client ID" but as a number field, not a serial number, as previously suggested. I do not have anything checked in the Options on that field except for "Prohibit modification of value during data entry". So now the new relationship is Client:Client ID = Product:Client ID. I created a sample layout containing some fields from Client table and some from Product table and all the products show and corresponds with the right client. Seems to work. I just need to make this happen on the portal!

                                 

                                • 13. Re: Create a query from specific data in multiple tables
                                  philmodjunk

                                  How would I go about creating a 3rd table that contains the fields, and their data, from the Clients table and the Products table? That would seem to be logical choice so that I can source the portal to that 3rd table.

                                  Now we're getting somewhere. That's exactly the right question to ask!

                                  First, for your existing set up just to see the portal in action.

                                  Since your layout refers to Client, add a portal to Products to the layout. Put fields from Products in the portal. Include the Products::ClientID field.

                                  In browse mode, click through different client records on your layout by clicking the book control or dragging the slider below it. You should see records from Products appearing in your portal whenever you are on a client record that has records with the same value in ClientID. If you want, you can then add a portal filter to reduce the records shown to only those matching the additional criteria of your portal. (This is a little bit like adding a "WHERE" clause..)

                                  Left ( Products::ProductDescription ; 1 ) = "A"

                                  for example would filter out any related records that don't have a product description starting with the letter A. If you research text functions in FileMaker, you'll find a pretty rich assortment of text functions that can be used here.

                                  You can also return to Manage | database | Relationships and double click the relationship line, then change the relationship operator from = to X, the cartesian join operator. With this operator selected, any record in Clients now matches to all records in Products.  With this relationship any constraints you set on what records appear in the portal rely soley on any portal filter you define for it.

                                  And it's possible to set up portal filter expressions that refer to global fields or variables so that the user can modify the filter criteria to see different subsets of records.

                                  • 14. Re: Create a query from specific data in multiple tables
                                    philmodjunk

                                    Now back to your first question.

                                    What we are now discussing is called a many to many relationship. In Filemaker there are actually two ways to implement that. The method we will discuss here is the standard "Join" table method common to most relational databases. It's also possible to dispense with the join table by using a text field filled with a list of return separated values, but that method is much less flexible.

                                    In manage | database | Relationships, you'd set up your table occurrences and relationships like this:

                                    Clients----<Client_Product>-----Products

                                    Client::ClientID = Client_Product::ClientID
                                    Products::ProductID = Client_Product::ProductID

                                    You can place a portal to Client_Product on a Clients based layout to list all products linked to that client record. You can include fields from Products in this portal to fill in the details about those products. This way you can include a description field, price, etc. You can format the Client_Product::ProductID field as a drop down list or pop up menu that displays Product ID's and Descriptions from the Products table, but which enters the Product ID. If you then enable "Allow Creation of Records Via This Relationship" for Client_Product in the Clients to Client_Product relationship, you can then create new records in Client_Product simply by selecting a ProductID from this value list.

                                    In similar fashion, a portal to Client_Product can be placed on a Products layout to list and link clients to the current Products record.

                                    Here's a demo file that links "events" to "contact" in this fashion. What I have just described is implemented on the "basic setup" layout while additional layouts demonstrate various enhancements to thet basic approach.

                                    https://www.dropbox.com/s/oyir7cs0yxmbn6i/ManyToManywDemoWExtras.fp7

                                    1 2 Previous Next