Lookup changing data in original table, instead of running calculation on current table
I'm running FM v10 on XP.
I'm creating an acupuncture clinic database. I have a table called "Services" which has one record for each service an employee provides. Some provide more than one service (e.g. Acupuncture, Massage). This table also has the amount this employee is paid for this service (amounts differ for different employees).
EID = Employee ID
Type of Service = Indexed, By Value List "Service Type" which lists all the different kinds of services
Payment = amount this EID is paid for this Type of Service
On the "Schedule" table, where appointments are put in, there's a drop-down list called SchTypeOfService (Lookup,By Value List called "This Employees Service Types" which Uses values from field: "Services::Type of Service."
This does almost exactly what I want it to do. When you put the EID into the schedule, it shows the employee's name, and the SchTypeOfService drop-down list shows only the services that this employee has records for in the "Services" table. It even calculates their pay correctly (it selects the first record for this employee).
The problem comes in when I try to select the 2nd or Nth entry in the drop-down list. Instead of recalculating payment, that all stays the same, but in the "Services" table, the first record's "Type of Service" field gets changed to the entry selected on the "Schedule" layout.
So if the employee had "Acupunture" and "Massage" as the first two records, and I select "Massage" in the drop-down list on the Schedule layout, the employee now has "Massage" and "Massage" in their first two "Service" Table records.
I'm so close, anyone know what I'm doing wrong?