1 Reply Latest reply on Mar 14, 2011 9:17 AM by philmodjunk

    Relational DB and calculation



      Relational DB and calculation


      I built a DB solution on FM6 and now I am upgrading it, Somehow I was able to do this then, but with the new and slightly different fuctionality, I can not seem to t this to work. I have a billing DB. I have 3 fields that enter anesthesia procedures when they are preformed. I typically never do more than three of these procedures in any one case, so the three fields are AnesthesiaProcedures1, AnesthesiaProcedures2, AnesthesiaProcedures3.  I have a relational DB called you guessed it, Anesthesia Procedures with 3 fields, Procedure, Code, Charge.   So here is the problem. I use the same single DB (anesthesiaProcedures) with the 3 fields and relate it to all 3 of the AnesthesiaProcedures (1,2,3) in the related BillingDB. I have each field set up as a drop down list using the BillingProcedure::AnesthesiaProceduresProcedure relation. this gives me a drop down of the 10 or so procedures. So for example, AnesthesiaProcedure1 is Arterial line with charge of 100, AP2 is central Line with charge 200. I dont show the related charge on the layout, just the procedure.  Now when I want to use all the information, I have a total charge for that entire anesthetic. One part of that charge is the total of the anesthesia procedures.  So I have a field called Anesthesia procedure total charge. Basically it is a sum of the 3 AnesthesiaProcedures. Previously, I was able to bring up the related Charge field for each of the 3 AP fields. So my old calc field looked like this 

      unstored calculation = Sum(AnesthesiaProcedure1::charge,AnesthesiaProcedure2::charge,AnesthesiaProcedure3::charge)

      My problem is that with this new version 11, I can not seem to pull up the related charge field for the calculation.  I dont think I should need create 3 seperate Anesthesia procedure databases and have charge 1,2,3. Again I dont have a charge field to relate to the AnesthesiaProcedures charge field, but for the calculation, I think I should be able to pull up the related charge for the procedure chosen in the related AnesthesiaProcedure  procedure field.

      I am banging my head and computer over this.

      Thanks for any help. (i have tried rerelating, changing this or that, etc, cant seem to get the right way.)


        • 1. Re: Relational DB and calculation

          In either 6 or 11 you'd need three relationships, one for each procedure field. How you set up the three relationships is quite different. In 11, you open Manage | database | Relationships, click on the 'box' (called a table occurrence) for your Anasthesia Procedures to select it, then click the button with two green plus signs on lower left twice to create two new table occurrences. These all refer to the same table, but now you can link each of your procedure fields to a different table occurrence so that you can define the appropriate cost look ups for each.

          A simpler structure, is to add a table of such procedures so that you can list these procecures in a portal on your billing layout. This would be much like a typical invoicing system where your Billing table correponds to an Invoices table, the procedures performed to a line items table and the costs look up table to a products table. Such a structure not only allows more than 3 such procedures (you never know, you just might need that for some unusual situation), but also for a much simpler set of relationships and calculations.

          Here's a very simple invoicing demo, created by Comment, that you can download and examine to see how this might be set up: