12 Replies Latest reply on Sep 28, 2015 1:35 PM by electon

    Updating records

    tays01s

      I have the following table relationships:

      Patient < Calc < IO < Nut_calcs > Nut_eqs

      &

      Patient < Nut_calcs 2 > Nut_eqs 2.

       

      I want to be able to evaluate Nut_eqs to produce results that appear in a Nut_calc 2 portal on Patient layout. The script below allows creation of record sets (51 calcs to be evaluated from Nut_eqs), it usess. However,

       

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

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

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

      Go to Layout [ “Nut_calcs” (Nut_calcs) ]

      If [ IsEmpty (Nut_calcs 2::_IDIO ) ]

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

      Exit Loop If [ $i = 52 ]

      New Record/Request

      Set Field [ Nut_calcs::_IDNut_eqs; $i ]

      Set Field [ Nut_calcs::_IDIO; $nutcalc_ioID ]

      End Loop

      End If

      Go to Record/Request/Page [ First ]

      Loop

      Set Field [ Nut_calcs::Req; Evaluate(Nut_eqs::Req) ]

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

      End Loop

      Go to Layout [ “Patient_L” (Patient) ]

      Commit Records/Requests [ No dialog ]

       

      2 problems:

      1. I find I can only generate a 1 set of Nut_calc records per Calc record, ie. it only generate for the first IO child of Calc.

      2. The evaluated result in the Nut_calc record is a repeat of the first set.

       

      I'm no longer seeing wood for trees. Any clues at to what I should check?

        • 1. Re: Updating records
          erolst

          Actually, I have no idea what you're doing or why (and it my be helpful if you'd explain it), but be aware that using a mere reference to a related field returns the value of that field from the first related record (in relationship sort order) only; if you want all related values, use List ( RelatedTable::field ).

           

          btw, please post-process your script code after pasting it (indents, correcting double entries per line); this may even help you understand your own logic better …

          • 2. Re: Updating records
            tays01s

            The aim of the script:

            1. First loop: To generate a new set of 51 related records IF one doesn't already exist.

            2. 2nd loop: To update other fields (I show only 1 eg) to the above set of records.

             

            Erolst: Were you suggesting using 'List ( RelatedTable::field )' where I've got GRRT?

            • 3. Re: Updating records
              ibrahim_bittar

              Taking a first look at your script, it looks like you can do your list of 51 records just using List ():

               

              LeftValues ( List ( MyRelatedTable::MyValue ) , 51 )

              • 4. Re: Updating records
                tays01s

                Which part(s) of the script were you suggesting use of:

                LeftValues ( List ( MyRelatedTable::MyValue ) , 51 )?

                • 5. Re: Updating records
                  electon


                  The Nut calcs revisited.

                  In the previous, related posts you were advised to create separate 51 NutCalc records for each Patient, or maybe each Calc.

                  From what I gather the relationships are at least 5 levels deep.


                  Patients < Calc < IO < Nut_calcs > Nut_eqs.

                   

                  2 problems:

                  1. I find I can only generate a 1 set of Nut_calc records per Calc record, ie. it only generate for the first IO child of Calc.

                  2. The evaluated result in the Nut_calc record is a repeat of the first set.

                   

                  You really need to try to explain the whole process in plain english like:

                  "I'm dealing with a patient and need to make this and that happen".


                  Can a patient have many Calcs?

                  What is the IO?

                  What are the Evaluations looking from Nut_calcs into the rest of the relationships?


                  At the moment you're only showing a part of the script, out of context. Maybe you need to create another Calc record or some more IO records.

                  Who knows.

                   

                  At the end you go back to Patient layout and Commit the record.

                  If you wonder off somewhere else and the changes in the Patient record are not commited, they may not be available somewhere else if you must rely on them.



                  • 6. Re: Updating records
                    tays01s

                    1. I've added a Commit Record before GRRT.

                    2. General aim/ function: In terms of records, each Patient may have several Calc records which in turn have several IO records (amount in/ out). Each IO record has a set of 51 Nut_calc records that can either be new or updated (should the IO values change). Nut_Eqs is a set of fields containing 'static text' or 'text equations'; the latter need evaluation calcs before the value is recorded on the Nut_calc records.

                     

                    So Nut_Eqs never change unless a reference calc or value should change, but Nut_calcs can be new records if there's a new IO record, or they could be updated if the IO value is changed.

                    • 7. Re: Updating records
                      electon

                      Then it looks like you need to establish the correct context as you traverse the relationship.

                      What you're trying to do is fairly complex and probably without seeing the file people can't tell what's wrong.

                      Or help without doing it for you, for that matter. Maybe you need to hire someone for advice.

                       

                      It's possible you can't do it with the relationships you have now and a utility ( extra fields, or a table ) must be built to get it working. Can't tell without seeing the file.

                       

                      My guess is that you need to familiarize yourself with how multi-level relationships work ( which records are evaluated depending on the layout you're on ) and how to establish the correct context.

                       

                      Some problems with the script, apology for the capitals.

                       

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

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


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

                      ??? WHAT IF THERE ARE NO RELATED RECORDS ?

                      YOU WILL STAY ON THE CURRENT LAYOUT AND YOUR LOOP WILL GO AHEAD AND CREATE 51 NEW RECORDS!!!


                      Go to Layout [ “Nut_calcs” (Nut_calcs) ]   NOT NEEDED !!! ALREDY ON THE Nut_calcs LAYOUT

                       

                      If [ IsEmpty (Nut_calcs 2::_IDIO ) ]

                      Nut_calcs2 IS SUSSPICIOUS, I THOUGHT NOT IN THE CONTEXT BUT MAY BE WRONG

                      GOES VIA THE PATIENT RECORD BUT IS A COPY OF THE CURRENT LAYOUT. I THINK YOU SHOULD TAKE CARE OF THAT BEFORE GTRR!!!



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

                      Exit Loop If [ $i = 52 ]

                      New Record/Request

                      Set Field [ Nut_calcs::_IDNut_eqs; $i ]

                      Set Field [ Nut_calcs::_IDIO; $nutcalc_ioID ]

                      End Loop

                      End If

                      Go to Record/Request/Page [ First ]

                      Loop

                      Set Field [ Nut_calcs::Req; Evaluate(Nut_eqs::Req) ]

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

                      End Loop

                      Go to Layout [ “Patient_L” (Patient) ]

                      Commit Records/Requests [ No dialog ]

                      • 8. Re: Updating records
                        tays01s

                        Electron: As you previously suggested, it's a contextual problem. I originally mentioned the relationships:

                        Patient < Calc < IO < Nut_calcs > Nut_eqs

                        &

                        Patient < Nut_calcs 2 > Nut_eqs 2.

                         

                        If I switch to using Nut_calcs 2 as the portal fields and these fields together with evaluating Nut_eqs 2 fields as the values when setting fields, I can switch between different IO records of difference Calc records, and the portal in correctly updated.

                         

                        2 things:

                        a. Nut_calcs 2 TO has only the first record repeated 51 times. Yet the portal using this table has the correct values as if from Nut_calcs TO.

                         

                        b. I've failed in getting the script to recognise when Nut_calc 2::_IOID 'IsEmpty' or not equal to $Nutcalc_IO the variable showing the current IO record. It therefore doesn't generate new records.

                        • 9. Re: Updating records
                          erolst

                          Is it just me, or does “nut_calc” sound suspiciously like “nut_job”?

                          • 10. Re: Updating records
                            electon

                            What do you normally do to achieve your calculation when you only have a patient record?

                             

                            I'm trying not to be offensive but I think it's way above your skill set and you should hire someone to help you.

                             

                            erolst, yes, it's driving me NUTS a bit too.

                            • 11. Re: Updating records
                              tays01s

                              If I only had a Patient record I would not generate Nut_calc values. I'd first have to create a Calc record, then IO record. Only then would there be enough information to be worth updating Nut_calc values.

                               

                              I dare say it is above my skill set. Fortunately my life doesn't depend on me being right ........yet!

                              • 12. Re: Updating records
                                electon

                                Good position to be in

                                 

                                It's definately way above my pay grade.