11 Replies Latest reply on Feb 12, 2010 2:49 PM by philmodjunk

    What is wrong with this Script



      What is wrong with this Script


      Table one: BankData

           Fields:  BankPayee - Text

                      BankReportPayee - Text


      Table two: Payees_W_Cats

            Fields: WherePayee - Text

                       Reporting Payee - Text


      BankPayee is a long string of text numberic and dates

      BankReportPayee is Blank


      WherePayee is a short string contained within Bankpayee

      ReportingPayee is that data I want to post to BankReportingPayee


       I tried this Script.  It runs, but does not update BankReportingPayee


      Set Field[BankData::BankReportPayee; If ("*" & BankData::BankPayee & "*" = Payees_W_Cats::WherePayee; Payees_W_Cats::ReportingPayee ; "")


      What did I do wrong?

        • 1. Re: What is wrong with this Script
          Steve Wright

          I think you need to use the script step like this


          Set Field[BankData::BankReportPayee; case ( PatternCount(BankData::BankPayee ; Payees_W_Cats::WherePayee ) ;  Payees_W_Cats::ReportingPayee ) ]



          If I read correctly, this will check for a pattern in the field

          BankData::BankPayee  (which contains for example : 2873297ABC239843)

          For the value contained in Payees_W_Cats::WherePayee (i.e  ABC)  


          If a match is found, then it will set the field.



          • 2. Re: What is wrong with this Script


            gsbilsky wrote:

            BankPayee is a long string of text numberic and dates

            WherePayee is a short string contained within Bankpayee




            Let's say that BankPayee is: ABC12345XYZ

            WherePayee is: 345X

            "*" & BankData::BankPayee & "*" = Payees_W_Cats::WherePayee


            *ABC12345XYZ* = 345X, which is    NOT TRUE


            If you are looking for a pattern, then look into the text functions such as: Filter, PatternCount, Position, Right, Left, etc.



            • 3. Re: What is wrong with this Script

              Wrote the script and got error needed operator.  I changed the first ; after BankReportPayee; to a =


              Saved the script and ran it.  Nothing happened.  If I entered the BankReportPayee field and used Command1 keys the number 1 showed up in the field.


              What did or am I doing wrong?

              • 4. Re: What is wrong with this Script
                   You can't user * as a wild card in your if statement. It will be treated as the actual * character instead. As suggested by the others, look up the text functions like patterncount and others to find one that will do what you need.
                • 5. Re: What is wrong with this Script

                  gsbilsky wrote:   ( from other thread )

                  Does anyone have both Access and FM experience that can convert the folloing SQL to script.

                   <!--  [if gte mso 9]&gt;&lt;xml&gt; &lt;o:DocumentProperties&gt;   &lt;o:Template&gt;Normal&lt;/o:Template&gt;   &lt;o:Revision&gt;0&lt;/o:Revision&gt;   &lt;o:TotalTime&gt;0&lt;/o:TotalTime&gt;   &lt;o:Pages&gt;1&lt;/o:Pages&gt;   &lt;o:Words&gt;36&lt;/o:Words&gt;   &lt;o:Characters&gt;209&lt;/o:Characters&gt;   &lt;o:Lines&gt;1&lt;/o:Lines&gt;   &lt;o:Paragraphs&gt;1&lt;/o:Paragraphs&gt;   &lt;o:CharactersWithSpaces&gt;256&lt;/o:CharactersWithSpaces&gt;   &lt;o:Version&gt;11.1282&lt;/o:Version&gt; &lt;/o:DocumentProperties&gt; &lt;o:OfficeDocumentSettings&gt;   &lt;o:AllowPNG/&gt; &lt;/o:OfficeDocumentSettings&gt; &lt;/xml&gt;&lt;![endif]  --><!--  [if gte mso 9]&gt;&lt;xml&gt; &lt;w:WordDocument&gt;   &lt;w:Zoom&gt;0&lt;/w:Zoom&gt;   &lt;w:DoNotShowRevisions/&gt;   &lt;w:DoNotPrintRevisions/&gt;   &lt;w:DisplayHorizontalDrawingGridEvery&gt;0&lt;/w:DisplayHorizontalDrawingGridEvery&gt;   &lt;w:DisplayVerticalDrawingGridEvery&gt;0&lt;/w:DisplayVerticalDrawingGridEvery&gt;   &lt;w:UseMarginsForDrawingGridOrigin/&gt; &lt;/w:WordDocument&gt; &lt;/xml&gt;&lt;![endif]  --><!--  /* Font Definitions */ @font-face      {font-family:&quot;Times New Roman&quot;;      panose-1:0 2 2 6 3 5 4 5 2 3;      mso-font-charset:0;      mso-generic-font-family:auto;      mso-font-pitch:variable;      mso-font-signature:50331648 0 0 0 1 0;} /* Style Definitions */ p.MsoNormal, li.MsoNormal, div.MsoNormal      {mso-style-parent:&quot;&quot;;      margin:0in;      margin-bottom:.0001pt;      mso-pagination:widow-orphan;      font-size:12.0pt;      font-family:&quot;Times New Roman&quot;;} table.MsoNormalTable      {mso-style-parent:&quot;&quot;;      font-size:10.0pt;      font-family:&quot;Times New Roman&quot;;} @page Section1      {size:8.5in 11.0in;      margin:1.0in 1.25in 1.0in 1.25in;      mso-header-margin:.5in;      mso-footer-margin:.5in;      mso-paper-source:0;} div.Section1      {page:Section1;}   --><!--  StartFragment  -->

                  UPDATE tbl_BankData INNER JOIN tbl_ReportPayeesWCats ON [tbl_BankData].[bd_Payee]

                  Like "*" & [tbl_ReportPayeesWCats].[P_Where] & "*" SET tbl_BankData.bd_ReportPayee = [tbl_ReportPayeesWCats].[p_Name]

                  WHERE ((([tbl_BankData].[bd_ReportPayee]) Is Null));


                  Perhaps you can try something like this? 



                  Enter Find Mode [ ]

                  Set Field [ bd_ReportPayee; "=" ]

                  Set Error Capture [ On ]

                  Perform Find [ ] // finds where bd_ReportPayee is null

                  If [ Get ( FoundCount ) ]

                  Replace Field Contents [ bd_ReportPayee; Case ( PatternCount ( bd_Payee; P_Where ); p_Name ) ]

                  End If


                  • 6. Re: What is wrong with this Script

                    Boy can I come up with them.


                    I downloaded Filemaker 8 Functions Reference Guide.  234 pages of Functions.  I found one (FilterValues) I want to try.  Started to write if statment in Scripts and tried to use FilterValues and got error that FilterValues was not a function.  How do I write scripts with functions not found on the Script list?

                    • 7. Re: What is wrong with this Script
                         Script steps and functions are tow totally different things. FilterValues () is most definitely available. For example, if you were using Set Variable or Set Field script steps, you can use FilterValues in the calculated results part. Try it out.
                      • 8. Re: What is wrong with this Script

                        Did I write this correctly.


                        Set Field[If(FilterValues(BankData::BankPayee;Payees_W_Cats::WherePayee)=Payees_W_Cats::WherePayee;Payees_W_Cats::ReportingPayee;"")]


                        It is in the Calculated result: Specify


                        I run the Script and nothing happens.


                        Hopefully someday I will understand what I am doing.


                        • 9. Re: What is wrong with this Script

                          Many new users get confused the first time they try to add Set Field to a script. See the following link for step by step instructions: (Scroll down towards the end of the thread.)

                          Selecting an "All" option with checkboxes


                          (You have two specify... buttons and you have to click both of them.)

                          • 10. Re: What is wrong with this Script

                            Have gotten closer.  My script works somewhat.


                            My script reads:


                            Set Field [BankData::BankReportPayee; If(BankData::BankPayee = Payees_W_Cats::WherePayee; Payees_W_Cats::ReportingPayee; "Opps")


                            The text in BankData::BankPayee and Payee_W_Cats::WherePayee have been made to match exactly.


                            When I run the script I only get "Opps"


                            If I replace Payees_W_Cats::wherepayee in the above statement with the exact info from BankPayee it works.


                            Why asking for data from another Table causes it only return False statement?


                            I have checked over and over to make sure the text in both fields match.



                            Quick second question.  When the script works I have to be in BankData::BankReportingPayee.


                            How can I update all records at once?

                            • 11. Re: What is wrong with this Script

                              If you are getting "Opps" then the value in BankData::BankPayee does not equal the first related record with Payees_W_Cats::WherePayee


                              I assume that the current layout lists BankData as its TableOccurrence?


                              Try placing a portal to payees_W_Cats on this layout, make it several rows tall and place WherePayee in the portal row. Do not specify a sort order for the portal. The value in the first row is the value your script step will refer to in the calculation. If the values in the two fields look like they should be equal. Click into them and see if there are any invisible characters that are present in one field and not the other such as a space or a carriage return.