3 Replies Latest reply on Apr 4, 2013 5:21 AM by mikebeargie

    Scripting Find/Adding Record using Error 401 / Criteria

    kraftfm

      I am trying to build an add record script into an application. Depending on certain criteria, the user wants to set a different 'date' on the record.

      1. Set date = current date 'if' no records exist for that client-consultation combination
      2. Set date = last date entered for that client-consultation 'if' records exist for this client's consultation

       

      The application is for an alternative medicine counselor and she is entering medication during a consultation for a specific client. A client can have many consultations and can be prescribed many medications. Therefore, in the case where the client 'has' a consult(s), the date to be used is for the 'current' consultation, not others (the add is being done in a portal within a consultation layout).

       

      The table graph is below. The pharmacy table is just used to store medication names.

       

      Screen Shot 2013-03-15 at 3.46.06 PM.png

       

      The 'date' is when an 'Add Record' is requested for a medication to be added to a specific client's consultation. I have written the following script, however am not getting the desired result. (note: fields preceeded with 'x' are Global fields)

       

      Set Field [Consult::xglobalConsultID; Consult::__pkCOnsultID]

      Go to Layout ["Medication_Form" (Medication)]

      Set Error Capture [On]

      Enter Find Mode [Pause]

      Set Field [Medication::_fkConsultID; Consult::xglobalConsultID]

      Perform Find []

      If [Get(lastError)=401]

      New Record/Request

      Set Field [Medication::_fkConsultID; Consult::xglobalConsultID]

      Set Field [Medication::DateStart; Get(CurrentDate)]

      Else

      Sort Records by Field [Ascending; Medication::DateStart]

      Go to Record/Request/Page (Last)

      Set Field [Medication::xDate; Medication::DateStart]

      New Record/Request

      Set Field [Medication::_fkConsultID; Consult::xglobalConsultID]

      Set Field [Medication::DateStart; Medication::xdate]

      End If

      Go to Layout ["Consult" (Consult)]

      Go to Object [Object Nate: "Medication Detail Portal"]

      Go to Portal Row [Select; First]

      Go to Field [Medication::DateStart]

       

      Appreciate some input. THANK YOU!!

        • 1. Re: Scripting Find/Adding Record using Error 401 / Criteria
          mikebeargie

          you already have a portal displaying "related" medication on your consultation form. Assuming all the related medication records have the same date, or you have sorted the relationship appropriately, you should be able to use this:

           

          set variable $id = Consult::__pkCOnsultID

          If ( count ( Medication::_fkConsultID) > 0 )    <-- If there is more than one related record

             set variable $date = Medication::DateStart

          Else

             set variable $date = Get (CurrentDate)   <-- Else, if there are no related records

          End If

          go to layout - Medication

          new record

          set field Medication::_fkConsultID -> $id

          set field Medication::DateStart -> $date

          Go to Object [Object Nate: "Medication Detail Portal"]

          Go to Portal Row [Select; First]


          Since none of your global fields are used in your relationship graph, you shouldn't need to use them at all. Use variables instead.

          • 2. Re: Scripting Find/Adding Record using Error 401 / Criteria
            kraftfm

            Mike

             

            Thanks for this input.  Unfortunately when I used the above I got nothing.  The cursor just went to the last portal row.  No records were added.  I was also unsure what you meant when you said "Assuming all the related medication records have the same date or you have sorted the relationship appropriately...".  The point is, medication for a specific consult (remember different clients will have multiple consults and there can be multiple medications per consult) have different dates.

             

            When the "If (count..." is done above, the layout is still 'Consult'.  Does Filemaker know to take the __pkConsultID, use it in the equation for _fkConsultID and look in the Medication table?  I usually go to that table's layout first.

             

            Let me know if you have any other thoughts.

             

            Appreciate it.  Mike

            • 3. Re: Scripting Find/Adding Record using Error 401 / Criteria
              mikebeargie

              Yes, the count() function ( http://www.filemaker.com/12help/html/func_ref1.31.3.html ) counts records through a relationship. So you can count your related "Medication" records from a "Consult" layout.

               

              Do you have FMP12 advanced? Using the script debugger and data viewer (tools menu), I would go through and see what is failing. I would imagine that something with the key handling is going wrong somewhere.

               

              I noticed the above example I posted is missing a "go to layout - original layout" step after the two "set field" commands. I might also add a "refresh window - flush cached join results" to the end to refresh the data in the portal.