5 Replies Latest reply on May 20, 2014 10:45 AM by SlicChic

    Conditional Value List from Multiple Tables??

    SlicChic

      Title

      Conditional Value List from Multiple Tables??

      Post

           I'm looking for a way to have sales department information show up on payments records.  Our db is based on students, and our tables are set  up as such:

           Students ---<Enrolments

           Students ---<Books

           Students ---<Payments

           Both Enrolments and Books have the sales department codes.  What I want is to create a record in Payments, and be able to choose the enrolment or book that this payment is for, which would then bring in the correct sales department codes.  

           I've been exploring conditional value lists for this, but can't seem to have a way of choosing data from 2 different tables... Would a script be a better option?  Or am I missing something?

        • 1. Re: Conditional Value List from Multiple Tables??
          philmodjunk

               If Enrollments and Books are two different items a student may purchase, why use separate tables? A business that sells products and services uses a single table to list each sale,whether the item sold is a physical product or a service. Seems like the same set up would work here. You may have additional information unique to enrollments or books not recorded for both types of items sold, but this can be handled either with fields that are only populated with data for that item or by linking in your current tables as "detail" tables.

               Your relationships might work like this:

               Students-----<Sales>----Enrollments
                                         v
                                         |
                                    Books

               Your sales code and purchase amounts would be logged in Sales and now your conditional value list can draw values from a unified Sales table.

          • 2. Re: Conditional Value List from Multiple Tables??
            SlicChic

                 Didn't think to set it up like that, since our enrolments have a lot more involved with them (ie. limited class size), and the books are more of an add-on. On the other side of the Enrolments is Classes, which is where we are putting the sales codes.  This is because the type of class determines what sales department it's a part of. 

                 Are you suggesting to have Payments be apart of this new Sales table?  One thing I'm trying to wrap my brain around is that families can have multiple payments for 1 enrolment.  And I want to remove some of the 'thinking' out of making a payment record.  Am I understanding correctly that with the Sales table, containing the sales code, we would make a record, and then choose which sales department it's a part of from the conditional value list?  

            • 3. Re: Conditional Value List from Multiple Tables??
              SlicChic

                   Or would everything look like this?


                                    Payments
                                         \/
                                          |
                   Students----<Sales>----Enrolments
                                          \/
                                           |
                                        Books

                   The purchase of books or the enrolment would be done on the Sales table, and then when a payment is made we could make the payment and choose which sale it is for, from a value list?

              • 4. Re: Conditional Value List from Multiple Tables??
                philmodjunk

                     Payments would still be a separate table and you might even need a join table between sales and Payments if you have any situations where a payment received would pay off more than one Sales record.

                • 5. Re: Conditional Value List from Multiple Tables??
                  SlicChic

                       Thank you Phil!  I've been playing around with this, and it seems to be what we need.  It'll take some work, as we have to change some of the architecture of the db, but is getting the results I need.  Thank you!