13 Replies Latest reply on Mar 12, 2014 11:23 AM by Stephen Huston

    Pull Freight value from Line Item into Invoice Table

    jmsinnz

      I want to find the ExtendedPrice of a line item in an invoice (if the code = "Freight"), and place that value into a FreightComponent in the Invoice table. I'm having a blank moment.

        • 1. Re: Pull Freight value from Line Item into Invoice Table
          Stephen Huston

          One could do this several ways:

          1. script walking the line-items portal until you get to the freight item, grab that amount in a variable and insert it in the invoice field. (scripting only)
          2. add a TO which uses the line-items table but uses a constant in the Invoice table = "freight", and then grab the value across that relationship. (special TO and scripting required)
          3. use a separately-named one-row portal of the line-items filtered to show only the freight items, and grab the value from the first match in that portal. (special filtered portal and scripting required)
          1 of 1 people found this helpful
          • 2. Re: Pull Freight value from Line Item into Invoice Table
            jmsinnz

            Thank you for that! That was just the help I needed.

            I have a script that does the job. I'm now wondering the best place to call/trigger this.

             

            1. The ideal: triggers when user chooses Freight from the product drop-down in a new line item
            2. Also would work when exiting the record - ie when the user goes from here to another layout.
            • 3. Re: Pull Freight value from Line Item into Invoice Table
              Stephen Huston

              I suggest a layout-based trigger which runs onRecordCommit. That way it always checks the current group of line-item records after any edits.

               

              Keep in mind that onRecordCommit runs before the commit completes, so, if you have changed line-items which might not be committed until the parent record commits, the script may need to flush cache to disk, check whether the target value is already correct and reset it only if it needs updating, then be sure to exit script with a script result = "1" to let the commit complete properly.

               

              I have used the same type of trigger to set the final totals of invoices based on line-items so that the invoice total can be stored-- speeds up a lot of reports over an unstored calc/sum field in the parent invoice.

              • 4. Re: Pull Freight value from Line Item into Invoice Table
                jmsinnz

                Hi Stephen,

                 

                I get it. That's elegant.

                I'm very grateful for you taking the time to jump in and help me out.

                 

                Thank you!

                • 5. Re: Pull Freight value from Line Item into Invoice Table
                  jmsinnz

                  Hhmmm, I thought that was going to be easier...  I'm kind of at a bit of a loss with:

                   

                  • flush cache to disk,
                  • check whether the target value is already correct
                  • and reset it only if it needs updating,
                  • then be sure to exit script with a script result = "1" to let the commit complete properly.

                   

                  Please, let me know if I am asking too much?

                  I'm grateful for what I've learnt from you already, and don't wish to take advantage of your good will.

                  • 6. Re: Pull Freight value from Line Item into Invoice Table
                    Stephen Huston

                    In the Layout Setup dialog, set the layout to have an onRecordCommit script triggered which reads roughly like this: (script steps in bullets)

                    • Set Variable [ $freightamount ; "0" ]
                    • Flush Cache to Disk
                    • Go to Object [yourportalname here]
                    • Got to Portal Row [1]
                    • Loop
                    • If portalrow_typefield = "Freight"
                    • Set Variable [ $freightamount ; amountfield ]
                    • End If
                    • Exit loop if [ (portalrow_typefield = "Freight") or isEmpty (fKeyFromInvoice, empty portal row) ]
                    • Go to Portal Row [ Next ; Exit after Last ]
                    • End Loop
                    • If [ Invoice::freight ≠ $freightamount ]
                    • Set Field [ Invoice::freight ≠ $freightamount ]
                    • End If
                    • Exit Script [Result: "1"]

                     

                    I'm doing this from memory, but I think that should cover it.

                    The sequence is to clear the amount (to 0) so that no value will be added for freight if there is not such item, then walk the portal to locate a freight value, and set that in the $variable, then exit the loop which walked the portal, reset the invoice freight value if it's no longer correct, and then let the record commit.

                    • 7. Re: Pull Freight value from Line Item into Invoice Table
                      Scripts

                      Thanks for showing some script steps! 

                      • 8. Re: Pull Freight value from Line Item into Invoice Table
                        jmsinnz

                        Thank you Stephen,

                         

                        I've enabled all that, and it works, except that when I run the Debugger, I can see the script cycling in an endless loop...

                         

                        Copy_FreightAmount_to_Invoice.jpg

                        • 9. Re: Pull Freight value from Line Item into Invoice Table
                          jmsinnz

                          I set the trigger to be OnLayoutExit instead and it works beautfully :-)

                          It's not perhaps quite as elegant, and requires some trust from the user, to know that the Freight figure will get extracted, or updated.

                          • 10. Re: Pull Freight value from Line Item into Invoice Table
                            erolst

                            Scripts wrote:

                            Thanks for showing some script steps! 

                            Many small and easily downlodable sample files are being posted in this forum (and others), and on a multitude of web sites, so there is not exactly a shortage of script code to study …

                             

                            As to the OP's requirement: starting from the reasonable assumption that every line item has a product code and a price/amount, here is an approach that only takes three steps one step (for the core logic) and operates purely in memory (except for actually writing to the field).

                             

                            # feel free to add a script comment here …

                            Set Field [ Invoice::freightCost ;

                            Let ( [

                            codeList = List ( T12etc::id_product ) ;

                            // get a list of related codes; assumes all line items have non-empty amount and code fields (since List() ignores empty values)

                            pos = Position ( ¶ & codeList & ¶ ; "¶Freight¶" ; 1 ; 1 )

                            // find (first) position of code "Freight" in list of related codes, if any (and it may be a good idea to add “Freight” to your product table, so it has a unique, unchangeable ID …)

                            ] ;

                            Case ( not pos ; 0 ; GetValue ( List ( T12etc.::extendedAmount ) ; ValueCount ( Left ( codeList ; pos ) ) ) )

                            // if there is no "Freight" in the code list, use 0, otherwise take amount from same list position as code

                            ) ]

                            • 11. Re: Pull Freight value from Line Item into Invoice Table
                              Stephen Huston

                              1. Just a cautionary note:

                              I assume that the onLayoutExit  trigger is working because you have allowed no way to navigate between invoices without leaving that layout, otherwise that trigger won't be be reliable for each visited invoice.

                               

                              2. Re the reason for testing the value before setting it: this way the record's modification date won't get updated just because this script executed unless a change is actually required to the value.

                              • 12. Re: Pull Freight value from Line Item into Invoice Table
                                jmsinnz

                                Yes, you're quite correct. So OnCommit would be a better option, but using that put the script into an endless loop.

                                • 13. Re: Pull Freight value from Line Item into Invoice Table
                                  Stephen Huston

                                  I see your script is using the Go to Record {Next, etc] step instead of the Go to Portal Row [Next, exist after last]. That's probably the trouble in the loop.