14 Replies Latest reply on Nov 11, 2013 10:52 AM by MelissaChase

    Table Relationship Help

    MelissaChase

      Title

      Table Relationship Help

      Post

           I'm having some trouble wrapping my head around how many tables I need. 

           I have a Member database.  Each member can purchase a membership, Gear or private lessons, or pay Misc Fees.  I keep track of those in the Receipts table connecting those through Member # ID.  

           I'd like to keep track of the Memberships (when they expire, and who is the account holder), and Private Lessons (keep track of when each lesson is used) and then gear and fees.  

           I wanted to use a Receipt Number  to track the memberships and lessons, but I'm not sure how to connect these tables to the member - whether I use the member ID, or a receipt no.  

           The idea is to keep track of all the memberships separately, so I know when to send out renewal emails, and also to see all the purchases from the members layout using a portal to receipts. 

           Under the Receipts table, I have a choice for memberships, lessons, gear or fees.  And within the Receipts Table I wanted to issue a unique number (Like Type+Initials+No+date) and use that in other tables to keep track of Membership and also Lessons.  

           So my trouble is, I'm wondering if I should track all types of purchases under the Receipts table, or if I'm better off using more tables, and figuring out how to connect them, so that if I choose a member ID, I see only memberships in the membership table, and lessons in the lesson table, based on what feels to me like "magic", right now.  Any ideas?  I'm good with easy.  It doesn't have to be automated.  

           I'm very grateful for any help or thoughts.

            

            

            

            

            

      Screen_Shot_2013-10-27_at_5.26.16_PM.png

        • 1. Re: Table Relationship Help
          philmodjunk
               

                    I wanted to issue a unique number (Like Type+Initials+No+date) and use that in other tables to keep track of Membership and also Lessons.

               That's an unnecessary complication best avoided. Use an auto-entered serial number as the value to link records by member ID.

               There are two workable approaches that come to mind:

               1) Link each record in receipt to detail tables for Memberships and lessons where you record additional data (date when lesson is used, date when membership expires...) A receipt ID can be yet another serial number field and can link a receipt to a record in one of these tables. The relationship from detail to receipts to Members can be used to identify the person that purchased that lesson or membership.

               2) Add extra fields to Receipts that record this additional info. Leave the fields empty when they are not needed for a given item in Receipts.

               Both methods work, there are pros and cons with either method. In general, if you just need a few extra fields for your memberships and lessons, option 2 may be the simpler approach. But as you find more and more detail info is needed to document them, 1 becomes the better option.

          • 2. Re: Table Relationship Help
            MelissaChase

                 THANK YOU!!!

                 What is the best option if I'd like to use a portal to view all the details of various types of purchases in one member record?  And can 1 primary key (Member #)  be used as a match field in more than 1 table (like I have in my pic) or do I need a different key to sort of chain link them together? The member # is an auto enter serial.

                 I'm thinking I may just add more fields to my Receipt table and have the different types of purchases under tabs in the Receipt layout, so long as I can still do another layout that shows just Memberships, and just private lessons, etc. Am I on the right track?

                  

                 Many many thanks!!!!

                  

            • 3. Re: Table Relationship Help
              philmodjunk

                   As long as you are uniquely identifying a member, a single primary key field in Members can be linked to foreign key match fields in as many other related tables as you need.

                   A Portal to show the purchase details would seem to be a portal to Receipts, placed on your members layout. This would be exactly the same with either method 1 or method 2.

                   You may, however want to set up a table of invoices linked to a related table of line items so that a single transaction that purchases multiple items can be set up as a single record linked to members:

                   Members----<Invoices-----<LineItems

              • 4. Re: Table Relationship Help
                MelissaChase

                     My Line items are 1 of 4 items - Memberships, Lessons, Goods(Gear), and Misc Fees.  So if I link these detail tables to the invoice table, I can still use a portal from members to display all my line items... and so detail tables seem the best option?  

                     And if so, I would use a primary key in the receipts to link to line items?  Do I use a PK to link Members to Receipts, and then use another PK to link Receipts to the different Detail tables?

                     Members-----<Receipts---------<Memberships
                                                               ---------<Lessons
                                                               ---------<Goods/Gear
                                                               ---------<Fees

                     And then use a portal to view all purchases by member by way of Receipt#? And then I can still go to each detail table when I need to send out renewal emails, or restock Gear or check up on unused lessons?

                      

                     I very much appreciate your help on this!!

                      

                      

                      

                • 5. Re: Table Relationship Help
                  philmodjunk

                       Your detail tables would link to the line items table rather than the invoices.

                       With invoices and line items tables, you can use a portal to or a list/table view layout based on Invoices to get a list of all purchase transactions. You'd use a portal to or a layout based on LineItems to get an itemized list of every item purchased--all the items purchased on a single invoice, all the items ever purchased by that member or all that member's items over a specific date range are possible.

                  • 6. Re: Table Relationship Help
                    MelissaChase

                         Sooo, would it be linked like:

                          

                         --<Receipts-----<Lineitems---------<Memberships
                                                                        ---------<Lessons
                                                                        ---------<Goods/Gear
                                                                        ---------<Fees

                         I insert a table to link the receipts and the different detail tables based on Member#?  And this would be by way of a portal on the Receipt table to pull in line items?  I can see the "invoice" start solution in FM11 but I think that's  bit more than I need or maybe that level of detail is what I do need, but at this point, I'm not sure.
                          
                         And given that I may be entering "Over my head" territory, would you recommend just sticking to adding fields to Receipts?   
                          
                          
                    • 7. Re: Table Relationship Help
                      philmodjunk
                           

                                I insert a table to link the receipts and the different detail tables based on Member#?

                           I don't see a need for detail tables for fees, goods and gear. You would also use one more table linked to line items that serves as a combined list of all items that a member might choose to purchase where you have one record for each type of lesson, each fee, each item of gear, memberships etc. This is your "price list" where you can look up the current price charged for each item.

                           You can see examples of this in the Invoices starter solutions that come with FileMaker 11 and 12. The starter solutions, however, will not show the detail tables I have suggested that you might use.

                           

                                And this would be by way of a portal on the Receipt table to pull in line items?

                           Yes.

                           I think there's some confusion here over "detail tables" and the "products" table you'll find in the invoice starter solution. These are not the same table an serve different purposes. The detail tables are where you'd record additional details such as when a membership expires or when a lesson was actually used. The "products" table in invoices lists all items that can be purchased (including memberships, lessons and fees) for purposes of looking up the current price for each and also for tracking inventory levels on physical items such as your "gear".

                           

                                And given that I may be entering "Over my head" territory, would you recommend just sticking to adding fields to Receipts?

                           I can't recommend one way or the other. It depends on how you need this to work. I do think that you need a lineitems table for listing each item purchased but whether or not you should just add fields to the line items records instead of linking in detail tables for that data is not something I can recommend on given the current limited view of what you need to do to support your business.

                            

                      • 8. Re: Table Relationship Help
                        MelissaChase

                             Thank you for all your help.  I realize it's not easy to explain to newbies, and I appreciate all the time you took to explain.  I will look further at the starter solutions and see if I can make this work.  

                              

                             Many many thanks!

                        • 9. Re: Table Relationship Help
                          MelissaChase

                               Hi Phil,

                               I looked further and found I had set the id field up wrong - the tables are all relating, so thank you!  

                               I do have another question - I would like to have the value of one field populate based on the text of another - but jumping one ahead in the value list.

                               For instance - a field named Size with a drop down menu based on a value list with the values " 01 Small, 02 Medium," etc and a field named Next Size Up - would automatically populate with the next size up.  If field 1 had "01 Small", then field 2 would populate "02 Medium".  Can you populate a field on a text based value list?

                               Many thanks for any guidance!

                                

                                

                                

                          • 10. Re: Table Relationship Help
                            philmodjunk

                                 A calculation could be used to extract the next value in your value list or if your values are defined with the "use values from a field" option, a relationship could be set to match to the next larger value.

                                 This calculation--can be used as an auto-enter calculation or in a field of type calculation--can return the next value in the value list:

                                 Let ( [ v = YourValueListFIeldHere ;
                                           VL = ValueListItems ( Get ( FileName ) ; "YourValueListNameHere" ) ;
                                           p = Position ( VL ; v ; 1 ; 1 ) ;
                                          ]
                                           GetValue ( VL ; Patterncount ( Left ( VL  ; p ) ; ¶ ) + 2 )
                                        )

                            • 11. Re: Table Relationship Help
                              MelissaChase

                                   Wow, thank you.

                                   You are awesome!

                                    

                              • 12. Re: Table Relationship Help
                                MelissaChase

                                     Hi Phil,

                                     I am still working on this database, and a new question came up - and I hope I can pose the correct question.  

                                     I'd like to track the "Contract End Date" though only on current contracts.  A Member can have several contracts but I only care about the most recent.  I have two tables - one for Members and one for Membership contracts.  I have a date "Contract End Date" in the Memberships table, and I have a Current Contract End Date in my Members table.  I would like to pull that Date field in from the Memberships table but only if the contract is the current one. 

                                     I was thinking maybe if I had a checkbox for current, I could do some sort of if/then?   

                                     Many thanks for any guidance.

                                      

                                      

                                • 13. Re: Table Relationship Help
                                  philmodjunk

                                       You can set up a relationship to contracts that matches records both by Member ID AND by a pair of additional fields that work to omit any contracts for that member that have expired:

                                       Members::__pkMemberID = Contracts::_fkMemberID AND
                                       Members::cToday < Contracts::Contract End Date

                                       Define cToday as an unstored calculation with: Get ( currentDate ) and select Date as the result type. You can add a new table occurrence of Contracts fo this relationship if you need to keep your current relationship between Members and Contracts unchanged.

                                       Another option is to use the original relationship and use

                                       Get ( CurrentDate ) < Contracts::Contract End Date

                                       In a portal filter.

                                  • 14. Re: Table Relationship Help
                                    MelissaChase

                                         As always, you are awesome and I deeply appreciate your help!