result of one table to another table using script
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
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]
The script "Search History for time" is as follow
Go to Layout ["History" (History)]
Enter Find Mode
Set Field [History::EmployeeID; $$EmpID]
Set Field [History::Time; GetAsText ($$Start_Date) & "..." & GetAsText ($$End_Date)
Set Variable [$$TotalTimeSpent; Value:if (History::HistoryID > 0 ; Sum(History::TotalTimeSpent))]
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.