1 Reply Latest reply on Apr 15, 2010 1:53 PM by philmodjunk

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

    maxrivers

      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?

        • 1. Re: Lookup changing data in original table, instead of running calculation on current table
          philmodjunk

          It sounds like you have more than one problem here. It would help if you more fully described your tables and how you've related them in your database.

           

          I think you have taken the type of service  field from your employee field, placed it on a layout that refers to your schedule table and set it up with your value list. If your relationship matches THe Employee::EID field to an EID field in your schedules table, selecting a value in this field is actually editing the first matching record in your employee table.

           

          I think you'll also need a second relationship that matches both the EID and the selected service to the corresponding fields in your employee field so that it will correctly look up the fee.