13 Replies Latest reply on Nov 9, 2012 3:26 AM by Sorbsbuster

    Script - Replace Field Contents with multiple criteria

    ChrisSmythe

      Title

      Script - Replace Field Contents with multiple criteria

      Post

           Hello, I am running FileMaker Pro v12 on Windows XP and have the following script problem.  Could someone please advise on how I can fix my Replace Field Contents to do the following:

           I need the DbUserEdits::LetterSent field to change from value: "No" to value: "Yes" whereby:

           DbUserEdits::LetterSent = "No" and DbUserEdits::DeploymentStatus = "Pending" or "Deployed" and Personnel::Service = "Army"

           Please refer to my attached screen-shot.

           Kind regards,

           Chris :)

            

      Script_5.jpg

        • 1. Re: Script - Replace Field Contents with multiple criteria
          philmodjunk

               If your find is pulling up the correct records, all you need for replace field contents is:

               Replace Field Contents [ No dialog ; DbUserEdits::LetterSent ; "Yes" ]

               Does your script find the correct set of records? I can read your posted criteria more than one way. I read it one way and your scripted find criteria is correct. I read it another way and it is not.

               Your script finds two groups of records and combines them into a single found set.

               Group 1: All records where deploymentstatus is "pending"

               Group 2: All records where DeploymentStatus is "Deployed", Service is "Army" AND LetterSent is "No"

          • 2. Re: Script - Replace Field Contents with multiple criteria
            ChrisSmythe

                 Hello PhilModJunk, thank you for your kind assistance and help - greatly appreciated.

                 I did as you suggested and changed my Replace Field Contents as follows:

                 Replace Field Contents [ No dialog ; DbUserEdits::LetterSent ; "Yes" ]

                 I am unsure that my script finds the correct set of records given that you can read it in another way.

                 It appears to work - but that could be "good-luck" rather than good-design.

                 I would very much appreciated your feedback on my script design.

                 Please refer to attached screen-shot.

                 Kind regards,

                 Chris:)

                  

            • 3. Re: Script - Replace Field Contents with multiple criteria
              philmodjunk

                   I spelled out what records are found in my last post. When I talk of "two groups" of records, I'm telling you what your script actually does. What I am unsure of is whether or not that is the group of records that you want to modify in this fashion.

                   One test you can do is to remove the replace field contents step and just run the part of the script that finds records and then you can inspect the records found to see if they are the correct ones to modify. You can then select Show Omitted Only to inspect the records that were not found to see if they are the ones that should be excluded or not.

              • 4. Re: Script - Replace Field Contents with multiple criteria
                ChrisSmythe

                     Hello PhilModJunk, sorry about my last post, I did not really understand about the two groups of records.  I understand a little better now.

                     I did as you suggested and ran my script without the Replace Field Contents step to inspect the records found.

                     Interestingly, the found set contained  the DbUserEdits::DeploymentStatus equal to "Deployed", "Pending" and "Not Deployed" whereby Personnel::Service = "Army" and DbUserEdits::LetterSent = "No".

                     I was not expecting the found set to contain any records relating to:

                     DbUserEdits::DeploymentStatus = "Not Deployed".

                     Could you please advise on how I can fix this.

                     Kind regards,

                     Chris :)

                • 5. Re: Script - Replace Field Contents with multiple criteria
                  Abhaya

                       Hi Cris,

                       Please go through the below steps...

                       Go to Layout

                       Enter find mode

                       set field[status=pending]

                       set field[service=Army]

                       set field[LetterSent=No]

                       New Record Request

                       set field[status=Deployed]

                       set field[service=Army]

                       set field[LetterSent=No]

                       Perform find

                       Export records

                       Replace field content[letter Sent= "Yes"]

                       Showall Record

                       In above we have defind 2 groups with "OR" condition means record will fetch from database  by the above condition.

                       But in your previuos script no doubt you had 2 group but one is only for status where as other is for (status,lettersent & service) that's why it was showing "Not Deployed" records.

                       Hope it will work at your end.

                       Thanks

                       Atauf

                        

                  • 6. Re: Script - Replace Field Contents with multiple criteria
                    ChrisSmythe

                         Hello Atauf, thank you for your help - very much appreciated.  I implemented your suggestions and still have the same problem as detailed in my last post.

                         I believe that we may have defined the 2 groups with possibly an "AND" condition?

                         I do not understand how we know if we have defined the 2 groups with either: an "OR" condition or an "AND" condition?

                         Please find attached a screen-shot of my script below.

                         Have I done something wrong or ommitted something?

                         Kind regards,

                         Chris :)

                          

                    • 7. Re: Script - Replace Field Contents with multiple criteria
                      Sorbsbuster

                           Your latest script is going to find one set of records (as Filemaker always does).  It will be made up of records which have all these 3 entries on their particular record:

                           Deployment Status = Pending as well as Service = Army as well as Letter Sent = No

                           but as well as that they also qualify to be in the set if they have all 3 of these entries on their record:

                           Deployment Status = Deployed as well as Service = Army as well as Letter Sent = No

                           -----

                           The confusion is in the difference between the strict AND and OR of Boolean logic and the loosely-termed 'and' and 'or' of Englsh.
                           When you define an AND search in Filemaker (or any other database) you mean that in order to prove to be a record you want it has to meet this condition AND that condition AND this other condition, all at the same time.

                           An OR search means that you are interested in it if it meets this condition, OR it meets that condition - either will do.

                           When you describe the first search to someone in English you tend to say something like "I want records that are this and that.", but you actually mean I want all the records that are this, and as well as them I also want all the records that are that.  I don;t mean they have to be this and that at the same time."  That is not a Boolean AND, that is an OR search.

                           It is more obvious how confusing the English version is when you describe a Boolean OR.  You would say, "I want to see all the records for people who are female and over 18."  Everyone would take you to mean all female over-18-year-olds.  No males.  No under-18s.  But you are saying "I want all the records that are Pending and Deployed."  People (and in a way yourself) get confused, because you really mean "I would like to see all the records where people are Pending, or the records where people are Deployed."  Everyone knows what you mean then.

                           To specify an AND search put all the criteria on one request.  To specify an OR search put the criteia on separate requests.

                      • 8. Re: Script - Replace Field Contents with multiple criteria
                        Abhaya

                             Hi Chris,

                             I gave you some idea from my view on your post. So can you confirm about below functinality.

                             My post will fetch records having Deployment Status=Pending as well as Service = Army as well as Letter Sent = No and also

                        Deployment Status = Deployed as well as Service = Army as well as Letter Sent = No

                             The both the underlined words are the conditions. If either these condition satify then it will fetch records.

                             The status field is a variable field e.g pending or Deployed. So all records fetched from the databse are either pending or Deployed. It can't be both.

                             Please confirm do you want this or anything else..

                              

                             thanks

                              

                        • 9. Re: Script - Replace Field Contents with multiple criteria
                          schamblee

                               Sorry Atauf you are incorrect it is both.  Chris you need to put == in front of Deployed.  By default filemaker serach for any occurance of Deployed in the find field and Deployed is in both Deployed and Not Deployed.  The == makes filemaker make an exact find which will fix your problem.   Took me a little while to figure out what was going on.  

                               To futher to answer your question about "And" or "Or"   Each item enter on one record will be and each item on a separate record is or.

                               Again just put two equal signs in front of Deployed and your find will perform correctly.

                                

                          http://help.filemaker.com/app/answers/detail/a_id/5210/~/multiple-find-requests

                          • 10. Re: Script - Replace Field Contents with multiple criteria
                            Abhaya

                                 Yes its right chamblee.

                                 For exact match we have to use "==".

                                 My question was from the pre. post the records are either pending or deployment at the time of  find. Means after find you can see only records those are pending and also deployment.

                                  

                            • 11. Re: Script - Replace Field Contents with multiple criteria
                              ChrisSmythe

                                   Hello Sorbsbuster, thank you for your advice on "AND" and "OR" usage.  At this stage of my early development in making simple scripts, I find this rather confusing, but I really do appreciate it.

                                   So here I go: I believe what I am trying to say is that I want to see all records wherby:

                                   DbUserEdits::DeploymentStatus = "Pending" as well as

                                   Personnel::Service = "Army" as well as

                                   DbUserEdits::LetterSent = "No"

                                   I also want to see all records whereby:

                                   DbUserEdits::DeploymentStatus = "Deployed" as well as

                                   Personnel::Service = "Army" as well as

                                   DbUserEdits::LetterSent = "No"

                                   I am not sure if this constitutes an "AND" or an "OR" condition?

                                   I have tried using both in my script, but with no success.

                                   Could you please further assist me fixing up my script and clarifying if my logic is correct in assuming that I believe that what I am asking is an "OR" condition?

                                   Kind regards,

                                   Chris :)

                                    

                              • 12. Re: Script - Replace Field Contents with multiple criteria
                                ChrisSmythe

                                     Hello S Chamblee, Atauf and Sorbsbuster, thanks so much for all your excellent help - I am very grateful.  S Chamblee's suggestion of using == before the "Deployed" value works very well.

                                     Kind regards,

                                     Chris :)

                                • 13. Re: Script - Replace Field Contents with multiple criteria
                                  Sorbsbuster

                                       You have a combination of And and Or:

                                       Find records that are like:

                                       (This AND This AND This)
                                       OR like
                                       (That AND That AND That )

                                       =

                                       ( DbUserEdits::DeploymentStatus = "Pending" AND Personnel::Service = "Army" AND DbUserEdits::LetterSent = "No" )
                                       OR like
                                       ( DbUserEdits::DeploymentStatus = "Deployed" AND Personnel::Service = "Army" AND DbUserEdits::LetterSent = "No" )

                                       First request: ( DbUserEdits::DeploymentStatus = "Pending" AND Personnel::Service = "Army" AND DbUserEdits::LetterSent = "No" )
                                       Second request: ( DbUserEdits::DeploymentStatus = "Deployed" AND Personnel::Service = "Army" AND DbUserEdits::LetterSent = "No" )

                                       By the way, a slightly simpler way to do it is:

                                       Enter Find Mode
                                       Set Field [DbUserEdits::DeploymentStatus = "Pending"]
                                       Set Field [Personnel::Service = "Army"]
                                       Set Field [DbUserEdits::LetterSent = "No"]
                                       Duplicate Record/Request
                                       Set Field [DbUserEdits::DeploymentStatus = "==Deployed"]
                                       etc...

                                       You need the == because Filemaker will otherwise (very usefully) find all records that have the letter combination starting 'deployed' in the field.  So it will find records showing 'Not deployed', as it also contains that character sequence.  It would do exactly the same think with 'deplo'.  If the 'Not Deployed' records were actually marked as 'Undeployed' the case would not arise.