1 2 Previous Next 15 Replies Latest reply on Feb 24, 2016 1:44 PM by lcot17

    Script Trigger - Finishing Help Needed

    lcot17

      Hiya,

       

      Is it possible to ask for help finishing a script trigger.

      I have two tables: 1) Product Options 2) Barcode Pool

      Keys in each:

      Product Options

      __productOptionIDpk

      _barcodeIDfk

      Barcode No

       

      Barcode Pool

      __barcodeIDpk

      _productOptionIDfk

      Barcode No

       

      In basic terms - I am trying to synchronise the two!

      A barcode cannot be assigned to two product options, and because a product option cannot have two barcodes.

      SO, when I assign a barcode in product options, I want to set the option ID in the barcode pool. This is because a calculation relies on it to calculate an availability status.


      My script is intended to: (set to OnObjectSave)

      1) Check if barcode ID value is set or empty

      2) If empty, exit script

      3) If set - set product option ID variable, set barcode ID variable, go to barcode pool and set the ProductOptionID in the record that matches the barcode ID.


      The script I currently have is:

      Screen Shot 2016-02-23 at 15.35.50.png

      Can someone help me finish this script?

       

        • 1. Re: Script Trigger - Finishing Help Needed
          Mike_Mitchell

          lcot17 wrote:

           

          In basic terms - I am trying to synchronise the two!

          A barcode cannot be assigned to two product options, and because a product option cannot have two barcodes.

           

           

          If it's a one-to-one relationship, why are they in separate tables?

          • 2. Re: Script Trigger - Finishing Help Needed
            lcot17

            Mike_Mitchell wrote:

             

            If it's a one-to-one relationship, why are they in separate tables?

             

            Basically in one table I have all Product Options - imagine these records as LineItems to a product. (similar to invoice products). The product options are size variations for each product. (Small, Medium, Large for example). A barcode needs to be assigned to each individual variation.


            And in the other I have barcode pool - this is a big list of records of barcodes I have available to me that my organisation has purchased from GS1.

             

            By doing it this way, I can store ALL barcodes in a separate table and pick them off to assign to a product option one by one.

            Its just stops me having an excel with 10,000 barcodes in and accidentally assigning one barcode twice or missing a barcode out etc.

             

            I could also count how many barcodes I have assigned and how many I have available etc.

            • 3. Re: Script Trigger - Finishing Help Needed
              Mike_Mitchell

              I see. Well, you don't need both keys in both tables. Decide which one is the "parent" (probably the product) and just put the "child" ID in that table. By doing that, your script winds up looking like this:

               

              If [ IsEmpty ( Product Options::_barcodeIDfk ]

                   Exit Script [ ]

              Else If [ not IsEmpty ( Barcode Pool::__barcodeIDpk ) ]

                   Show Custom Dialog [ "Error" ; "Barcode doesn't exist in the pool!" ]

                   Go to Field [ Product Options::_barcodeIDfk ; Select ]

              Else If [ Barcode Pool::status = "In use" ]

                   Show Custom Dialog [ "Error" ; "Barcode already in use!" ]

                   Go to Field [ Product Options::_barcodeIDfk ; Select ]

              Else

                   Set Field [ Barcode Pool::status ; Value: "In use" ]

                   Commit Record / Request [ ]

              End If

               

              Okay, so what have I done here? First, I'm assuming you're just setting the barcode ID in the Products table. This can be used to set the relationship between the two, so there's no need to go to Barcodes or have a product ID field in that table.

               

              Second, the first Else If clause makes a determination whether the barcode is already in use or not. By looking into the Barcode Pool table and seeing if there's a valid match (via not IsEmpty), you determine whether or not the record exists. You want to check that, because if the user puts in something that's not a valid item, you don't want your script to set that field.

               

              Third, the second Else If clause. This determines whether the barcode is already in use. I'm using a field called "status" to record that information, rather than a calculation. Why? Because calculations are slow and impose a performance demand on the system every time they're evaluated. By simply setting a field for the status of the item, you'll dramatically improve the performance of the system, including reporting.

               

              Finally, I set that status field if the barcode exists and isn't already assigned. Done!

               

              HTH

               

              Mike

              • 4. Re: Script Trigger - Finishing Help Needed
                lcot17

                Thank you Mike_Mitchell for your response!!

                I've actually learned about a few new script functions here so thank you.

                 

                I understand fully your logic here and it makes sense 100% with the three if clauses.

                I do have a couple of short queries i'd like to quickly discuss with you.

                 

                At present, with your script I do only get to the 1st elseif statement and get prompted with the error message. It seems that even though a valid ID is entered, it still doesn't seem to find a record. Maybe something is missing here...

                I have screenshots for you - and just to avoid any confusion, i've attached the database to the reply.

                 

                Screen Shot 2016-02-23 at 23.33.42.png

                 

                Screen Shot 2016-02-23 at 23.33.52.png

                • 5. Re: Script Trigger - Finishing Help Needed
                  Mike_Mitchell

                  Oops, my bad. The script should be:

                   

                  If [ IsEmpty ( Product Options::_barcodeIDfk ]

                       Exit Script [ ]

                  Else If [ IsEmpty ( Barcode Pool::__barcodeIDpk ) ]                                             <--- Change this line

                       Show Custom Dialog [ "Error" ; "Barcode doesn't exist in the pool!" ]

                       Go to Field [ Product Options::_barcodeIDfk ; Select ]

                  Else If [ Barcode Pool::status = "In use" ]

                       Show Custom Dialog [ "Error" ; "Barcode already in use!" ]

                       Go to Field [ Product Options::_barcodeIDfk ; Select ]

                  Else

                       Set Field [ Barcode Pool::status ; Value: "In use" ]

                       Commit Record / Request [ ]

                  End If

                  • 6. Re: Script Trigger - Finishing Help Needed
                    lcot17

                    Perfect!! I thought so - but i'm not the expert here so I thought i'd check!

                     

                    Can I ask, if I were to expand this to go as far as, if I 'REMOVE' a barcode from a product option - this should change the status back to "". What would be your best advice on how to approach this?

                     

                    If I were to empty the field AFTER it had been set?

                     

                    I had a brief head scratch over this issue earlier ...

                    • 7. Re: Script Trigger - Finishing Help Needed
                      Mike_Mitchell

                      That's a little more complicated. Once you remove the barcode key, it breaks the relationship, so you can't directly edit the related record so easily. There are a couple of ways to accomplish the task:

                       

                      1) Create a global field and base the relationship on that. You can populate the global field automatically if the product already has a barcode ID using an auto-enter calculation or an OnObjectEnter Script Trigger. Then if the actual key is blank, use the relationship from the global field to set the status.

                       

                      2) Use an offscreen window to perform a Find for the original barcode ID (which you store in a variable on, again OnObjectEnter) and update that way.

                       

                      3) Use a timed script to perform the search for any barcodes that don't have parents and remove the status indicator from them. This can be fired server-side to avoid interfering with the user's experience.

                      • 8. Re: Script Trigger - Finishing Help Needed
                        lcot17

                        What I have done is chosen an OnObjectKeystroke to set the barcode ID variable and automatically delete the association when the barcode ID is active. This means that the original script sets the association and updates the status field. This script then removes the status from the barcode every time the field is active.

                         

                        I chose Keystroke because if I used Enter, the script only executes every time I click the field. Keystroke only unsets the association if the field changes.

                         

                        This is the script I came up with:

                        OnObjectKeystroke

                        Set Variable [$BarcodeID ; Value : Product Options::_barcodeIDfk]

                        Set Field [ Barcode Pool::Status ; ""]

                        Go to Layout [ original layout ]

                        Go to Field [Product Options::_barcodeIDfk]

                         

                        So far, I have not encountered any issues. Can you foresee any problems with this?

                        Thank you

                        • 9. Re: Script Trigger - Finishing Help Needed
                          Mike_Mitchell

                          You could use OnObjectModify instead of OnObjectKeystroke. That way, it doesn’t fire with every keystroke (a lot of overhead there).

                           

                          And you don’t need the Go to Layout script step. But otherwise, yes, this should be fine.

                          • 10. Re: Script Trigger - Finishing Help Needed
                            lcot17

                            You're right! I'll change it around.. works better the way you suggested.

                            I've also had to add a few scripts to say that if I delete that option record it unassigns the barcode again.

                             

                            Mike_Mitchell - you've been a huge help with this problem I had! Could I possibly trouble you for your thoughts on one more script to further extend the capability of this (perhaps unnecessarily - but it's all in the spirit of learning new things).

                            Next job is to click a button to 'Auto Assign' an available barcode to that option.

                             

                            This is what I have so far - (doesn't work)

                             

                            Go to Layout [ "Barcode Pool" (Barcode Pool) ]

                            Perform Quick Find [ Barcode Pool::Status = ""]

                            Sort Records [ Restore ; No dialog ]

                            Go to Record/Request/Page [ First ]

                            Set Variable [ $BarcodeID ; Value: Barcode Pool::__barcodeIDpk ]

                            Set Field [ Product Options::_barcodeIDfk ; $BarcodeID]

                            // I am executing this from the master product layout via a popup button from the portal records for the product options

                            Go to Layout [ original layout ]

                            Go to Object [ Object Name: "Barcode Select" ]

                            Commit Records/Requests []

                             

                            Currently - according to the script debugger the __productOptionIDpk is unknown so it doesn't know which record to apply it to. Any advice? :O Or should I quite whilst I am ahead haha.

                            Again - i'll upload the DB asis.

                            • 11. Re: Script Trigger - Finishing Help Needed
                              Mike_Mitchell

                              Okay, first off, quit using Quick Find. That searches across all fields on the layout for the search term, and you want just one field. (Quick Find is used for a Google-like search - "find me this value in any field".) Use a construct like this:

                               

                              Enter Find Mode [ ]

                              Set Field [ Barcode Pool::Status ; Value: "=" ]

                              Set Error Capture [ On ]

                              Perform Find [ ]

                              Set Error Capture [ Off ]

                              If ( Get ( FoundCount ) < 1 )

                                   //   Do error trapping stuff here

                              End If

                               

                              The Script Debugger is correct: If you don't have a matching record on the parent side, then you can't set a field. You can solve that by going back to the original layout before you try to set the field. Or, you can create a new relationship from the child to the parent based on a global field and save the product ID prior to navigating to the barcode layout. But that's probably superfluous, simply because you're going back to the original layout anyway.

                              • 12. Re: Script Trigger - Finishing Help Needed
                                lcot17

                                Mike_Mitchell wrote:

                                 

                                Okay, first off, quit using Quick Find.

                                NOTED for future reference!

                                 

                                Mike_Mitchell wrote:

                                 

                                The Script Debugger is correct: If you don't have a matching record on the parent side, then you can't set a field. You can solve that by going back to the original layout before you try to set the field.

                                Done.

                                Got this .... (works on ProductOption layout - but not on the master product layout when clicked from a popover button link)

                                Will continue to investigate )

                                Screen Shot 2016-02-24 at 13.46.53.png

                                • 13. Re: Script Trigger - Finishing Help Needed
                                  lcot17

                                  Screen Shot 2016-02-24 at 14.11.13.png

                                  Script sets everything - but when returns to master Product page it doesn't know which Product Option ID it came from so goes to first record in portal (ID=7). The script was made from ID=21.

                                  What am I missing here to get this to assign the barcode ID to the correct product option. Its works in the product option layout!

                                  • 14. Re: Script Trigger - Finishing Help Needed
                                    Mike_Mitchell

                                    I'm not 100% sure I understand your question. It sounds like you're trying to set a field in a different related table through a portal? Is that right?

                                    1 2 Previous Next