5 Replies Latest reply on Oct 12, 2015 10:29 PM by tays01s

    Generating a series of new or updated records

    tays01s

      I have some related tables:

      Patient < Calc < IO < Nut_calc > Nut_eq

      also relationships using TOs:

      IO::__uuID = Nut_calc::_IOuuID &

      Patient::IO_n = IO 2::__uuID &

      Patient::IO_n = Nut_calc 2::_IO

       

      From a Patient table layout, using a 'button script' in an IO 2 portal I want a set of 51 Nut_calc records. These should be new where none exist for an IO 2 record or if a set already exist, they should be updated. Each Nut_calc field is evaluated from a text equation on Nut_eq.

       

      If there are no Nut_calc records, new records and evaluations are done perfectly. However, after the first set of records, the 2nd loop fails to update old records or set the fields for new records.

       

      Set Field [ Patient::IO_n; IO 2::__uuID ]

      Set Variable [ $nutcalc_ioID; Value:Patient::IO_n ]

      Commit Records/Requests [ No dialog ]

      Go to Layout [ “Nut_calcs” (Nut_calcs) ] {without this line, focus stays on patient > generates new patient records}

      If [ IsEmpty (Nut_calcs 2::_IOuuID) ]

      Loop

      Set Variable [ $i; Value:$i+1 ]

      Exit Loop If [ $i = 52 ]

      New Record/Request Set Field [ Nut_calcs::__uuID; Get(UUID) ]

      Set Field [ Nut_calcs::_IOuuID; $nutcalc_ioID ]

      Set Field [ Nut_calcs::_Nut_eqsID; $i ]

      End Loop

      End If

       

      Go to Related Record [ From table: “Nut_calcs”; Using layout: <Current Layout> ] [ Show only related records ]

      Go to Record/Request/Page  [ First ]

      Loop If [ Nut_calcs::_IOuuID = Patient::IO_n ]

      Set Field [ Nut_calcs::Nut; Nut_eqs::Nut ]

      Go to Record/Request/Page [ Next; Exit after last ]

      End If End Loop

       

      Go to Layout [ “Patient_L” (Patient) ]

        • 1. Re: Generating a series of new or updated records
          erolst

          tays01s wrote:

           

          I have some related tables:

          Patient < Calc < IO < Nut_calc > Nut_eq

          also relationships using TOs:

          IO::__uuID = Nut_calc::_IOuuID &

          Patient::IO_n = IO 2::__uuID &

          Patient::IO_n = Nut_calc 2::_IO

           

          From a Patient table layout, using a 'button script' in an IO 2 portal I want a set of 51 Nut_calc records. These should be new where none exist for an IO 2 record or if a set already exist, they should be updated. Each Nut_calc field is evaluated from a text equation on Nut_eq.


          Why not have a clear separation of concerns?


          If [ IsEmpty (Nut_calcs::_IOuuID) ]

            # [ to 2 or not to 2 … ? use more meaningful TO names! ]

            Set Field [ Patient::IO_n; IO 2::__uuID ]

            Set Variable [ $nutcalc_ioID; Value:Patient::IO_n ]

            Go to Layout [ “Nut_calcs” (Nut_calcs) ]

            Loop

              Exit Loop If [ Let ( $i = $i + 1 ; $i > 51 ]

              New Record/Request

              Set Field [ Nut_calcs::__uuID; Get(UUID) ]

              Set Field [ Nut_calcs::_IOuuID; $nutcalc_ioID ]

              Set Field [ Nut_calcs::_Nut_eqsID; $i ]

            End Loop

          Else

            Go to Related Record [ From table: “Nut_calcs”; Using layout: Nut_calcs ; Show only related records ]

            Replace Field Contents [ no dialog ; Nut_calcs::Nut; Nut_eqs::Nut ]

          End If

          Go to Layout [ “Patient_L” (Patient) ]


          btw, you're not doing yourself a favour using these abbreviated field names – let alone the people you're asking for assistance …

          • 2. Re: Generating a series of new or updated records
            tays01s

            Point taken re. TO naming.

             

            Your script looks neater. The problem remaining is that when I switch between IO records, the 'replace field contents' replaces all records contents with calcs pertaining to the current IO record. I had wanted only those Nut_calc records updated that were related to the current IO, ie. the on active in the Patient portal and using the following relationships:

            Patient::IO_n = IO 2::__uuID &

            Patient::IO_n = Nut_calc 2::_IO.


            To get the intended  result, I've instead evaluated 'Nut_calc 2' fields. I need to do a bit more testing tomorrow before being sure.


            But thank you, the 'replace field contents' is much neater.

            • 3. Re: Generating a series of new or updated records
              erolst

              tays01s wrote:

               

              Point taken re. TO naming.

               

              Your script looks neater. The problem remaining is that when I switch between IO records, the 'replace field contents' replaces all records contents with calcs pertaining to the current IO record. I had wanted only those Nut_calc records updated that were related to the current IO, ie. the on active in the Patient portal and using the following relationships:

              Patient::IO_n = IO 2::__uuID &

              Patient::IO_n = Nut_calc 2::_IO

               

              You can of course use a Loop rather than RFC; the main thing I wanted to suggest was to have a clean separation in your script between two similar, but different tasks:

               

              if [ they don't exist ]

                go to context

                create & set as you go

              else

                go to context & isolate

                update

              else

               

              Especially for scripts with complicated logic (or if you're temporarily confused … ), it pays off to create such a pseudo, higher-level script using comment lines; then flesh that skeleton out using real script steps (and leave the pertinent comment lines in).

              • 4. Re: Generating a series of new or updated records
                tays01s

                I've made note; it's a useful logic to remember.

                 

                Snag: I can generate new Nut_calc records and if there is only 1 'IO' record per 'Calc' record, then the set Nut_calc records related to that IO record are correctly evaluated. However, for subsequent 'IO' records, although new 'Nut_calc' records are generated with the correct foreign key from IO, the evaluations are from the first IO record.

                 

                This failure of GTRR to isolate only the current 'IO' record and it's related set of 'Nut_calc' records was something I've struggled with. I remember a Philmodjunk article where there are cautions re. GTRR not taking you to the correct context when using a portal row script.

                 

                Just a suspicion, I'm wondering if there's a problem in the calcs that are evaluated for 'Nut_calc' records in that they use this kind of script:

                 

                ExecuteSQL("SELECT SUM (rx_water) FROM FeedsUsed WHERE Route='e' AND \"_IOuuid\"=?";"";"";IO 2::__uuid)

                 

                However, again, because there should only be 1 'IO 2' record current at any one time, I'm puzzled that the evaluation is specific to the current IO record.

                • 5. Re: Generating a series of new or updated records
                  tays01s

                  The original relationships:

                  Patient::IO_n = Nut_calc 2::_IOuuid was wrong. It should have been:

                  IO 2::__uuID = Nut_calc 2::_IOuuid to get the current 'IO 2' record alone used.