1 2 Previous Next 15 Replies Latest reply on May 28, 2015 11:28 AM by philmodjunk

    How to apply a second TO in a parallel manner

    GaoshengHuang

      Title

      How to apply a second TO in a parallel manner

      Post

      Dear all,

      I am working on a database that captures transaction appointments for a family business. I have a Voucher table, which records discounted vouchers prepaid by some willing customers, on top of a Staff(who services customers) table, Customer table, Appointments table. So for each visit, the amount applied (VAmountApplied) is put into the Appointment table, and then added to the TotalAmountedUsed and meantime deducted from balance in Voucher table.

      This seems to be working until I come across another situation: a customer may bring their family or friends and want to apply his/her voucher. I tried creating a second TO of Voucher and related it to Appointments or Customers, but neither could get the balance correct. 

      Did I do something wrong with the calculation? OR is there a better arrangement? 

      I would appreciate your suggestions! 

      Capture.PNG

        • 1. Re: How to apply a second TO in a parallel manner
          philmodjunk

          But exactly how did you try relating that added TO to appointments or customers? by what match fields?

          I think that's where you had some trouble. In a sense, this scenario means that you have more than one customer for the same appointment. If there are a flexible number of such additional "customers" (seen as such since their vouchers are being used in the transaction), it would seem that you need a join table between appointments and customers.

          • 2. Re: How to apply a second TO in a parallel manner
            GaoshengHuang

            Hi Phil,

            Thanks for looking into this! I tried two ways. One is to treat the person WITH a voucher as a relative by connecing the RelativeID with Customer ID (using a pull-down list from Customer ID) (Figure 1). The other is to relate Voucher directly to Appointments (Figure 2). I like the first one because I can further indicate the connection in case both of them come at the same time and request a couple service.

            However, the problem with both arrangements is that I cannot update the Voucher table correctly. Supposedly, whenever I apply a certain amount from either the customer's voucher, or from a friend/reltave's councher, the TotalAmountUsed and Balance in Voucher table should updated but it is not. Maybe it is because of my definition of TotalVoucherUsed: it is a summary field for VAmountApplied (in Appoitment table)?

            • 3. Re: How to apply a second TO in a parallel manner
              philmodjunk

              How are you currently updating the "totalAmountUsed"? Is this a calculation field or do you use a script to update that total?

              I see possible problems with all of the options--including your original version if TotalAmountUsed is a calculation field.

              • 4. Re: How to apply a second TO in a parallel manner
                GaoshengHuang

                I used the TotalAmountUsed as a summary field based on a portal in Voucher TO (the portal puts together all the Appointments using this voucher). I can also sense the problem with all these options because the field can only be evaluated either from Voucher TO, or from Voucher2 TO but my options require it to work on both. But I cannot figure out a way to solve it.

                • 5. Re: How to apply a second TO in a parallel manner
                  GaoshengHuang

                  Actually I first created a summary field TotalAmountUsed in Appointments table, and then created another TotalAmountUsed in Vouchers table, but it is a calculation field: GetSummary ( Appointments::TotalAmountUsed; VoucherID). The problem lies here: I have to choose: Evaluate This Calculation from the Context of ..... That means I cannot update the voucher balance from both TOs. 

                  • 6. Re: How to apply a second TO in a parallel manner
                    philmodjunk

                    Here's the scenario that I think sums up the main problem ( and it can be resolved). Let me know if this is possible.

                    Customer A makes an appointment and uses 33% of a voucher for that appointment. Customer B, a relative of Customer A comes in and Customer A's voucher is used for it as well, using up another percentage or the  remaining balance of it for this appointment.

                    That sound like a possibility?

                    And if Customer A's voucher does not fully cover the cost of the appointment, might Customer B use part of his voucher on the same appointment?

                    • 7. Re: How to apply a second TO in a parallel manner
                      GaoshengHuang

                      Phil,

                      Yes, the first part of the scenario you suggested is exactly what happens. So in reality, my situation is even simpler:  if Customer A's voucher does not fully cover the cost of the appointment, he/she just pays the remaining with cash or credit card. If you could resolve the first part of the scenario, I would really appreciate it.

                      • 8. Re: How to apply a second TO in a parallel manner
                        philmodjunk

                        Are you then saying that the second part of my scenario, using two vouchers for the same appointment is never possible?

                        • 9. Re: How to apply a second TO in a parallel manner
                          GaoshengHuang

                          So far it has not happened. But if you could integrate that part, that would be even better. If this requires significant efforts on top of the first part, we could ignore it.

                          • 10. Re: How to apply a second TO in a parallel manner
                            philmodjunk

                            Good systems allow you to handle the rare and unlikely events as well as business as usual. This is really a business decision as to whether you will allow two or more vouchers to pay part of the cost for the same appointment.

                            From a database standpoint, it's the difference between a many to many relationship, one voucher links to more than one appointment and one appointment can link to more than one voucher or a one to many relationship. One voucher can link to more than one appointment, but one appointment can never link to more than one voucher.

                            • 11. Re: How to apply a second TO in a parallel manner
                              GaoshengHuang

                              Phil, 

                              Thanks a lot for your suggestion. I talked with the company; it is just one voucher links to many one appointment and one appointment links to one voucher only. Could you please suggest a schema? I still cannot get the right TO and their relations to get this result. 

                              • 12. Re: How to apply a second TO in a parallel manner
                                TSGal

                                Gaosheng Huang:

                                If a voucher can link to many appointments, but an appointment can only link to one voucher, then you have a many-to-one relationship.  In the Appointment table, create a fk_VoucherID field, and have it link to the Voucher table.  The "fk" is short for "foreign key", since it does not relate to the primary key of the Appointment table.  This will allow you to link multiple appointments to the same voucher.

                                TSGal
                                FileMaker, Inc.

                                • 13. Re: How to apply a second TO in a parallel manner
                                  starstuff

                                  Hi Gaosheng Huang,

                                  i just read the full thread and based on TSGal solution, you could set it like this,

                                  [Staff]

                                  pk_staff

                                  [Appointment]

                                  pk_appointment

                                  fk_staff

                                  fk_voucher

                                  [Voucher]

                                  pk_voucher

                                  fk_customer

                                  [Customer]

                                  pk_customer

                                   

                                   

                                  • 14. Re: How to apply a second TO in a parallel manner
                                    GaoshengHuang

                                    Thanks to both TSGal and star stuff (as well as Phil)! I think this works. Since a customer may use a voucher from somebody else, I will have to use a second TO of Customers table related to Appointments table directly. Is that correct? I still have trouble with the concept of TO. It looks difficult to conduct calculations on different TOs.

                                    I will try out this solution. Thanks again!

                                    1 2 Previous Next