AnsweredAssumed Answered

Lookup changing data in original table, instead of running calculation on current table

Question asked by maxrivers on Apr 15, 2010
Latest reply on Apr 15, 2010 by philmodjunk

Title

Lookup changing data in original table, instead of running calculation on current table

Post

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).

Pertainent Fields:

    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?

Outcomes