1 2 Previous Next 22 Replies Latest reply on Jul 10, 2014 10:56 AM by philmodjunk

    Script failure

    tays01s

      Title

      Script failure

      Post

           I have a series of parent child portals whose records are created/ deleted from scripted buttons. This all works fine until I delete all records from all tables except Patient. I then find that the scripts (eg below) instead of creating a new record within the portal, instead create a new patient.

           Relationships: Patient < Calc < Prescription. I have NOT activated create/delete via the relationships because I want everything to work via the scripts.

      Perform Script [ “Allow user abort” ]

      Freeze Window
                          Go to Object
      [ Object Name: "calc" ] If [ not IsEmpty (Calc::_PatientID) ]

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

      Go to Record/Request/Page [ Last ]
      Duplicate Record/Request

      Else
                          Set Variable
      [ $ID; Value:Patient::__PatientID ]

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

      End If

      Go to Layout [ “L_Patient Copy” (Patient) ]

      Go to Object [ Object Name: "calc" ]

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

      Perform Script [ “New_prescription” ]

            

        • 1. Re: Script failure
          philmodjunk

               Go to Related records will not do anything but silently return an error code if there are no related records for the current parent record. The script then skips to the next script step--which is intended to work on the layout your GTRR step was supposed to change to but didn't, and executes it.

               To fix this, you need to either add an If step just before the GTRR that checks for the presence of related records or an If step just after the GTRR that uses Get ( LastError ) to check for a nonzero error code. This If step can then allow you to add code that uses Go to Layout to take you to the correct layout followed by a New Record/Request step that now executes in the context provided by the correct layout.

          • 2. Re: Script failure
            FentonJones

                  

                 I can't really see why the new record is not where you expect it; so I'm kind of guessing here. And Phil's advice is likely the reason. But I don't know that he noticed that you DID have an IF test for the Go to Related Record, because it wasn't visible on the line it should be (it is difficult to copy/paste a FileMaker script correctly to a web page). Your script looks like:

                  

            Perform Script [ “Allow user abort” ]

            Freeze Window
            Go to Object [ Object Name: "calc" ] 

            If [ not IsEmpty (Calc::_PatientID) ]

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

            Go to Record/Request/Page [ Last ]
            Duplicate Record/Request

            Else

            Set Variable [ $ID; Value:Patient::__PatientID ]

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

            End If

            Go to Layout [ “L_Patient Copy” (Patient) ]

            Go to Object [ Object Name: "calc" ]

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

            Perform Script [ “New_prescription” ]

                  

                 So, I don't see why the script creates the new record in the original layout. You are going back to a different "Patient" table's layout, then going to the: object name: "calc". Is that set correctly on that layout? There are also 2 scripts called; one of then is "New_prescription". Is that where the error is happening?

                  

                 It seems the your first test, "not IsEmpty (Calc::_PatientID)," would be valid (or not) correctly. I would either turn off those later scripts, or (mostly likely) run with Script Debugger (requires FileMaker Pro Advanced), as it is the easiest way to see when/where a script fails.

                  

                 The one thing I did notice is that you have "Matching found set" option specified for the GTRR. That would mean "all related records matching ALL patients of the Found Set of the Patient table (of the current window)"; which is likely more than what you want. Just use [x] Match current record only. This option is for where you're coming "from"; it can still see multiple records of where you're going "to". 

                  

                 All of the above may be some help, or may just be confusing. I'd run this with FileMaker Pro Advanced, one step at a time (or similar). It can show you exactly when that "new record" happens, and you'd see what layout it is (still) on.

            • 3. Re: Script failure
              philmodjunk

                   I did miss the If step.

                   Like Fenton, I don't see any way that it could possibly create a new record in Patients.

              • 4. Re: Script failure
                tays01s

                     As suspected there was a fault with the downstream 'prescription' script that caused a new patient (parent layout) record to be created. The scripts still have 2 faults:

                     1. Calc: Fine if there's a record in the portal, but if empty, the first time the new button is pressed, Calc_n and _CalcID fields blank. On the 2nd click they come back and the new Calc record is generated.

                     2. Prescription: New records are only generated for the first calc record even when later records are selected.

                     The current scripts are below:

                     1. Calc:

                Perform Script [ “Allow user abort” ]

                Freeze Window

                Go to Object [ Object Name: "calc" ]

                      

                If [ not IsEmpty (Calc::_PatientID) ]

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

                Go to Record/Request/Page [ Last ]

                Duplicate Record/Request

                      

                Else

                Set Variable [ $ID; Value:Patient::__PatientID ]

                Go to Layout [ “Calc” (Calc) ]

                New Record/Request

                Set Field [ Calc::_PatientID; $ID ]

                End If

                Go to Layout [ “L_Patient Copy” (Patient) ]

                Go to Object [ Object Name: "calc" ]

                Go to Portal Row [Select; First ]

                Perform Script [ “Select Calc Prescription” ]

                Perform Script [ “New_prescription” ]

                2. Prescription:

                           

                Perform Script [ “Allow user abort” ]

                Freeze Window

                Go to Object [ Object Name: "prescription" ]

                If [ not IsEmpty (Prescription::_CalcID) ]

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

                Go to Record/Request/Page [ Last ]

                Duplicate Record/Request

                Else

                Set Variable [ $ID; Value:Patient::Calc_n ]

                Go to Layout [ “Prescription” (Prescription) ]

                New Record/Request

                Set Field [ Prescription::_CalcID; $ID ]

                End If

                Go to Layout [ “L_Patient Copy” (Patient) ]

                Go to Object [ Object Name: "prescription" ]

                Go to Portal Row [ Select; First ]

                      

                • 5. Re: Script failure
                  philmodjunk

                       What does the script "allow user abort" do?

                       Am I correct that the first script starts from a layout based on Patients?

                       Is the relationship between patients and calc this?

                       Patients::__PatientID = Calc::_PatientID

                       Yet script 2 implies a third table occurrence? While your project rings a few bells that I've seen posts by you before, I'm very unlikely to remember all the table occurrences and relationships involved. Please document your current design as relevant to these two scripts and your layouts.

                       At the end of Script 1, two scripts are performed:

                       “Select Calc Prescription”and“New_prescription”

                       Neither are named "Prescription" so it is not clear which, if either, refers to script 2 and that leaves the other script a mystery.

                        

                  • 6. Re: Script failure
                    tays01s

                         1. "allow user abort" is to allow only the administrator to abort the script to prevent hacking.

                          

                         2. Is the relationship between patients and calc this?

                         Patients::__PatientID = Calc::_PatientID and between Calc and Prescription is:

                         Calc::__CalcID = Prescription::_CalcID

                         3. Script 2 relies on a portal filter (Prescription::_CalcID=Patient::Calc_n) rather than a TO of prescription being related to Patient.

                         4. Select Calc Prescription script (not the best name!) simply selects a specific Calc record so that the Prescription portal filter pulls up only those Prescriptions related to that single Calc.

                         5. New_prescription script (pasted as 2. Prescription in my post above) creates a new Prescription record either by duplicating the last one in the portal or, if the portal is empty, creating a 'blank' new record.

                          

                         At least those were the intentions.

                    • 7. Re: Script failure
                      tays01s

                           A thought re. my 'new Prescription' script only creating new records for the first Calc, is this because the 'if(not IsEmpty', acts on a field, not the portal so that if there is any Prescription, the first 'if' alternative will be to duplicate it and this means every Prescription will related to the first Calc, whereas if there were no Prescriptions, the alternative for the new Prescription script is to create a new blank Prescription related to whichever Calc is selected.

                      • 8. Re: Script failure
                        philmodjunk

                             1. yes, but HOW does it do that? There might be a side affect of that script that affects how your script functions and thus it would be good to see that script.

                             2. Which means you have Patients---<Calc----<Prescription (each relationship is one to many)

                             3. Sorry, but I see nothing in script 2 that will be affected by the presence or absence of a portal filter. The GTRR step will produce identical results whether the portal is filtered or unfiltered. In fact, the record duplicated could be a record that is excluded by the filter from the portal.

                             4. While that explains some of my concerns stated in 3 above, that still leaves possible details that affect getting the correct results missing from your posts. It would help to see that additional script. Keep in mind that with a filtered portal,

                             not IsEmpty (Prescription::_CalcID) ]

                             can be true while your filtered portal is empty.

                              

                        • 9. Re: Script failure
                          tays01s

                               Ah, I'd done a further post that doesn't seem to have been recorded. I changed the first line of the 'if' part of the script and it seems to have fixed the problem:

                          Go to Object [ Object Name: "prescription" ]
                          If [ IsEmpty (FilterValues ( List(Prescription::_CalcID);"Patient::Prescription_n")) ]

                          Set Variable [ $ID; Value:Patient::Calc_n ] Go to Layout [ “Prescription” (Prescription) ] New Record/Request
                                              Set Field
                          [ Prescription::_CalcID; $ID ]

                          Else
                                              Go to Portal Row
                          [ Select; First ]

                          Perform Script [ “Select Prescription Input” ]

                          Go to Record/Request/Page [ Last ]
                          Duplicate Record/Request

                          End If

                               Does this explain the original problem?

                          • 10. Re: Script failure
                            philmodjunk

                                 Does this explain the original problem?

                                 I don't see how that answers all the questions that I've posted.

                                 It would still seem to risk issues created by that portal filter. Expressions such as: List(Prescription::_CalcID) will list ALL related records, not just those that pass a portal filter calculation.

                            One solution to that issue is to set up a relationship that uses Prescription::_CalcID=Patient::Calc_n as an additional set of match fields in the relationship instead of specifying a portal filter.

                            • 11. Re: Script failure
                              tays01s

                                   Do you mean set up: Prescription::_CalcID=Patient::Calc_n

                                   via the relationship graph?

                              • 12. Re: Script failure
                                philmodjunk

                                     I mean use the relationship graph to add those fields as an additional pair of match fields to the pair that you already have, then remove the portal filter expression as it then becomes redundant. This may require adding a new Tutorial: What are Table Occurrences? to your relationship graph in order to preserve the original relationship unchanged for other parts of your database that rely on that specific relationship in order to work.

                                • 13. Re: Script failure
                                  tays01s

                                       OK, so got rid of filters [BTW, I'm a bit confused as to where filters are useful if not for ensuring the correct subset of records shows in a portal?].

                                        

                                  Relationships:

                                       Patient < Calc [Patient::Patient__ID < Calc::Patient_ID]

                                       Calc < Prescription [Calc::Calc__ID < Prescription::Calc_ID] and then using a Prescription TO

                                       Patient < Prescription 2 [Patient::Calc_n < Prescription::Calc_ID]

                                  Layout = Patient

                                  Portals: Calc = Calc & Prescription = Prescription 2

                                        

                                       I've updated scripts and

                                       - Calc records are created + appear + are selected correctly.

                                       - Prescription records are created and appear with the correct Prescription__ID & Calc_ID and related to the correct Calc. However, the selection buttons I have in Prescription portal rows only select the first created record (despite the ID number changing when I select others) & if I change the date field on 1 portal record, it changes for all, including those of different related Calcs.

                                  • 14. Re: Script failure
                                    philmodjunk

                                         Portal filters can be used to produce a subset of the related records that would appear in the portal if it were unfiltered. This can be used to reduce the number of table occurrence boxes and special match fields used in your relationship graph. And sometimes, the expression needed to filter the set of related records is not one that can readily be replicated in a relationship. PatternCount ( PortalTable::TextField ; "SomeText" ) is an example of such a filter expression.

                                         But filtered portals can be many times slower to update and calculations that refer to data in a related table do not access a portal's filter expression, they access data just as though the portal is unfiltered. (In fact, you will get the same results even if you do not have a portal to that table on any of your layouts.)

                                         And Filter expressions that use the = operator to match the values of two fields are expressions that can (99% of the time) be readily replicated with match fields used in the relationship and this is often necessary in order to get faster layout updates and (unless you use ExecuteSQL) is needed in order for calculations defined in fields, conditional formats, Hide object when settings and script steps to access the correct set of records in the related table.

                                         

                                              if I change the date field on 1 portal record, it changes for all, including those of different related Calcs.

                                         Enter layout mode and click this date field. Then check to see what is shown in "display data from" on the Inspector's data tab. I predict that you'll see Prescription:: as the first part of the text in this box instead of "Prescription 2::" Thus, you are changing the date of the first related Prescription record no matter what portal row is used instead of changing the date of a record in the Prescription table, but via the relationship to Prescription 2.

                                    1 2 Previous Next