AnsweredAssumed Answered

result of one table to another table using script

Question asked by lamp on Jul 7, 2010
Latest reply on Jul 7, 2010 by philmodjunk


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.