14 Replies Latest reply on Jun 13, 2015 8:59 AM by tays01s

    Related values only problem

    tays01s

      Title

      Related values only problem

      Post

      Part of 1 of my scripts is:

      Go to Layout [ “Calc” (Calc) ]

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

      Go to Record/Request/Page [ Last ]
      If [ Patient::__ID=Calc::_PatientID and Calc::Date_calc = Get ( CurrentDate ) ]

      Show Custom Dialog [ Title: "Already a 'Calc' for this date"; Message: "A 'Calc' already exists for this date. To change 'Config', delete this 'Calc' record (this will delete the associated IO record and feeds for this date), then select 'New' Calc."; Default Button: “OK”, Commit: “Yes” ]

      Unfortunately I find the Custom dialogue being triggered when there is ANY Calc record of the same date instead of only Calc records of the same date & same patient. In fact I'd thought my early script lines should have made the script criter "Patient::__ID=Calc::_PatientID" unecessary. However, even the latter failed. I'm missing something obvious?

        • 1. Re: Related values only problem
          philmodjunk

          You appear to have two table occurrences: Calc and Patient linked in a relationship. What is that relationship?

          Your script starts by changing layouts to the layout "Calc". At that point in time just after changing layouts, is your script on the correct current layout for the results that you want?

          It's not very typical to use the parameters you've listed for the Go to related records step. The typical use for GTRR would look like this:

          #From a Layout based on Table 1 where there is a relationship linking Table 1 To Table 2.....
          Go To Related Record [Show only related records; From table: Table2; Using layout: "Table2" (Table2) ]

          Note that you are specifying the same table and layout that has already been specified in the preceding go to Layout step. I can't be absolutely sure that this is wrong, but at the least, it's not the usual use for this script step.

          • 2. Re: Related values only problem
            tays01s

            Sorry, I provided too little info. Patient and Calc are separate tables related as: Patient::__ID = Calc::_PatientID. Calc appears as a Portal ('Calc' object) on the Patient_L layout. I found that without the Go Calc layout step before GTRR, I'd still be on Patient_L and create Patient records (endlessly). More of the script (the bit I think relevant):

            Go to Object [ Object Name: "calc" ]
            Set Variable [ $CalcID; Value:Patient::__ID ]

            Go to Layout [ “Calc” (Calc) ]

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

            Go to Record/Request/Page [ Last ]
            If [ Patient::__ID=Calc::_PatientID and Calc::Date_calc = Get ( CurrentDate ) ]

            Show Custom Dialog [ Title: "Already a 'Calc' for this date"; Message: "A 'Calc' already exists for this date. To change 'Config', delete this 'Calc' record (this will delete the associated IO record and feeds for this date), then select 'New' Calc."; Default Button: “OK”, Commit: “Yes” ]

            Else If [ not IsEmpty (Calc::_PatientID) ]

            Duplicate Record/Request
            Set Field
            [ Calc::_PatientID; $CalcID ]

            Go to Layout [ “Patient_L” (Patient) ]

            Go to Object [ Object Name: "calc" ]

            Go to Portal Row [ Select; First ]
            Perform Script [ “Select Calc IO” ] Perform Script [ “New_IO” ]

            Else

            New Record/Request

            Set Field [ Calc::_PatientID; $CalcID ]

            Go to Layout [ “Patient_L” (Patient) ]

            Go to Object [ Object Name: "calc" ]

            Go to Portal Row [ Select; First ]
            Perform Script [ “Select Calc IO” ] Perform Script [ “New_IO” ]

            End If
            Go to Layout
            [ “Patient_L” (Patient) ]

            • 3. Re: Related values only problem
              philmodjunk

              Go To layout ["Calc" (Calc)] will change your context and the current calc record on the Calc record may not be connected to your current Patient record at all. It could be a Calc record for a completely different patient.

              It would appear then that your GTRR step was not set up correctly. Typical code for it would look like this in order to bring up a set of related Calc records from a given patient record on a layout based on Patients:

              If [IsEmpty ( calc::_PatientID ) ]
                 Show Custom Dialog ["There are no related Calc Records to go to."]
              Else
                 Go To Related Record [Show only related records; From table: Calc; Using layout: "Calc" (Calc) ]
              End IF

              If this fails to bring up the correct set of Calc records something is wrong either with your relationship or the data in your ID fields.

              • 4. Re: Related values only problem
                tays01s

                Point taken re. context. I have modified my scrip, but there's still a problem with the red section. The 'Else if' correctly duplicates the last RR and the 'Else' creates a new record if there are no RRs. However, the 'red' script fails to recognise RRs that have the current date and should trigger the warning dialogue.

                 

                Go to Object [ Object Name: "calc" ]

                Set Variable [ $CalcID; Value:Patient::__ID ]

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

                If [ Calc::Date_calc = Get ( CurrentDate ) ]

                Show Custom Dialog [ Title: "Already a 'Calc' for this date"; Message: "A 'Calc' already exists for this date. To change 'Config', delete this 'Calc' record (this will delete the associated IO record and feeds for this date), then select 'New' Calc."; Default Button: “OK”, Commit: “Yes” ]

                Else If [ not IsEmpty (Calc::_PatientID) ]

                Go to Record/Request/Page [ Last ]

                Duplicate Record/Request

                Else

                Go to Layout [ “Calc” (Calc) ]

                New Record/Request

                End If

                Set Field [ Calc::_PatientID; $CalcID ]

                • 5. Re: Related values only problem
                  philmodjunk

                  Does the GTRR go to a found set of multiple records (That what you mean by RR?) or just one?

                  Your IF step tests the value of Date_Calc for only a single record. If there are other records in the found set, they are checked at all.

                  And assuming that Date_calc is a field of type calculation, make sure that it's specified result type is date and not text or number.

                  • 6. Re: Related values only problem
                    tays01s

                    I'd want to check any Calc record for the current patient record; I'd included 'match current record only'.

                    2nd line: Does my script not check as I'd hoped in the line above?

                    Date_calc is a Date field, not a calculation.

                    So I'm still puzzled on this one!

                    • 7. Re: Related values only problem
                      philmodjunk

                      Your script only checks a single record. If there are other records in the found set to check, they are not checked. You'd need to modify your methods in order to check them.

                      What you might do is have the script enter find mode, specify the current date and then constrain the found set. If any records remain in your found set, then you have "Already a 'Calc' for this date"

                      • 8. Re: Related values only problem
                        tays01s

                        Freeze Window

                        Go to Object [ Object Name: "calc" ]

                        I've added a find. This does succeed in triggering the dialogue message that there's already a Calc record for this Patient on CurrentDate. However, if there isn't such a record it goes 'New Calc' rather than duplicating a previous Calc record for that patient.

                        I did try putting Calc::_PatientID = Patient::__ID & Calc::Date = current date in either a Constrain Find or Perform [] but that failed to block when there already was a record of the current date.

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

                        Set Variable [ $CalcID; Value:Patient::__ID ]

                        Enter Find Mode [ ]
                        Set Field [ Calc::_PatientID; $CalcID ]
                        Set Field [ Calc::Date_calc; Get(CurrentDate) ]

                        Perform Find [ ]

                        If [ Calc::Date_calc = Get ( CurrentDate ) ]

                        Show Custom Dialog [ Title: "Already a 'Calc' for this date"; Message: "A 'Calc' already exists for this date. To change 'Config', delete this 'Calc' record (this will delete the associated IO record and feeds for this date), then select 'New' Calc."; Default Button: “OK”, Commit: “Yes” ]

                        Go to Layout [ “Patient_L” (Patient) ] Else

                        If [ not IsEmpty (Calc::_PatientID) ]

                        Go to Record/Request/Page [ Last ]

                        Duplicate Record/Request

                        Set Field [ Calc::_PatientID; $CalcID ]

                        Else

                        Go to Layout [ “Calc” (Calc) ]
                        New Record/Request
                        Set Field
                        [ Calc::_PatientID; $CalcID ]

                        End If

                        End If
                        • 9. Re: Related values only problem
                          philmodjunk

                          I didn't say to perform a find, but to constrain the found set. replace the perform find script step with the constrain found set step. You simply want to apply the find criteria to your found set, not every record in the table.

                          • 10. Re: Related values only problem
                            tays01s

                            Sorry, v. new to 'Finds'. On deleting my last 3 lines and replacing with a Constrain, I get the same results as my previous post, ie. correctly get a warning when there's already a current date record but otherwise get a new record, not a duplicate. However, is this because I need to 'un-constrain'?

                            BTW when there's no current date record I'd prefer if it always skipped through 'Continue' instead of asking; but how to do it?

                            • 11. Re: Related values only problem
                              philmodjunk

                              Set error capture [on]

                              can supress the "no records found" dialog.

                              Yes, what I suggested only takes you to the point of determining if there is or is not a record already there with today's date.

                              We could do a second find or flip back to the original layout and do another GTRR once wev'e determined that there is no such date, but there's a simpler way to do this checking than to do a find or GTRR for it:

                              Set up another table occurrence of Calc and match to it as you currently do, but then also match by current date. To do this, you can define an unstored calculation field with a date result type defined with Get ( CurrentDate ) as it's calculation:

                              Patient::__ID = Calc|Today::_PatientID AND
                              Patient::cToday = Calc|Today::Date_calc

                              Then, before leaving the Patient layout,

                              ISEmpty ( Calc|Today::_PatientID )

                              will only be true if there is no related Calc record with today's date.

                              • 12. Re: Related values only problem
                                tays01s

                                I'd done as suggested and initially the dialogue indicating presence of a related record for current date wasn't being triggered.......but it does when starting from a new Patient. I assume legacy patient records didn't have all the 'new connections' in place.

                                • 13. Re: Related values only problem
                                  philmodjunk

                                  There should not be a difference between old and new. I would check the data in your match fields to see why.

                                  Make sure that cToday is an UNSTORED calculation field, not a date field with an auto-entered date nor a stored calculation field.

                                  • 14. Re: Related values only problem
                                    tays01s

                                    Fixed. I thought I'd 'unstored' it, but hadn't.

                                    Thank you.