1 Reply Latest reply on Jul 7, 2010 9:38 AM by philmodjunk

    result of one table to another table using script



      result of one table to another table using script


      Dear All,

      I have tables Employee, Query, History and TimeSpent.

      Any Query has History until the Query is closed. one Query can have any number of History. In History table we have filed TotalTimeSpent.

      I want to write a script to search all Hisory of an Employee in a period of date then add all the time spent (TotalTimeSpent field) then the result should set to TimeSpen field in TimeSpent table.

      Here is my script

      TimeSpent::Start_date_His is a global field

      TimeSpent::End_date_His is a global field

      --Start script--

      Go to Related Record [from table: "TimeSpent"; Using layout: "TimeSpent" (TimeSpent)]

      Set Variable [$$backToHere; Value:TimeSpent::TimeSpentID]

      Set Variable [$$EmpID; Value:Employee::EmployeeID]

      Set Variable [$$Start_Date; TimeSpent::Start_date_His]

      Set Variable [$$End_Date; TimeSpent::End_date_His]

      Perform Script ["Search History for time"]

      Set Field [ TimeSpent:: TotalTimeSpent; $$TotalTimeHistory]

      --End script

      The script "Search History for time" is as follow

      --Start script--

      Go to Layout ["History" (History)]

      Enter Find Mode[]

      Set Field [History::EmployeeID; $$EmpID]

      Set Field [History::Time; GetAsText ($$Start_Date) & "..." & GetAsText ($$End_Date)

      Perform Find[]

      Set Variable [$$TotalTimeSpent; Value:if (History::HistoryID > 0 ; Sum(History::TotalTimeSpent))]

      --End script--

      The problem with the script:

      the scirp goes to Hisory table and does the find and calculation but how can I put the value in TimeSpent table? If I put "Go to Layout" command it will go in a loop exit one script and enter to anther then that script will be repeated again.

      Please let me know if you need more informaiton.

      Your help is apprecaited.



        • 1. Re: result of one table to another table using script

          What is the purpose of the Go To Related Record Step? What layout do you specify in this step?

          What layout are you one when you start your first script? (That's important)

          This step: Set Variable [$$TotalTimeSpent; Value:if (History::HistoryID > 0 ; Sum(History::TotalTimeSpent))]
          doesn't look like right. Since you are on the History layout, Sum ( History::TotalTimeSpent ) will give you the same value as History::TotalTimeSpent. Is TotalTimeSpent a summary field?

          Depending on why you need this total, you could compute this total with a relationship and calculations and not need any script at all.

          PS. You can avoid the double space effect when posting scripts or other vertical lists if you use Shift-Return instead of return to start a new line.