1 2 3 Previous Next 38 Replies Latest reply on Dec 13, 2013 8:29 AM by philmodjunk

    Multiple tables and relationships

    Daniel

      Title

      Multiple tables and relationships

      Post

           FM pro 12

           I am working on a database for my dog training club. I have owners, dogs and classes which I hope is all fine

           I also want to keep track of payments per term (10 weeks) which is not the same for each class. So a dog has an owner, the dog is in one or more classes. A number of classes will start a term on the same date and I would like to have an overview and permanent record  of which dog/owner was in that class in that particular term and how much and how they paid.

           Below is a picture of what I have so far , I suspect I might need another "intermediate or join "table but am a bit puzzled where and what to put into this.  I am stuck with the payment line items table that shows up blanks instead of a list of dogs & owners per term.

           Can anyone recommend a link that will help me out with this? I have watched several tutorials but none seem to cover this.

            

           Many thanks in advance

            

           Daniel

      Screen_Shot_2013-11-16_at_14.59.11.png

        • 1. Re: Multiple tables and relationships
          philmodjunk

               Since a Dog can be linked to more than one class, I'd link Dogs directly to your Join table instead of to the payments table.

               A portal to the join table could then be placed on your Dogs layout and you would select both a class and a term from drop down fields in order to link that join table record to data in both of those fields. Once you have selected values in those two fields, fields from terms and classes, if included in the portal row, will show data from those tables.

          • 2. Re: Multiple tables and relationships
            Daniel

                 Thanks PhilModJunk for responding so quickly. Perhaps I haven't elaborated enough, what i would like to do is that once a new term starts I select the classes for that term which then should give me all the dogs that are in the respective classes.

                 Correct me if I am wrong as I am quite new to this ,but if I do a portal on the dogs field as you suggested wouldn't I need to go through each dog for each new term which is quite a bit of work?

                 Basically as soon as a term has ended I would like to select the classes that need to pay again which in turn then gives me  all dogs and owners that need to pay for that term.

                 Although the dog can be in more than one class I haven't been able to solve this and basically copied (or cloned) the dog for a second class.

                 Daniel

                  

                  

            • 3. Re: Multiple tables and relationships
              philmodjunk
                   

                        what i would like to do is that once a new term starts I select the classes for that term which then should give me all the dogs that are in the respective classes.

                   Which would be best done on a list view layout based on the join table. You can perform a find for all records of the desired term and then sort them by class. A sub summary layout can serve as a "sub heading" to show the start of each set of records for a given class for the specified term.

                   

                        but if I do a portal on the dogs field as you suggested wouldn't I need to go through each dog for each new term which is quite a bit of work?

                   You would not. That would not be the purpose of such a portal. It would be one way to enroll a dog in a class and a way to see all the classes that a given dog has been enrolled in.

                   

                        Although the dog can be in more than one class I haven't been able to solve this and basically copied (or cloned) the dog for a second class.

                   Proper use of your Join table will eliminate the need to do that.

              • 4. Re: Multiple tables and relationships
                Daniel

                     Thanks again, I'll go and have a try at that.

                • 5. Re: Multiple tables and relationships
                  Daniel

                       I hope someone can help me out a bit further as I keep getting stuck. I am trying to have 2 portals in 1 layout, the 1st one is all the classes in a term (hooray this works) the second portal gives me a selection of dogs based on the classes I have selected in the 1st portal and then add records in the payment line items at which point I am stuck. I cannot see how to create records in the portal to PaymentLineItems

                  • 6. Re: Multiple tables and relationships
                    Daniel

                         To clarify the current structure of the database

                    • 7. Re: Multiple tables and relationships
                      philmodjunk

                           I can see good progress towards an Anchor Buoy type system of relationships.

                           I would guess that the last Table Occurrence Group (TOG) is the one you intend to use as the basis for your layout and two portals. Is this correct?

                      • 8. Re: Multiple tables and relationships
                        Daniel

                             Thank you, yes I came across that and made more sense.  Guy Stevens' YouTube channel helped here a lot.

                             Yes if you mean the Terms table. Thanks to your earlier suggestion it dawned upon me that I needed to use the join table for the relationship and that works now. However I am stuck at creating a new record in the PaymentLineItem. If I create one I can change that and get the correct list of dogs but am not sure how to create a new record through two Table Occurences. Also if I now select another class on the term layout it doesn't change. Back to the drawing board.

                              

                        • 9. Re: Multiple tables and relationships
                          philmodjunk

                               Your second screen shot is very different from the first. I was looking for details not present in the first that I can find in the second.

                               But first a "business rules" question: Are all payments, payment in full, for just one dog for one class? Could a person show up and make a single payment for two or more dogs and/or two or more classes? Do you accept partial payments or get payments by mail or other method where someone might pay too much or not enough?

                               Where this question is headed is that often, businesses receive a single payment that has to be used to payoff more than one outstanding bill and sometimes also permit (or receive due to error) partial payment of bills. This can require setting up a table where you have one record for each payment received with a join table linking the payment record to possibly many different invoices (or classes in your case) with a field in the join table that records that portion of the total payment to be applied to that class.

                               On the other hand, you may need a script that produces "billing records" for each enrolled dog in a current class so that you can present each dog owner with an itemized list of what they owe in addition to the above set up for managing payments received in a payment  register.

                                

                          • 10. Re: Multiple tables and relationships
                            Daniel
                                 

                                      Your second screen shot is very different from the first. I was looking for details not present in the first that I can find in the second.\

                                 That sounds like a step in the right direction? 

                                  

                                 

                                      But first a "business rules" question: Are all payments, payment in full, for just one dog for one class? Could a person show up and make a single payment for two or more dogs and/or two or more classes? Do you accept partial payments or get payments by mail or other method where someone might pay too much or not enough?

                                 In principle this can all happen, people pay per term per dog so a 10 week class (i.e. 1 term) is at the moment £50. All payments are in full, some people pay for two dogs at the same time but I am happy to process these as 2 separate payments. If people don't pay enough or pay too much I do not necessary want to register this. It happens seldom and I just make a note somewhere until it's corrected one way or the other.

                                 

                                      Where this question is headed is that often, businesses receive a single payment that has to be used to payoff more than one outstanding bill and sometimes also permit (or receive due to error) partial payment of bills. This can require setting up a table where you have one record for each payment received with a join table linking the payment record to possibly many different invoices (or classes in your case) with a field in the join table that records that portion of the total payment to be applied to that class.

                                 I can understand that but I'm a one man business  and don't have masses of payments, I just want an overview who needs to pay for a term and who has paid what so I can show the taxman what the income in a period and I don't miss any payments

                                 

                                      On the other hand, you may need a script that produces "billing records" for each enrolled dog in a current class so that you can present each dog owner with an itemized list of what they owe in addition to the above set up for managing payments received in a payment  register.

                                 I think I would need that if it was a bit more complex business but it's not. At the end of a 10 week term I tell the classes they need to pay verbally and by email and they pay me in full. The only exception is when people join part into a term then they pay obviously less but I can manually adjust the amount they paid, payment is cash, cheque or direct bank transfer.

                                 In my ideal world I would create a new term, add the classes that below to that term (not all classes are in sync) and then generate records of people who need to pay for that term in my PaymentLineItems, but that would most likely involve complex (to me) scripting?. Currently I am working on adding the PaymentLineItem records through the portal from the Terms form but that is not working as expected.

                                 Thanks again for your time in looking at this, it's much appreciated.

                                  

                            • 11. Re: Multiple tables and relationships
                              Daniel

                                   hopefully this is a step in the right direction? Added a calculate field in the Terms table that gets the ID of the selected Join_Classes_Terms record. But still no luck with getting the dogs in the 2nd portal on the Terms table.

                                    

                                   Should I create the 2nd portal on the Join_Dog_Classes table or on the PaymentLineItems? I want to create records in the PaymentLineItems and when doing so copy information from Join_Dogs_Clases & Dogs_Payment & Owners_Payment into these records. 

                                   I am also wondering if I need to keep track of more ID's when doing so?

                              • 12. Re: Multiple tables and relationships
                                Daniel

                                     I'm working from this at the moment and it seems to work, some more testing needed and to find how to automatically copy data into the PaymentLineItems records as soon as I click in the portal. The Terms portal is set onto the Join_Dog_Classes occurence and fields added from PaymentLineItems.

                                • 13. Re: Multiple tables and relationships
                                  philmodjunk

                                       Sorry for not getting back to you sooner. So to sum up recent comments back and forth, you want a list of each dog scheduled for one of the classes for the term and need to record a payment when such is made?

                                       From your layout based on Terms, a portal to either Join_Classes_Terms or Classes_Terms can be used to list all classes for the term. A Portal to the join table makes it possible to add/remove classes from the term so that is usually the option that I recommend and you've indicated that you have this working.

                                       For recording payments, you might try setting up a portal to Join_Dogs_Classes. If you stay with your current design and are OK with having only a single payment record for each dog for each class in which they are enrolled, you can include fields from PaymentsLineItems in this portal's row. Just be sure to enable "allow creation..." in the relationship between the join table and PaymentsLineItems. But if you are sticking with a single payment record for each dog/class, then you could also define the fields that you currently have in PaymentsLineItems as fields in the join table and not have an additional table at all.

                                  • 14. Re: Multiple tables and relationships
                                    Daniel
                                         

                                              Sorry for not getting back to you sooner. So to sum up recent comments back and forth, you want a list of each dog scheduled for one of the classes for the term and need to record a payment when such is made?

                                         Not quite a dog can be in more than 1 class, but otherwise yes. Also the next term the dog is usually in the same class again. 

                                         

                                              From your layout based on Terms, a portal to either Join_Classes_Terms or Classes_Terms can be used to list all classes for the term. A Portal to the join table makes it possible to add/remove classes from the term so that is usually the option that I recommend and you've indicated that you have this working.

                                         Correct that is working fine

                                         

                                              For recording payments, you might try setting up a portal to Join_Dogs_Classes. If you stay with your current design and are OK with having only a single payment record for each dog for each class in which they are enrolled, you can include fields from PaymentsLineItems in this portal's row. Just be sure to enable "allow creation..." in the relationship between the join table and PaymentsLineItems. But if you are sticking with a single payment record for each dog/class, then you could also define the fields that you currently have in PaymentsLineItems as fields in the join table and not have an additional table at all.

                                         Yes I have worked the first part out in the last relationship diagram as per my  previous comment. That works fine until I add a class to a second term it then automatically picks up the payments from the 1st time the class was in a term. I've tried filtering the portal records but then I cannot add records. Bit stuck again. As for combining the PaymentLineItems with the dogs I think that might not work as a dog is usually in the same class in the next term, I only now realise that that is not obvious for others, apologies.

                                          

                                    1 2 3 Previous Next