2 Replies Latest reply on Jan 31, 2010 2:28 PM by comment_1

    What's wrong with my script?



      What's wrong with my script?


      I'm working with FM pro 10

      I've created two layouts with two different tables, the first one is called "maintenance schedule" that contains 3 fields : Product SN, Client Name, Maintenance visit date. The second layout/table is called "Maintenance visit search" and contains one one field : Date

      I want the second layout to perform a search on "maintenance Schedule" by entering a date value , this search should return as result the maintenance visits scheduled between the current date and the value entered in the field "date" on the "Maintenance visit search" layout. So I've made a script but it isn't returning right results:


      If[IsEmpty (Maintenance visits search::Date )]

      Show custom dialog [ "Enter Date";"Please enter a value"]


      Set Variable [$$unit; Value:Maintenance visits search::Date]

      Go to layout ["Maintenance List:(Maintenance schedule)]

      Enter Find Mode []

      Set Field [ Maintenance schedul::Maintenance Date; Maintenance schedule::Maintenance Date >= Get (CurrentDate ) and MAintenance Schedule::Maintenance Date <= $$unit]

      Perform Find[]

      If [not Get(FoundCount)]

      Show custom Dialog ["No maintenance visits found"]

      Go to layout["Maintenance visits search" (Maintenance visits search)]

      End If

      End If


      What's wrong with my script??



        • 1. Re: What's wrong with my script?



          First, you don't need two tables. Delete the one with the date.


          Then, in the first table with the 3 fields, create a global date field. (To make it global, double click it after making a date field, then go to the storage tab and check the option to make it global, let's say it's called "gSelectDate".) Then, make another global field, say called "gCurrentDate".


          Then, make a relationship by doing the following:


          Go to the manage database menu item > relationship graph.


          At the bottom left, click the plus sign and create another "instance" of the same table. NOTE: you are not duplicating the table, you are just making a second instance of it. This is why you don't need another table. This is called a self-join.


          Now, link the two tables so that the date field in table2 is lesser than or equal to the gCurrentDate field and greater than or equal to the gSelectDate field (or strictly greater than, lesser than, as needed).


          Now you need to set the value of gCurrentDate.


          Create a script that sets gCurrentDate with this calculation: Get(CurrentDate).


          Now, go to file > file options


          And set this script to run at file start. This will set the global every time you open the file to today's date.


          Then, create your portal to view the data from this relationship, which you can do on a new layout if you like.


           Finally, close your file and open it again so that the script will run and you should be all set.





          • 2. Re: What's wrong with my script?

            Elie wrote:


            Set Field [ Maintenance schedul::Maintenance Date; Maintenance schedule::Maintenance Date >= Get (CurrentDate ) and MAintenance Schedule::Maintenance Date <= $$unit]

            This should be:


            Set Field [ Maintenance schedule::Maintenance Date ; Get (CurrentDate ) & ".." & $$unit]




            If you use a global field to get user input, you don't need the variable (which could be local anyway).