1 2 Previous Next 16 Replies Latest reply on Apr 27, 2017 8:47 AM by bvanni

    Calculate Date

    bvanni

      Hi All,

       

      Sorry if this is a super easy question but i am trying to figure out a way to get a perform a find based on certain criteria.

       

      I have 2 fields named "Contracts::ContractDate" and "Contracts::CancellationDate"

       

      What im am trying to do is this. I have a button setup to performa  script that will perform a find request to find all records that have a cacellationdate that is more than 3 days past the contract date.

       

      I have this below but it wont seem to work properly. Please help, any input is appreciated. Thank you!

       

      Got to Layout ["New Contract Edit" (Contracts)]

      Enter Find Mode [ ]

      Set Variable [$ContrDatePlus4; Value: Contracts::ContractDate + 4]

      Set Field [Contracts::CancellationDate; " > " & $ContDatePlus4]

      Perform Find [ ]

       

      I've tried other similar variations but i cant get it to work. Thanks again!

        • 1. Re: Calculate Date
          BruceRobertson

          You just entered Find mode.

          Therefore Contracts::ContractDate is empty.

          • 2. Re: Calculate Date
            philmodjunk

            to expand on that, put

             

            Set Variable [$ContrDatePlus4; Value: Contracts::ContractDate + 4]

            above the step that enters find mode

            • 3. Re: Calculate Date
              bvanni

              Hi Bruce, thank you for the reply.

               

              So simply removing the "Enter find mode" from the script should return the result i'm looking for?

              I tried and its not finding any results.

               

              Is my definition of "Contracts::ContractDate + 4" the correct way to do a find request for this data that i'm trying to find?

               

              Thanks again Bruce.

              • 4. Re: Calculate Date
                BruceRobertson

                "I've tried other similar variations but i cant get it to work."

                That is correct.

                You can't.

                You will need to create a calculated field which gets the difference; and search that.

                • 5. Re: Calculate Date
                  justinc

                  Got to Layout ["New Contract Edit" (Contracts)]

                  Enter Find Mode [ ]

                  Set Variable [$ContrDatePlus4; Value: Contracts::ContractDate + 4]

                  Set Field [Contracts::CancellationDate; " > " & $ContDatePlus4]

                  Perform Find [ ]

                  Not sure if it is a transcription problem, but there variable names you have in steps 3 and step 4 are different.  If your script is actually coded this way, then the field will be empty.

                  • 6. Re: Calculate Date
                    user19752

                    In FM you can't comparing 2 field in search criteria.

                     

                    Make a calculation field like as

                    a - b

                    then find on the field.

                    • 7. Re: Calculate Date
                      ch0c0halic

                      First, in Find mode there is no 'Contracts::ContractDate' value.

                      Second, your logic is probably flawed.

                       

                      The date used in the find is the date in the first record. Using that won't find records with a difference of 4 days between the two fields. Rather it finds all records with a 'Contracts::CancellationDate" value of 4 days from the current records 'Contracts::ContractDate'. This might be what you want but it is not what you said is your desired outcome.

                       

                      To check the difference between two fields requires either a new calculation field that subtracts one from the other.

                       

                      Or a looping script that does the subtraction and checks for > 4

                       

                      Pseudo code.

                      Perform Find[] //Possibly limit found set? Maybe you only want contracts in this month?

                      go to first record

                      loop

                      if

                      go to next record

                      else

                      omit record

                      end if

                      end loop

                      • 8. Re: Calculate Date
                        bvanni

                        Thank you everyone for your reply's. Im sure as you can tell im new to scripting, thanks for bearing with me.

                         

                        I think you all have pointed me in the right direction. Especially Ch0c0halic, thanks for breaking that down for me, it helped me understand. I now understand i need to have it calculated in a new field.

                        I made a field named Contracts::CancellationMinusContractDate the calculation is simply:

                         

                        Contracts::CancellationDate - Contracts::ContractDate

                         

                        MY script now looks like this:

                         

                        Got to Layout ["New Contract Edit" (Contracts)]

                        If [Contracts::CancellationMinusContractDate > 4]

                        Perform Find

                        End if

                         

                        I knew that probably wouldnt work seeing how simple it looked. I know im getting closer now though.

                        Im not sure how to use the loop feature. I would like it to search the entire data base for the time being, i will introduce a date range once the basic is working properly.

                        go to first record doesnt seem to be a working function for me. Im not sure if i need it though as i want to find all relateable records. Thank you everyone again for the help! It is so much appreciated!

                        • 9. Re: Calculate Date
                          ch0c0halic

                          Very close.

                          The If[] doesn't do any good.

                          In the Find set the criteria of your new field = 1

                          You can easily do this by first doing a Find, manually, for a 1 in your new field.

                          Then remove the IF[] and Find[] steps.

                          Now add a new Find step and check to use the current criteria.

                           

                          Done!

                          • 10. Re: Calculate Date
                            ch0c0halic

                            BTW, I suggest before you write a script first do the task manually and write down "every" step as you do them.

                             

                            Now recreate each step in the script. You'll have to translate the manual steps into FMP script steps. But, they are pretty close to a 1-to-1 match up.

                             

                            From my experience this is the best way to learn script writing and a good way to set up values used in specific script steps, like the find criteria, for when you write the script.

                            • 11. Re: Calculate Date
                              bvanni

                              Thank you Ch0c0halic,

                               

                              Im a little confused on what you mean by having the new field  = 1

                              This would return a value of true? meaning that the field that is 4 or more days out would be true? Im confused on this one.

                              Please see below the screen shot of the layout in referencesnip1.PNG

                              This would be a record that would be included in my found records if the code worked properly due to it being more than 3 days past the contract date.

                               

                              I tottally agree that recreating each step is the best way to learn it. Ive been using that method, and a lot of reverse engineering of old code to teach myself many things. For some reason though i cant seem to get this right.

                               

                              "The If[] doesn't do any good.

                              In the Find set the criteria of your new field = 1

                              You can easily do this by first doing a Find, manually, for a 1 in your new field.

                              Then remove the IF[] and Find[] steps.

                              Now add a new Find step and check to use the current criteria."

                               

                              Can you please elaborate? Im not sure exactly how to implement this. Thank you again!!

                              • 12. Re: Calculate Date
                                ch0c0halic

                                Ah yes, I failed to see you used a different calculation. I thought to include:

                                ". . . more than 3 days past the contract date . . ."

                                as part of the calculation.

                                 

                                Change the calculation to:

                                Contracts::CancellationDate - Contracts::ContractDate > 3

                                 

                                Now use Find = 1 in this calculation field.

                                 

                                Unless you expect the difference value to change later on? Then use your calculation and change the Find to use ">" & $diff_value instead of 1.

                                 

                                Set variable

                                Enter Find mode

                                set field

                                Perform Find[] //don't use save parameter

                                 

                                This allows you to see what the script is doing, nothing hidden inside the Find[] step. It also provides one easy to see place to change the difference value.

                                • 13. Re: Calculate Date
                                  bvanni

                                  Thank you Ch0c0halic

                                   

                                  I changed the calculation for the field Contracts::CancellationMinusContractDate to reflect:

                                  Contracts::CancellationDate - Contracts::ContractDate > 3

                                  Im not sure where i would place the Find = 1 though

                                  The value shouldnt change as this is to find contracts that are past their 3 day right to cancel the contract.

                                   

                                  Since it wouldnt be a variable i didnt use the set variable option in the script.

                                   

                                  Got to Layout ["New Contract Edit" (Contracts)]

                                  Enter Find Mode [ ]

                                  Set Field [Contracts::CancellationMinusContractDate]

                                  Perform Find

                                   

                                  When ran it comes back with an "The provided criteria is not valid" error

                                   

                                  Thank you for your patience with my lack of knowledge Ch0c0halic!

                                  • 14. Re: Calculate Date
                                    brucewilson

                                    Set Field [Contracts::CancellationMinusContractDate ; 1 ]

                                     

                                    You are looking for records where CancellationMinusContractDate is True (1).

                                    1 2 Previous Next