3 Replies Latest reply on Jan 2, 2014 6:11 AM by philmodjunk

    Using joins

    cinderjack

      Title

      Using joins

      Post

           I'm trying to figure out the best way to configure my new Filemaker installation.  I have tables called ACCOUNT (pkAccountID), CLIENT (pkClientID,fkAccountID), VISIT (pkVisitID, flClientID), CHARGES (pkChargesID, fkVisitID, fkInventoryID), PAYMENTS (pkPaymentID, fkAccountID).

           I'd like to have a tab for Visits (client information would be included on this screen including a calculation showing their balance (charges less payments), and a tab for Charges (I will often have multiple charge per visit).  If I pick Visit as the table to build the layout on then I don't get anything in my inventory field.  

           Just trying to figure out the best way to get all this set up.  

        • 1. Re: Using joins
          philmodjunk

               From the pk and fk fields you have defined, it appears that you have this set of relationships:

               Payments>------Account---<Client-----<Visit-----<Charges>-----Inventory

               If you base your layout on Visit, you can place a portal to Charges on this layout to list the charges linked to that visit. Charges::fkinventoryID should then be set up as a value list for selecting items from the inventory table you don't list as a table here.

               In fact,

               Client----<Visit---<Charges>-----Inventory

               Is really the same structure found in the Invoices starter solution though the table occurrences have different names. The FileMaker 13 invoices starter solution, for example, has:

               Customers----<Invoices---<Invoice Data>-----Products

               So you may want to examine the Invoices starter solution for ideas on how you might set this up.

          • 2. Re: Using joins
            cinderjack

                 Making great progress!  Now, what I'd like to do in this portal is pick the herb brand and then have the ItemName pop-up only display items that match that particular brand...how might I configure that?  (See attached portal)

            • 3. Re: Using joins
              philmodjunk

                   You are describing a conditional value list. Here are my links on the subject:

                   There's a lot of overlap between the first two links so you can read one and skim the other. The demo file gives you several examples of different conditional value lists so you can examine them to see how they are set up. If you are using FileMaker 12 or later, you can open this demo file from your File menu to get a converted copy of the demo file that you can examine in your version of FileMaker.

                   The next to last link discusses how to set up a chain of conditional value lists where the value selected in each conditional value list controls the values listed in the next value list.

                   My most recent contribution is how to use ExecuteSQL to set up conditional value lists that aren't readily possible with the other methods shown here.

                   Forum Tutorial: Custom Value List?

                   Knowledgebase article: http://help.filemaker.com/app/answers/detail/a_id/5833/kw/conditional%20value%20list

                   Demo File: https://www.dropbox.com/s/j6qf0z9fnem3uxd/ConditionalValueListDemo.fp7

                   Hierarchical Conditional Value lists: Conditional Value List Question

              New!!! Using ExecuteSQL to produce a conditional value list: Using ExecuteSQL to Produce a Conditional Value List

                   Feel free to post follow up questions here if those links aren't enough to get your value lists working correctly.