7 Replies Latest reply on May 23, 2012 8:48 AM by philmodjunk

    Calculation Problem maybe script???

    Immigranta

      Title

      Calculation Problem maybe script???

      Post

      Hi all,

      I have an issue here  and i hope you guys can give me some direction to work with my problem.
      I have two DBs: one for Patients and one for Budget for those Patients. There are 27 patients and there are total of 430 forms. There are located in Patient DB. Each form cost specific amount of money  and i have created a Calculation that show how much each form cost based on the Form Name, Clinical Site, Time point etc.
      I have a layout in Budget DB based on the table that holds all forms.I started experiencing huge slowing down on the Performance of FMPro. I was wondering if i have to start thinking about trying a script or if i can optimize the calculation somehow.
      is this what Filemaker Pro can provide or it is my fault for creating not so well designed calculation?


      Thanks for your help and time, i really appreciated it!

       

      Let (
      [
      @A = Patient::Arm = "A";
      @BT = Form Name = "BT" ; @VP = Form Name = "VP_IP";@VP2 = Form Name = "VP_IP" ; @PA = Form Name = "PA1"; @RC = Form Name = "RC"; @AT = Form Name = "AT"; @FU = Form Name = "FU"; @OT = Form Name = "OT"; @AE = Form Name = "AE";//Form Name

      @CBT = Time Point = "Baseline"; @C1 = Time Point = "Week 1"; @C2 = Time Point = "Week 2"; @C3 = Time Point = "Week 3-4"; @C4 = Time Point = "Week 4-5"; @C2m = Time Point = "Month 2"; @C3m = Time Point = "Month 3";@C4m = Time Point = "Month 4-5"; @C6m = Time Point = "Month 6-8"; @C12m = Time Point = "Month 9-12";@C16m = Time Point = "Month 13-16"; @C17m = Time Point = "Month 17-20"; @C24m = Time Point = "Month 21-24"; @C30m = Time Point = "Month 25-31"; @C35m = Time Point = "Month 32-36"; @CND = Time Point = "not defined";//Time Point

      @OSU = Clinical  = "x";  @COH = Clinical  = "x"; @SC = Clinical  = "x"; //Clinical

      @OBCY = optional_blood_collection = "Yes";//miscellaneous
      @OBCN = optional_blood_collection = "No";

      @VPN = visit_paid = ""];


      Case(
      @xOSU;
      Case(
      @BT and @OSU and @OBCN and @VPN and @A;x;
      @BT and @OSU and @OBCY and @VPN and @A ;x;
      @VP and @C1 and @OSU and @VPN and @A; x;
      @VP2 and @C3 and @OSU and @VPN and @A; x;
      @PA and @C1 and @OSU and @VPN and @A;x;
      @PA and @C3 and @OSU and @VPN and @A;x;
      @PA and @C2m and @OSU and @VPN and @A;x;
      @RC and @C6m and @OSU and @VPN and @A;x;
      @AT and @C1 and @OSU and @VPN and @A; x;
      @AT and @C2 and @OSU and @VPN and @A; x;
      @AT and @C3 and @OSU and @VPN and @A; x;
      @AT and @C4 and @OSU and @VPN and @A; x;
      @AT and @C2m and @OSU and @VPN and @A; x;
      @AT and @C3m and @OSU and @VPN and @A; x;
      @AT and @C4m and @OSU and @VPN and @A; x;
      @AT and @C6m and @OSU and @VPN and @A; x;
      @AT and @C12m and @OSU and @VPN and @A; x;
      @AT and @C16m and @OSU and @VPN and @A; x;
      @AT and @C17m and @OSU and @VPN and @A; x;
      @AT and @C24m and @OSU and @VPN and @A; x;
      @AT and @C30m and @OSU and @VPN and @A; x;
      @AT and @C35m and @OSU and @VPN and @A; x;
      @AT and @CND and @OSU and @VPN and @A;x;
      @FU and @C1 and @OSU and @VPN and @A; x;
      @FU and @C2 and @OSU and @VPN and @A; x;
      @FU and @C3 and @OSU and @VPN and @A; x;
      @FU and @C4 and @OSU and @VPN and @A; x;
      @FU and @C2m and @OSU and @VPN and @A; x;
      @FU and @C3m and @OSU and @VPN and @A; x;
      @FU and @C6m and @OSU and @VPN and @A; x;
      @FU and @C12m and @OSU and @VPN and @A; x;
      @FU and @C16m and @OSU and @VPN and @A; x;
      @FU and @C24m and @OSU and @VPN and @A; x;
      @FU and @C30m and @OSU and @VPN and @A; x;
      @FU and @C35m and @OSU and @VPN and @A; x;



      @COH;
      Case(
      @BT and @COH and @OBCN and @VPN and @A;x;
      @BT and @COH and @OBCY and @VPN and @A ;x.x;
      @VP and @C1 and @COH and @VPN and @A; x;
      @VP2 and @C3 and @COH and @VPN and @A; x;
      @PA and @C1 and @COH and @VPN and @A;x;
      @PA and @C3 and @COH and @VPN and @A;x;
      @PA and @C2m and @COH and @VPN and @A;x;
      @RC and @C6m and @COH and @VPN and @A;x;
      @AT and @C1 and @COH and @VPN and @A; x;
      @AT and @C2 and @COH and @VPN and @A; x;
      @AT and @C3 and @COH and @VPN and @A; x;
      @AT and @C4 and @COH and @VPN and @A; x;
      @AT and @C2m and @COH and @VPN and @A; x;
      @AT and @C3m and @COH and @VPN and @A; x;
      @AT and @C4m and @COH and @VPN and @A; x;
      @AT and @C6m and @COH and @VPN and @A; x;
      @AT and @C12m and @COH and @VPN and @A; x;
      @AT and @C16m and @COH and @VPN and @A; x;
      @AT and @C17m and @COH and @VPN and @A; x;
      @AT and @C24m and @COH and @VPN and @A; x;
      @AT and @C30m and @COH and @VPN and @A; x;
      @AT and @C35m and @COH and @VPN and @A; x;
      @AT and @CND and @COH and @VPN and @A;x;
      @FU and @C1 and @COH and @VPN and @A; x;
      @FU and @C2 and @COH and @VPN and @A; x;
      @FU and @C3 and @COH and @VPN and @A; x;
      @FU and @C4 and @COH and @VPN and @A; x;
      @FU and @C2m and @COH and @VPN and @A; x;
      @FU and @C3m and @COH and @VPN and @A; x;
      @FU and @C6m and @COH and @VPN and @A; x;
      @FU and @C12m and @OSU and @VPN and @A; x;
      @FU and @C16m and @OSU and @VPN and @A; x;
      @FU and @C24m and @OSU and @VPN and @A; x;
      @FU and @C30m and @OSU and @VPN and @A; x;
      @FU and @C35m and @OSU and @VPN and @A; x;




      @SC;
      Case(
      @BT and @SC and @OBCN and @VPN and @A;x;
      @BT and @SC and @OBCY and @VPN and @A ;x;
      @VP and @C1 and @SC and @VPN and @A; x;
      @VP2 and @C3 and @SC and @VPN and @A; 3x;
      @PA and @C1 and @SC and @VPN and @A;x;
      @PA and @C2 and @SC and @VPN and @A;x;
      @PA and @C3 and @SC and @VPN and @A;x;
      @PA and @C2m and @SC and @VPN and @A;x;
      @RC and @C6m and @SC and @VPN and @A; x;
      @RC and @C3m and @SC and @VPN and @A; x;
      @RC and @C2m and @SC and @VPN and @A; x;
      @AT and @C1 and @SC and @VPN and @A; x;
      @AT and @C2 and @SC and @VPN and @A; x;
      @AT and @C3 and @SC and @VPN and @A; x;
      @AT and @C4 and @SC and @VPN and @A; x;
      @AT and @C2m and @SC and @VPN and @A; x;
      @AT and @C3m and @SC and @VPN and @A; x;
      @AT and @C4m and @SC and @VPN and @A; x;
      @AT and @C6m and @SC and @VPN and @A; x;
      @AT and @C12m and @SC and @VPN and @A; x;
      @AT and @C16m and @SC and @VPN and @A; x;
      @AT and @C17m and @SC and @VPN and @A; x;
      @AT and @C24m and @SC and @VPN and @A; x;
      @AT and @C30m and @SC and @VPN and @A; x;
      @AT and @C35m and @SC and @VPN and @A; x;
      @AT and @CND and @SC and @VPN and @A; x4;
      @AE and @C1 and @SC and @VPN and @A; x4;
      @AE and @C2 and @SC and @VPN and @A; x4;
      @AE and @C3 and @SC and @VPN and @A; x4;
      @AE and @C4 and @SC and @VPN and @A; x4;
      @AE and @C2m and @SC and @VPN and @A; x4;
      @AE and @C3m and @SC and @VPN and @A; x4;
      @AE and @C4m and @SC and @VPN and @A; x4;
      @AE and @C6m and @SC and @VPN and @A; x4;
      @AE and @C12m and @SC and @VPN and @A; x4;
      @AE and @C16m and @SC and @VPN and @A; x4;
      @AE and @C17m and @SC and @VPN and @A; x4;
      @AE and @C24m and @SC and @VPN and @A; x4;
      @AE and @C30m and @SC and @VPN and @A; x4;
      @AE and @C35m and @SC and @VPN and @A; x4;
      @AE and @CND and @SC and @VPN and @A; x4;
      @FU and @C1 and @SC and @VPN and @A; x;
      @FU and @C2 and @SC and @VPN and @A; x;
      @FU and @C3 and @SC and @VPN and @A; x;
      @FU and @C4 and @SC and @VPN and @A; x;
      @FU and @C2m and @SC and @VPN and @A; x;
      @FU and @C3m and @SC and @VPN and @A; x;
      @FU and @C6m and @SC and @VPN and @A; x;
      @FU and @C12m and @SC and @VPN and @A; x;
      @FU and @C16m and @SC and @VPN and @A; x;
      @FU and @C24m and @SC and @VPN and @A; x;
      @FU and @C30m and @SC and @VPN and @A; x;
      @FU and @C35m and @SC and @VPN and @A; x;





      ) + 0


      )

        • 1. Re: Calculation Problem maybe script???
          Sorbsbuster

          Is this not just a case of a patient 'ordering' a list of products, with each product being a certain price?  Then totalling up how much they have 'ordered'?

          • 2. Re: Calculation Problem maybe script???
            Immigranta

            Each Patient has visits. Those visit are described by Form Name, Site, Arm, Time Point. I would like to assign the right amount that correspondence to each visit. But calculation got really heavy and is slowing down the whole database. 

            • 3. Re: Calculation Problem maybe script???
              Sorbsbuster

              So is your structure:

              Patients ------------------< Visits ---------------< Treatments (or 'Items ordered' equivalent)

              First Name                       Form name               Blood test
              Second Name                   Site                         Injection
              Date of birth                    Arm                         Biopsy
                                                    Time point

              • 4. Re: Calculation Problem maybe script???
                Immigranta

                Patients ------------------< Visits ---------------*---------------OTHER DB

                First Name                       Form name              
                Second Name                   Site                        
                Date of birth                    Arm                     
                                                      Time point

                                                      Blood test

                                                      Injection

                                                      Amount(this is the calculation i am talking about)

                • 5. Re: Calculation Problem maybe script???
                  Sorbsbuster

                  But how do you get that calculation?  I am suggesting that you have another table of the various items that make up that total - just like a standard 'Invoice' that has many products on it.

                  • 6. Re: Calculation Problem maybe script???
                    Immigranta

                    This DB is finished and all the data is entered. I try to work with it the way it is. The calculation is described above and again it is working but it is very slow. i am working on a script to calculate those amount once instead the unstored calculation that does it every single time i touch the fileds.

                    • 7. Re: Calculation Problem maybe script???
                      philmodjunk

                      I agree with Sorbsbuster. I believe you should modify the design of your database to make it more efficient.