9 Replies Latest reply on Mar 9, 2017 9:13 AM by jeffsb

    Script running in portal

    jeffsb

      This question relates to a solution that is in the customer, invoice, lineitem, products pattern.

       

      I am writing a button script that creates a standard order of two items. It starts from the customer layout.  The script then goes to the invoice layout and then goes to the first field of the first portal row and picks the the appropriate item, in the form of the product foreign key in the lineitem table for the item..

       

      All that works fine. The problem is. when the script tries to place the second item in this standard order it overwrites the first portal row. I tried placing the command "go to the next portal row" in the script but that doesn't seem to work. I also tried "go to the next field" several times as a kind of tab, to tab over to the first item in the next portal row but that doesn't work either.  In both cases it simply overwrites the first product in the first portal row. When doing this manually, you just tab over to the second row -- is there some way to have the script tab over to the second portal row?

       

      How can I specify that the appropriate item be placed in the second portal row?

       

      Any help with this would be appreciated!

        • 1. Re: Script running in portal
          philmodjunk

          The best approach is to avoid interacting with the portal at all and just work from the set of related records shown in the portal.

           

          What you want can be made to work, you probably need to give your portal an object name and use go to object to put the focus on the correct portal before using go to portal row to put the focus on a row in that portal. But this approach is "brittle". It can be easily broken by layout changes that might be made at a later date. And since it's pretty easy to get the job done without looping through portal rows, it's better not to use that approach.

           

          Note that you can set a variable to a list of values from the portal's table using either the List or ExecuteSQL functions and then you can loop through the values in the variable instead of looping through the physical portal rows.

           

          And if you then need to create records in a related table, take a look at the MagicKey method for doing so as it avoids having to go to the last, "add row" of a portal in order to add a record to a related table. You can web search that ter to find a number of good articles on the method.

          • 2. Re: Script running in portal
            StephenWonfor

            Jeff

             

            Phil is correct about portal peril.  Best to spawn a window into the portal context and do operations there - reliable.


            Stephen

            • 3. Re: Script running in portal
              philmodjunk

              Except that there's no reason to open a new window for this purpose that I can see here. There are ways to avoid even opening up that extra window.

              • 4. Re: Script running in portal
                jeffsb

                Well, Philmodjunk, you gave it seems like about four different approaches to solving this problem, though being a beginner I don't immediately understand any of them. I'm sure if I give it a little time I can figure something out and when I do or if I have any questions I'll be back. I do appreciate your effort in providing your answer, which is a good starting point!

                • 5. Re: Script running in portal
                  philmodjunk

                  Really only one in this discussion. I did suggest why your script was not working but recommended against using that script in the first place.

                   

                  The other things that I mentioned here are two variations of the same method. I had to keep my response very general as you haven't described very much about your actual solution--in particular, the tables and relationships involved.

                  • 6. Re: Script running in portal
                    jeffsb

                    Well, here is more information on the solution -- I appreciate your suggestions:

                     

                    It is a simple system, in the customers,Orders,Lineitems, Products, pattern, except that we are a non-profit and don't sell anything. In our solution,

                    People=Customers

                    Requests=Orders

                    Offerings = Products

                     

                    The "invoice" only has three fields per line (no price):

                    Item#

                    (Lineitems::idfkOfferings)

                    Item Name

                    (offerings::itemName)

                    Quantity

                    (Lineitems::quantity)

                     

                    Here are the TO's:

                     

                     

                     

                    Here is what the invoice portal looks like:

                     

                                   item#                              item name                    quantity

                    • 7. Re: Script running in portal
                      philmodjunk

                      I am writing a button script that creates a standard order of two items. It starts from the customer layout.  The script then goes to the invoice layout and then goes to the first field of the first portal row and picks the the appropriate item, in the form of the product foreign key in the lineitem table for the item..

                       

                      After re-reading that, two questions come to mind that you need to think about, but which I can put aside for the moment:

                      Does every person get the same two line items in their Request?

                      Might these "standard items" change in the future?

                       

                      As a historical note: I set up a recycling business so that each weight tag (actually a purchase order) initially lists the same 4 items that represent about 80% of the used beverage containers that are brought in so as to save data entry time, so what you want here is very familiar.

                       

                      This is going to be a working example of the MagicKey method for creating records in a related table. I'm also assuming that when you click that button in People, you first want to create a new Request record before populating the Request's line items portal with two "standard items".

                       

                      In LineItems, add a new field if you do not already have it:

                      idpkLineItems. Set it to auto-enter a serial number or UUID.

                       

                      Select LineItems and click the duplicate button to make a new "Occurrence" (Box) that also refers to line items. You can double click this new occurrence box to get a dialog where you can rename it: LineItems|MagicKey.

                       

                      Add a new field to Requests, idfkSelectedOffering. Make it the same data type as idpkLineItems.

                       

                      Link Requests to LineItems|MagicKey by these two new id fields. Double click the relationship line and enable the "allow creation..." option for LineItems|MagicKey.

                       

                      Now you can write a script to create a New Request and select a standard two offerings for it:

                       

                      #Get the ID of the person needing a new request

                      Set Variable [$PeopleID ; value: People::idpkPeople ]

                      #Create the new request and link it to the right People record

                      Go to Layout ["Request" ; (Request)]

                      New Record/Request

                      Set field [Request::idfkPeople ; $PeopleID ]

                      #Set up a list of the IDs of the items to add

                      Set Variable [$OfferingIDs ; Value: List ( 1 ; 2 ) ]

                      #Use MagicKey to create the line item records and link them to the current Request record

                      Loop

                         Set Variable [$K ; value: $K + 1 ]

                         Exit Loop If [ $K > ValueCount ( $OfferingIDs ) ]

                         #clear the match field in Request to make sure that a new record is created in Line Items

                         Set Field [ Request::idfkSelectedOffering ]

                         Set Field [ LineItems|MagicKey:: idfkRequest ; Request::idpkRequest ]

                         Set Field [ LineItems|MagicKey::idfkOfferning ; GetValue ( $offeringIDs ; $k ) ]

                      End Loop

                      Commit Records

                       

                      The line in Bold may need to be changed. From your screen shot, I am assuming that the standard two items have IDs of 1 and 2. If not, use the correct values in place of those two numbers in the list function. Once this is working, it's possible to set up a field in Offerings that designates a given offering as one of the standard items and then this particular script step can be modified to pull up the needed list of IDs using ExecuteSQL in order to get a list that can be updated in the future by updating fields in the Offerings table.

                       

                      You might want to WebSearch "MagicKey" to learn more about it

                       

                      PS. after reading the details that you've shown here, I find myself reciting parts of the Serenity Prayer--assuming that there are 12 steps in that "steps" item...

                      • 8. Re: Script running in portal
                        jeffsb

                        Well this is exciting! Thank you, Philmodjunk. It is late now but tomorrow I plan to make a full effort to get this working. No, everyone does not request the same two line items in the standard request. But we have many, many requests for those two items and this button is to create an easy way to process such requests, sometimes coming many times a day.. We actually have about 12 different items that we carry, but many people know about this particular offer so a good percentage of the people contacting us want it. And no, I do not expect this standard order of these same two items to change in the future. We have been around for over 30 years and for all that time these two items that we ship together have been by far our most requested. We are a sort of one trick pony -- we offer the same books, booklets and DVD's year in year out.

                         

                        I look forward to working on this tomorrow and will give a report on progress. Again thank you Philmodjunk! .

                        • 9. Re: Script running in portal
                          jeffsb

                          Philmodjunk, earlier in this thread you said:

                           

                          "What you want can be made to work, you probably need to give your portal an object name and use go to object to put the focus on the correct portal before using go to portal row to put the focus on a row in that portal. But this approach is "brittle". It can be easily broken by layout changes that might be made at a later date. And since it's pretty easy to get the job done without looping through portal rows, it's better not to use that approach."

                           

                           

                          I thought, well I'm going to try your first solution, giving the portal a name, a try just to see how it works. I researched how you can give an object a name with the inspector and that's what I did. Then in the script I named the portal and lo and behold, the first script that I started this thread about worked perfectly!

                           

                          I definitely want to learn this new technique the you gave about the MagicKey, but since your naming the portal solution worked, I'm going to use that for now until I have time to explore the method you outlined in your latest post.

                           

                          I'm going to give the first solution the "correct answer" choice. When I get your second solution working, plan to return and make that the correct answer!

                           

                          Thank you again, Philmodjunk.