4 Replies Latest reply on Sep 2, 2015 8:22 AM by erolst

    Create/Edit Layout + Script

    KenHoopes

      Hey All,

       

      I'm looking for help with some scripting for a "create/edit" layout that will serve a dual purpose. This layout has three global fields on it, and it can be accessed via two different ways. The first way, is by clicking a "create new drawing revision" button. When accessed this way, the "create/edit" layout, with it's global fields, loads blank. The second way is by selecting a revision that already exists, on a separate lookup drawings layout, by clicking a "edit drawing revision" button. When access this way, the "create/edit" layout, with it's global fields, loads prepopulated with the information from the selected record in the previous "lookup drawings" layout.

       

      What I need it to do now, is run a script attached to a button that is intelligent enough to check against multiple fields if a record already exists, and if it does not, to create a new record, and if it does, then to simply edit the old one.

       

      (Note: This is not the actual database design, I'm just using this as an example to assist me in my actual database.)

       

       

      Show Custom Dialog [ Title: "Confirmation"; Message: "Are you sure you wish to create/edit this setup sheet record?"; Default Button: “Yes” ,

      Commit: “No”; Button 2: “No”, Commit: “No” ]

      If [ Get ( LastMessageChoice ) = 2 ]

      Halt Script

      End If

      If [ gtbl_dwgs::drawing = "" or gtbl_dwgs::rev = "" ]

      Show Custom Dialog [ Title: "Empty Fields"; Message: "All fields marked as required must be completed."; Default Button: “OK”,

      Commit: “No” ]

      Halt Script

      End If

      Go to Layout [ “tbl_dwgs” (tbl_dwgs) ]

      Enter Find Mode [ ]

      [ Pause ]

      Set Field [ tbl_dwgs::drawing ; gtbl_dwgs::drawing ]

      Set Field [ tbl_dwgs::rev ; gtbl_dwgs::rev ]

      Set Error Capture [ On ]

      If [ Get ( FoundCount ) = 0 ]

      New Record/Request

      Set Field [ tbl_dwgs::drawing ; gtbl_dwgs::drawing ]

      Set Field [ tbl_dwgs::rev ; gtbl_dwgs::rev ]

      Set Field [ tbl_dwgs::notes ; gtbl_dwg::notes ]

      End If

      If [ Get ( FoundCount ) > 0 ]

      ---I am sort of lost here as to what to put---

      End If

       

      That is where I get stuck. Additionally, I am not sure if I am correctly checking the table for multiple fields. Simply already having the drawing number is not enough, it needs to check if it has the drawing number, and the revision. Please advise on that as well.

        • 1. Re: Create/Edit Layout + Script
          erolst

          See if this helps you; read the inline comments.

           

          Show Custom Dialog [ Title: "Confirmation"; Message: "… ]

          If [ Get ( LastMessageChoice ) = 2 ]

            Exit Script

          End If

          If [ IsEmpty ( gtbl_dwgs::drawing ) or IsEmpty ( gtbl_dwgs::rev ) ]

            # [ alternatively: Count ( gtbl_dwgs::drawing ; gtbl_dwgs::rev ) ≠ 2 ]

            Show Custom Dialog [ Title: "Empty Fields"; Message: … ]

            Exit Script

          End If

          Enter Find Mode [ no Pause ]

          Go to Layout [ “tbl_dwgs” (tbl_dwgs) ]

          Set Field [ tbl_dwgs::drawing ; gtbl_dwgs::drawing ]

          Set Field [ tbl_dwgs::rev ; gtbl_dwgs::rev ]

          Set Error Capture [ On ]

          Perform Find

          If [ Get ( FoundCount ) = 0 ]

            New Record/Request

            Set Field [ tbl_dwgs::drawing ; gtbl_dwgs::drawing ]

            Set Field [ tbl_dwgs::rev ; gtbl_dwgs::rev ]

            Set Field [ tbl_dwgs::notes ; gtbl_dwg::notes ]

          If [ Get ( FoundCount ) > 0 ]

          ---I am sort of lost here as to what to put---

          End If

          # [ you've either found an existing record (or several), or created a new one; at any rate, you want to start editing it – I guess … ]

          Go to Field [ field you want to start editing in ]

           

          If you only want to edit an existing record, but not a new one:

           

          If [ Get ( FoundCount ) = 0 ]

            New Record/Request

            Set Field [ tbl_dwgs::drawing ; gtbl_dwgs::drawing ]

            Set Field [ tbl_dwgs::rev ; gtbl_dwgs::rev ]

            Set Field [ tbl_dwgs::notes ; gtbl_dwg::notes ]

            # [ Go to Layout ( original ) ? ]

          Else

            Go to Field [ field you want to start editing in ]

          End If

          • 2. Re: Create/Edit Layout + Script
            KenHoopes

            I have updated my script with your best practices, as well as the "else". The questions below correspond with commented areas within the script. Can you review the following and advise?

             

            Question 1: Is this the best way or an appropriate way to query against multiple fields? i.e. I need it to return a value of 0 if the "drawing & rev" are both not in use. There will be 10 or so records with the same drawing number, but each one should have a unique rev number, as shown below:

             

            drawing       rev

            L98456        1

            L98456        2

            L98456        3

            L74567        1

            L74567        2

             

            Question 2: I am not really sure how to format this to be sure that it updates the current record where a match on those two fields "drawing & rev" exist, as opposed to overwriting a non related record, or creating a new one (not sure if that is possible w/o the command "New Record/Request" but just trying to be safe. Does the "Find" if found navigate to that record, therefore making editing it only, the only possibility?

             

            Show Custom Dialog [ Title: "Confirmation"; Message: "… ]

            If [ Get ( LastMessageChoice ) = 2 ]

              Exit Script

            End If

            If [ IsEmpty ( gtbl_dwgs::drawing ) or IsEmpty ( gtbl_dwgs::rev ) ]

              Show Custom Dialog [ Title: "Empty Fields"; Message: … ]

              Exit Script

            End If

            Enter Find Mode [ ]

            Go to Layout [ “tbl_dwgs” (tbl_dwgs) ]

            # Question1

            Set Field [ tbl_dwgs::drawing ; gtbl_dwgs::drawing ]

            Set Field [ tbl_dwgs::rev ; gtbl_dwgs::rev ]

            # End of Question 1

            Set Error Capture [ On ]

            Perform Find

            If [ Get ( FoundCount ) = 0 ]

              New Record/Request

              Set Field [ tbl_dwgs::drawing ; gtbl_dwgs::drawing ]

              Set Field [ tbl_dwgs::rev ; gtbl_dwgs::rev ]

              Set Field [ tbl_dwgs::notes ; gtbl_dwg::notes ]

              # [ Go to Layout ( original ) ? ] I haven't messed with this yet, but will.

            Else

            # Question 2

              Go to Field [ field you want to start editing in ]

            # End of Question 2

            End If

            • 3. Re: Create/Edit Layout + Script
              KenHoopes

              In response to question 1, whether it is appropriate or not, or best practice, I don't know, but it is working. My final code which answers what to do with question 2, is pasted below. Thanks for everyone's help on this.

               

              Show Custom Dialog [ Title: "Confirmation"; Message: "… ]

              If [ Get ( LastMessageChoice ) = 2 ]

                Exit Script

              End If

              If [ IsEmpty ( gtbl_dwgs::drawing ) or IsEmpty ( gtbl_dwgs::rev ) ]

                Show Custom Dialog [ Title: "Empty Fields"; Message: … ]

                Exit Script

              End If

              Enter Find Mode [ ]

              Go to Layout [ “tbl_dwgs” (tbl_dwgs) ]

              Set Field [ tbl_dwgs::drawing ; gtbl_dwgs::drawing ]

              Set Field [ tbl_dwgs::rev ; gtbl_dwgs::rev ]

              Set Error Capture [ On ]

              Perform Find

              If [ Get ( FoundCount ) = 0 ]

                New Record/Request

                Set Field [ tbl_dwgs::drawing ; gtbl_dwgs::drawing ]

                Set Field [ tbl_dwgs::rev ; gtbl_dwgs::rev ]

                Set Field [ tbl_dwgs::notes ; gtbl_dwgs::notes ]

              Go to Layout ["Create Drawing" (gtbl_dwgs)]

              Show Custom Dialog [ Title: "Success"; Message: "… ]

              Go to Layout ["Drawings Home" ()]

                Set Field [ gtbl_dwgs::drawing ; "" ]

                Set Field [ gtbl_dwgs::rev ; "" ]

                Set Field [ gtbl_dwgs::notes ; "" ]

              Else

                Set Field [ tbl_dwgs::drawing ; gtbl_dwgs::drawing ]

                Set Field [ tbl_dwgs::rev ; gtbl_dwgs::rev ]

                Set Field [ tbl_dwgs::notes ; gtbl_dwg::notes ]

              Go to Layout ["Edit Drawing" (gtbl_dwgs)]

              Show Custom Dialog [ Title: "Success"; Message: "… ]

              Go to Layout ["View Drawing" (tbl_dwgs)]

                Set Field [ gtbl_dwgs::drawing ; "" ]

                Set Field [ gtbl_dwgs::rev ; "" ]

                Set Field [ gtbl_dwgs::notes ; "" ]

              End If

              • 4. Re: Create/Edit Layout + Script
                erolst

                Ken –

                 

                sorry for not getting back to you earlier.

                 

                Let me note that the Else[] block of your revised script looks a bit suspicious; if you have found a matching record (or several), you're essentially overwriting the first (or only) record's data with your search criteria – including the notes field … which means that if the notes global is empty, you'd be overwriting the potentially non-empty note field of an existing record.

                 

                Meaning to say: once you've found one or more records, I think you don't need to do anything (except for announcing “Success”, if you like …