7 Replies Latest reply on Nov 30, 2011 4:16 PM by beverly

    Dynamic Scripting

    Jason_Farnsworth

      Ok so I have not done any scripts that involve loops to keep looking repeating its self until it is out of records to look at.

       

      What I need to do is

       

      Look at my lineItems

      Extract all of the process ID's and all material ID's linked to that lineitem

       

      Then Goto the "PVA Table"

      Get all the Vendor ID's that are linked to each Process ID and each MatID (Both are found in this table some lines have a Process ID some have Material ID but all lines have a Vendor ID)

       

      Then Goto the "BIDREQ Table"

      Create a new Record for each of the Vendors ID's, and with in that record set the fields of the information found, Vendor ID, MatID, ProcessID Etc.

       

       

      I will trigger this script with a button

       

      Thanks in Advance,

       

      Jason

      Midland, TX

        • 1. Re: Dynamic Scripting
          RayCologon

          Hi Jason,

           

          What you are trying to do involves working (or 'walking', as it's commonly called) through a set of records until there are no more to process. As you say, it requires a loop and an exit condition.

           

          The script code for walking a set of records would typically follow the format as set out below:

           

          Go to Layout [ ]

          Go to Record/Request/Page [First]

          Loop

          # Do some processing 'stuff' here...

          Go to Record/Request/Page [Next; Exit after last]

          End loop

           

          This doesn't include the code you'd require to isolate the set of records you need to process - for which you'll be using a Go to Related Record[ ] or Perform Find[ ] step, etc. And, of course, you'll need to add the relevant processing steps for the actions to be performed on each record where I've put the "# Do some processing 'stuff' here..." comment in the above example.

           

          Note that when you choose the "Next" option for the Go to Record/Request/Page[ ] command, a checkbox appears allowing you to also apply the "Exit after last" attribute. It's the "Exit after last" setting that causes the script to exit from the loop when it has reached the last record and there are no more records to step through.

           

          Regards,

          Ray

          ------------------------------------------------

          R J Cologon, Ph.D.

          FileMaker Certified Developer

          Author, FileMaker Pro 10 Bible

          NightWing Enterprises, Melbourne, Australia

          http://www.nightwingenterprises.com

          ------------------------------------------------

          • 2. Re: Dynamic Scripting
            Jason_Farnsworth

            Ray,

             

            Thanks again for the advise, its always a big help.

             

            I have one really novice question for you if you would...

             

            How do you put text comments in between the script line?

             

            Best regards,

             

            Jason

            • 3. Re: Dynamic Scripting
              RayCologon

              Jason_Farnsworth wrote:

              How do you put text comments in between the script line?

               

              Hi Jason,

               

              I assume you mean putting comments within the script code in your solution (as opposed to on the forum...)?

               

              Within a script, you can add one or more lines of text by inserting a special "comment" script step provided for the purpose:

               

              Comment.png

               

              You'll find it listed near the bottom of the Miscellaneous group of script commands at the left of the Edit Script window, as shown in the above image.

               

              Regards,

              Ray

              ------------------------------------------------

              R J Cologon, Ph.D.

              FileMaker Certified Developer

              Author, FileMaker Pro 10 Bible

              NightWing Enterprises, Melbourne, Australia

              http://www.nightwingenterprises.com

              ------------------------------------------------

              1 of 1 people found this helpful
              • 4. Re: Dynamic Scripting
                beverly

                Jason,

                The 'Comment' is a script step and can be placed anywhere within your script. Is that what you meant?

                 

                Also you can "comment" within anything that the calc dialog uses (examples:)

                     = 3 + $variable // this is a 'single-line' comment

                     = FirstName & " " & LastName

                     /* this is the concat of the name -

                     you can use multiple lines to make the comment */

                 

                Beverly

                1 of 1 people found this helpful
                • 5. Re: Dynamic Scripting
                  Jason_Farnsworth

                  Thanks for the "Commet" info I must have looked at the list a hundred times and never saw it.

                   

                  I have this loop working nicely now, it is doing things properly and is quite nice.

                   

                  I do have to add a little complexity to it though.

                   

                  1. There is a senerio when it is looping and performing a find that is cant find the data because it doesnt exist. It stops the script. Would I use and IF statement to get around that? I have looked at them they ask for a calculation. I am not sure how to word this calculation? Basicly if nothing is in the field move on with the loop is what I want.

                   

                  2. I am collecting data from two tables during this loop

                       Table 1 Process ID,LineItemID, VendorID and Vendor Tier

                       Table 2 MaterialID,LineItemID, VendorID and Vendor Tier

                  Then I go out and create a new lineitem in different BidReq table and push all the data to that lineitem.  Currently I am only Pushing Data from the Table 1. This was to test the loop and its results.

                   

                  I need to add the Info from Table 2 which is easy to do via another loop. However there might be some doubling up of the info, for instance The ProcessID might have the same VendorID and Vendor Tier as the MaterialID does for the same lineItemID. So if I go out and just create another loop it will simple just create a new BIDREQ lineItemsID. This will give me two of the same Vendor for Single LineItem (of the main form) if they have matching data.

                   

                  So how do I look for multiple vendors on the same LineItem? Then once found remove all but one? So when I do my printed report it does double the requests. Or is it better to catch the double up before it creates the new record? I would think before it creates the record to avoid having to delete anything. But I think I am back the the IF Statements to resovle it.

                   

                  Thanks again,

                   

                  Jason

                  • 6. Re: Dynamic Scripting
                    RayCologon

                    Jason_Farnsworth wrote:

                    1. There is a senerio when it is looping and performing a find that is cant find the data because it doesnt exist. It stops the script. Would I use and IF statement to get around that? I have looked at them they ask for a calculation. I am not sure how to word this calculation? Basicly if nothing is in the field move on with the loop is what I want.

                     

                    Hi Jason,

                     

                    Assuming you are talking about the Perform Find[ ] script step, then this would normally be approached by using a preceding Set Error Capture[On] step, then testing for the result of Get(LastError) to confirm whether any records were found. When no records are returned from a find, the error code returned is 401 ("no records match the request").

                     

                    So with error trapping in place, the find sequence will look something like this:

                     

                    Set Error Capture [On]

                    Perform Find [Restore]

                    If [Get(LastError) = 401]

                    Exit Script [ ]

                    End If

                     

                    ...where the Exit Script [ ] step is, of course only one of the possible actions you might want the script to take if there are no records matching the find criteria.

                     

                    Jason_Farnsworth wrote:

                    2. I am collecting data from two tables during this loop

                         Table 1 Process ID,LineItemID, VendorID and Vendor Tier

                         Table 2 MaterialID,LineItemID, VendorID and Vendor Tier

                    Then I go out and create a new lineitem in different BidReq table and push all the data to that lineitem.  Currently I am only Pushing Data from the Table 1. This was to test the loop and its results.

                     

                    I need to add the Info from Table 2 which is easy to do via another loop. However there might be some doubling up of the info, for instance The ProcessID might have the same VendorID and Vendor Tier as the MaterialID does for the same lineItemID. So if I go out and just create another loop it will simple just create a new BIDREQ lineItemsID. This will give me two of the same Vendor for Single LineItem (of the main form) if they have matching data.

                     

                    So how do I look for multiple vendors on the same LineItem? Then once found remove all but one? So when I do my printed report it does double the requests. Or is it better to catch the double up before it creates the new record? I would think before it creates the record to avoid having to delete anything. But I think I am back the the IF Statements to resovle it.

                     

                    I agree it's better to avoid creating the records in the first place.

                     

                    I suggest that you have the script build a list (in a local variable) of the vendor and tier for each of the line items it is creating from Table 1. Then when moving on to gather data from Table 2, you can add an If [ ] step that checks if the corresponding item has already been added before adding it.

                     

                    So, in the first loop, you'll need to include a line that gathers the relevant identifying information and appends it to a variable, along the lines of:

                     

                    Set Variable [$BidReqLines; Value: $BidReqLines & ¶ & Table1::VendorID & " " & Table1::VendorTier]

                     

                    Then, in the loop from Table 2, you'll need to add code that checks the $BidReqLines variable before adding each line:

                     

                    If [PatternCount($BidReqLines & ¶; ¶ & Table2::VendorID & " " & Table2::VendorTier & ¶) = 0]

                    #  Add Script code here that adds a new line in the BidReq table

                    End If

                     

                    So, in this case, the If[ ] step calculation is using the PatternCount( ) function to check whether the VendorID and Vendor Tier combination were already added during the processing of Table 1.

                     

                    Regards,

                    Ray

                    ------------------------------------------------

                    R J Cologon, Ph.D.

                    FileMaker Certified Developer

                    Author, FileMaker Pro 10 Bible

                    NightWing Enterprises, Melbourne, Australia

                    http://www.nightwingenterprises.com

                    ------------------------------------------------

                    • 7. Re: Dynamic Scripting
                      beverly

                      Jason, I also will test for "Get ( FoundCount )" within an "if" and branch from there. For example, if the foundcount is 0, show a dialog & go back to the find screen. If the foundcount is 1, show form view. If the foundcount is more than 1, show the list view. I may even want to test for a large set of data: if the foundcount is greater than 25, show dialog "do you want to narrow the search?", etc.

                       

                      Beverly