Creating a new record for each record in another table

[andersonk06]

I am looking to write a script that creates a set of new records (one for each week of the year) for each new employee added.

There are 3 tables; EmployeeInfo, WeeklyPay, Calendar. The new records created in WeeklyPay need to have their EmployeeID set to the new employee's ID and the WeekID needs to be drawn from each record in the Calendar table. (Many other fields will also be set at this time but this is where I am getting stuck). Right now my script is populating the fk_EmployeeID field correctly but nothing is happening with the WeekID. 

 

Set Variable [$EmployeeID; Value: EmployeeInfo :: EmployeeID]

Go to Layout [ "WeeklyPay" (WeeklyPay) ]

 

Loop

New Record/Request

Set Field [WeeklyPay :: fk_EmployeeID ;  $EmployeeID]

Set Field [WeeklyPay :: WeekID ; Calendar ::pk_WeekID]

Go to Record/Request/Page [Next ; Exit after last]

End Loop

[eos]

The new records created in WeeklyPay need to have their EmployeeID set to the new employee's ID and the WeekID needs to be drawn from each record in the Calendar table.[…] Right now my script is populating the fk_EmployeeID field correctly but nothing is happening with the WeekID. 

What you want is to process a list; namely, the list of all calendar records.

Try something like

Set Variable [ $weekIDList ; ExecuteSQL ( " SELECT pk_WeekID FROM Calendar " ; "" ; "" ) ] 
Set Variable [ $weekIDCount ; ValueCount ( $weekIDList ) ]
Set Variable [ $employeeID ; EmployeeInfo::employeeID ]
Go to Layout [ WeeklyPay ( WeeklyPay ) ]
Loop
  Exit Loop if [ Let ( $weekIDCounter = $weekIDCounter + 1 ; $weekIDCounter > $weekIDCount ) ]
  New Record/Request
  Set Field [ WeeklyPay::fk_EmployeeID ; $EmployeeID ]
  Set Field [ WeeklyPay::WeekID ; GetValue ( $weekIDList ; $weekIDCounter ) ]
  # [ … ]
End Loop

[dwdata]

IMHO Loops may be too slow, in this case.

Consider establishing and maintaining a "work_weeks_resource" table which holds the current set of work weeks for the year with a global field for the EmployeeID.

Then when you want to add the work_week records, simply script the set of the global EmployeeID in that table, then import the records to target table:

work_weeks_resource ---->  WeeklyPay

Hope this helps!

Edited June 18, 2015 by dwdata

[andersonk06]

IMHO Loops may be too slow, in this case.

Consider establishing and maintaining a "work_weeks_resource" table which holds the current set of work weeks for the year with a global field for the EmployeeID.

Then when you want to add the work_week records, simply script the set of the global EmployeeID in that table, then import the records to target table:

work_weeks_resource ---->  WeeklyPay

Hope this helps!

Hi dwdata- Can you expand more on how you would script the global ID onto that table and then import the records into the target table?

[dwdata]

Hi Anderson,

Easier to mock up this file then to document the routine. Better to see it action.

If you find anything useful that would benefit this thread, please post it for other users.

Hope this helps and good luck!

work_weeks.zip