AnsweredAssumed Answered

Relational DB and calculation

Question asked by VincentYoung on Mar 13, 2011
Latest reply on Mar 14, 2011 by philmodjunk


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