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.
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)?
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.
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.
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.
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?
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.
Are you then saying that the second part of my scenario, using two vouchers for the same appointment is never possible?
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.
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.
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.
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.
Hi Gaosheng Huang,
i just read the full thread and based on TSGal solution, you could set it like this,
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!