10 Replies Latest reply on Nov 21, 2013 1:41 PM by philmodjunk

    How do i make a script that makes Multiple Variables from One Field in Multiple Records

    WilliamSouth

      Title

      How do i make a script that makes Multiple Variables from One Field in Multiple Records

      Post

           I'm making an invoice database  that incorporates a Purchase Order System for special orders.

           So in my Line Item Data Table I have a serial number field(:pf_order_No ) for each line item. Which is related to my Purchase Order Table

           Some line items will be special order, Lay Away or Taken. The only records that i will need to be made will be the "To Be Ordered"

           So multiple records will be  made in my Purchase Order Table which will be determined through each invoice and the line items in it.    

           My question is how do i make a script that makes Multiple Variables from One Field in Multiple Records.

           Below is a simple script that works for only the first Line item. I could duplicate the script for each record changing $INVOICEx

      But that becomes inefficient, and on my purchase order side duplicate records.

      Go to Record/Request/Page [ First ]
      Set Variable [ $$INVOICE_ID; Value:Invoices::Invoice ID ]

      If [ Invoice Data::TBO LWY TKN="To Be Ordered" ]

      Set Variable [ $INVOICE1; Value:Invoice Data::pf_order_No ]

      Go to Record/Request/Page[ Next ]

      Else
                          Go to Record/Request/Page
      [ Next ]

      End If

      Go to Layout [ “Print Bridesmaid PO” (Purchase Orders PO) ]

      If [ $INVOICE1 "" ]

      New Record/Request
                          Set Field
      [ Purchase Orders PO::kf_order_No; $INVOICE1 ]

      Commit Records/Requests

      End If

                          Hope I explained it right, thanks in advance

            

        • 1. Re: How do i make a script that makes Multiple Variables from One Field in Multiple Records
          philmodjunk

               You need to pull up a found set of the invoicedata records and loop through them.

               If  [ Not Isempty ( Invoice Data::Invoice ID ) //make sure that related records exist ]
                  Freeze Window
                  Set Variable[ $$INVOICE_ID; Value:Invoices::Invoice ID ]
                 
          Go To Related Record [Show only related records; From table: Invoice Data; Using layout: "Invoice Data" (Invoice Data ) ]
                   Enter Find mode []
                   Set field [Invoice Data::TBO LWY TKN ; "To Be Ordered" ]
                   Constrain Found Set []
                   Go to Record/Request/Page [first]
                   Loop
                       Set Variable[ $INVOICE1; Value:Invoice Data::pf_order_No ]
                       Go to Layout[ “Print Bridesmaid PO” (Purchase Orders PO) ]
                       New Record/Request
                  Set Field[ Purchase Orders PO::kf_order_No; $INVOICE1 ]
                       Go to layout[ "Invoice Data" (Invoice Data ) ]
                       Go to Record/Request/Page [next ; exit after last]
                   End Loop
                   Go to Layout [original layout]
               End If

               Note: sometimes go to layout can trip a number of script triggers which then perform scripts that can slow or keep your script from running correctly. Sometimes you need to add some extra code and a global variable to keep the triggered scripts from interfering with this type of script.

          • 2. Re: How do i make a script that makes Multiple Variables from One Field in Multiple Records
            WilliamSouth

                 Yes this works perfectly, but i can see how script triggers could slow it down. As of right now I don't have any so i should be Ok for now. Thanks again

            • 3. Re: How do i make a script that makes Multiple Variables from One Field in Multiple Records
              WilliamSouth

                   Now I'm having an issue with, if the invoice DOES NOT contain any line items that need "To Be Ordered" a dialog box comes up from the find procedure "No records match this criteria".

                   The Set Error Capture [on] works to get rid of the Dialog box, but now the payment procedure dose not work correctly.

                   SO then I made an if statement, which works partially only if the first line item is " To be Order " product or all items don't need to be ordered

                   Hope this makes sense...

                   see below

              If [ Invoice Data::TBO LWY TKN "To Be Ordered" ]

              Perform Script [ “Make payment” ]
              Else If [ not IsEmpty ( Invoice Data::INVOICE ID MATCH FIELD) ]

              Freeze Window

              Set Variable [ $$INVOICE_ID; Value:Invoices::Invoice ID ]

              Go to Related Record [ From table: “Invoice Data”; Using layout: “Invoice Details | iPad” (Invoice Data) ] [ Show only related records ]

              Set Error Capture [ On ]

              Enter Find Mode [ ]

              Set Field [ Invoice Data::TBO LWY TKN; "To Be Ordered" ]

              Constrain Found Set [ ]

              Go to Record/Request/Page[ First ]

              Loop

              Set Variable [ $INVOICE1; Value:Invoice Data::pf_order_No ]

              Go to Layout [ “Print Bridesmaid PO” (Purchase Orders PO) ]

              New Record/Request

              Set Field [ Purchase Orders PO::kf_order_No; $INVOICE1 ]

              Go to Layout [ “Invoice Details | iPad” (Invoice Data) ]

              Go to Record/Request/Page

              [ Next; Exit after last ]

              End Loop

              Perform Script [ “Make payment” ]

              End If

                    

                    

              • 4. Re: How do i make a script that makes Multiple Variables from One Field in Multiple Records
                philmodjunk

                     If  [ Not Isempty ( FilterValues ( List ( Invoice Data::TBO LWY TKN) ;"To Be Ordered"  ) )//make sure that To Be Ordered records exist ]
                        Freeze Window
                        Set Variable[ $$INVOICE_ID; Value:Invoices::Invoice ID ]
                       
                Go To Related Record [Show only related records; From table: Invoice Data; Using layout: "Invoice Data" (Invoice Data ) ]
                         Enter Find mode []
                         Set field [Invoice Data::TBO LWY TKN ; "To Be Ordered" ]
                         Constrain Found Set []
                         Go to Record/Request/Page [first]
                         Loop
                             Set Variable[ $INVOICE1; Value:Invoice Data::pf_order_No ]
                             Go to Layout[ “Print Bridesmaid PO” (Purchase Orders PO) ]
                             New Record/Request
                        Set Field[ Purchase Orders PO::kf_order_No; $INVOICE1 ]
                             Go to layout[ "Invoice Data" (Invoice Data ) ]
                             Go to Record/Request/Page [next ; exit after last]
                         End Loop
                         Go to Layout [original layout]
                Else
                         Perform Script
                [ “Make payment” ]
                     End If

                • 5. Re: How do i make a script that makes Multiple Variables from One Field in Multiple Records
                  WilliamSouth

                       The script did not work correctly until i added this to the top of the script

                  Sort Records by Field [ Descending; Invoice Data::TBO LWY TKN ]

                  Go to Record/Request/Page [ First ]

                                      any suggestions?

                        

                  • 6. Re: How do i make a script that makes Multiple Variables from One Field in Multiple Records
                    philmodjunk

                         That makes no sense.

                         At the "top of the script" you should not even be on the Invoice Data layout, but on a layout for Invoices.

                    • 7. Re: How do i make a script that makes Multiple Variables from One Field in Multiple Records
                      WilliamSouth

                           Don't know what to tell ya, All i know is when I take that top bit of script off. It dose not populate any of the PO records,  it will only do it if the first line item is a "To Be Ordered"  Record.  But if it sorts the (Invoice Data::TBO LWY TKN) so that the "To Be Ordered" Records sort to the First Line Item it works fine.

                      • 8. Re: How do i make a script that makes Multiple Variables from One Field in Multiple Records
                        philmodjunk

                             What layout is current when the script is run? It should be invoices.

                             Do you expect this script to do this for a single invoice or a group of invoices?

                        • 9. Re: How do i make a script that makes Multiple Variables from One Field in Multiple Records
                          WilliamSouth

                               The Current Layout is Invoice Data, Which line items are related to the invoice.  If you see the sample file that comes with filemaker 12 Named invoices. Layout: Invoice Details | iPad.  That is where the inspiration came from.

                               I expect this script to be to the line item records, which could be one or multiple line items.

                          • 10. Re: How do i make a script that makes Multiple Variables from One Field in Multiple Records
                            philmodjunk
                                 

                                      The Current Layout is Invoice Data,

                                 And there's the problem. The script that I have posted should be run from the layout for Invoices, not Invoice data. It will change to the layout for Invoice Data in order to loop through the correct group of records for a single invoice.

                                 The step:

                                 Go to Layout [original layout]

                                 then returns you to the Invoices layout.

                                 But I am concerned that this may create an issue for  this line:

                            Perform Script[ “Make payment” ]

                            Since I have been assuming that a payment should be applied to the entire invoice, not an individual line item in Invoice Data.