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

    What is wrong with this Script

    gsbilsky

      Title

      What is wrong with this Script

      Post

      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
            mrvodka
              

             


            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
              gsbilsky
                

              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
                philmodjunk
                   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
                  mrvodka
                    

                  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
                    gsbilsky
                      

                    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
                      mrvodka
                         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
                        gsbilsky
                          

                        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
                          philmodjunk
                            

                          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
                            gsbilsky
                              

                            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
                              philmodjunk
                                

                              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.