Help me check a looping script that adds a record for each day in a year

[RavingLuhn]

In my database I've got a table that functions as a join table. Due to the nature of the data, I've got to create the records here manually - or at least with a script. There needs to be a set of records for each employee that contains their ID number and all possible dates of a given year. The workflow happens like this:

• An administrator navigates to the layout
• They enter an employee id number and the desired year into respective global fields: GLOBAL_AddYear and GLOBAL_AddEmployee
• A button is pressed and then the script runs to create a year's worth of records for the selected employee

This is my script, which - as far as I can tell - does what I need it to. I'd appreciate a second set of eyes to take a look at it and see if there's anything I missed or could do better:

#This script is meant to be run on LAYOUT
#Will create a year's worth of records, inserting one employee ID number along with the specified year #Both values come from global fields filled out by the user

#Check to see that both dependent fields are filled, then set variables.
If [ IsEmpty ( GLOBAL_AddEmployee )or IsEmpty ( GLOBAL_AddYear )]
Show Custom Dialog [ Message: "Need to specify a year and an employee ID number!" ]
Exit Script [  ]
End If
Set Variable [ $SetYear; GLOBAL_AddYear]
Set Variable [ $SetEmpID; GLOBAL_AddEmployee]

#Perform a find to verify that a particular employee has no records for the specified year Set Error Capture [On]
Show All Records
Enter Find Mode [  ]
Set Field [ TABLE::Date; "*/*/" & $SetYear ]
Set Field [ TABLE::EmpIDNum; $SetEmpID ]
Perform Find [  ]
If [ Get ( FoundCount ) > 0 ]
	Show Custom Dialog [ Message: "This user already has a serires of records for the speified year." ]
Exit Script [  ]
End If

#Get length of selected year in days, then set first date of year
Set Variable [ $YearLength; Value:Date ( 12 ; 31 ; $SetYear ) - Date ( 1 ; 1 ; $SetYear ) + 1]
Set Variable [ $YearDate; Value:Date ( 1 ; 1 ; $SetYear )]

#Create starting point; couter begins at 1 here
New Record/Request
Set Field [ TABLE::EmpIDNum; $SetEmpID ]
Set Field [ TABLE::Date; $YearDate ]
Set Variable [ $Counter; Value:1 ]
#Add new records; one for each day of the year. Loop will continue until counter equals number of days in each year 
Loop
	#Variables are increased here and are checked against year length Set Variable [ $YearDate; Value:$YearDate + 1]
	Set Variable [ $Counter; Value:$Counter + 1 ]
	Exit Loop If [ $Counter - 1 = $YearLength ]
	New Record/Request
	Set Field [ TABLE::EmpIDNum; $SetEmpID ] 
    Set Field [ TABLE::Date; $YearDate ]
End Loop

Show All Records
Sort Records [ Restore; No dialog ]
Clear [ GLOBAL_AddYear]
Clear [ GLOBAL_AddEmployee ]

Are there any potential landmines here that I need to be aware of?

I'll probably make a variation of this for year-end maintenance that creates a list of active employee ID numbers in December and then loops through for each ID, but I've got time to figure that out.

Thanks!

[comment]

I suppose a few things in your script could be streamlined a bit, but I would start by questioning the purpose of this entire exercise. Why would you need to have an empty record for every day of a year, for every employee? IMHO, you should create records when you have something meaningful to record in them.

[RavingLuhn]

13 minutes ago, comment said:

Why would you need to have an empty record for every day of a year, for every employee? IMHO, you should create records when you have something meaningful to record in them.

The shortest answer I can give is: it's quite complicated. This join table exists to merge data between:

• A table with worked hours; this table is populated from an import and updated at least once daily
• A table with manually entered job (or assignment) hours.

I need the join table to more easily calculate discrepancies for a given date and employee between the two tables. There are other fields on this table that aren't relevant to the script. These are primarily summary fields used for calculations and reporting.

Given some other limitations, it's probably the simplest means to accomplish my end goal.

Edited October 29, 2020 by RavingLuhn
More details

[comment]

Such task could probably be accomplished by having a table of Slots with 366 permanent records and global fields for the year and the employee ID. This is assuming you want to calculate results for an entire year, one employee at a time.

 

 

[RavingLuhn]

That probably wouldn't work, as I have layouts in this solution that require calculating daily discrepancies for all employees within a 14-day period.

The basic layout is that a user clicks on an employee name in a portal from the employee table. This triggers a filter in a portal that displays records from the join table. The values shown here are the sum of hours from the time clock table as well as a sum of hours from the jobs table. A date is selected on this join portal to filter the jobs portal for that specific day. The user enters jobs and hours for that day until the discrepancy field in the join table shows a zero.

My goal is to be able to find and display users with any discrepancies.

[comment]

1 minute ago, RavingLuhn said:

I have layouts in this solution that require calculating daily discrepancies for all employees within a 14-day period.

Then perhaps you should create 14 slot records for each employee when creating an employee record. I am afraid my usefulness here is severely limited because I don't know what this is really about. I am just uncomfortable thinking about the amount of records your approach would require.

 

[RavingLuhn]

15 hours ago, comment said:

I am just uncomfortable thinking about the amount of records your approach would require.

Is this because I intend to calculate or use summary fields on this table?

For reference, we tend to average 60-70 employees a year which would result in about 25,000 records on this table for each year. I've been working on a sample file as a test and it seems to be pretty responsive.

[comment]

1 hour ago, RavingLuhn said:

Is this because I intend to calculate or use summary fields on this table?

No, it's because I am a minimalist. Ultimately, though, I find there is a correlation between this and performance.

 

[RavingLuhn]

1 hour ago, comment said:

No, it's because I am a minimalist. Ultimately, though, I find there is a correlation between this and performance.

At what point do you usually see performance degradation? I know it depends heavily on the specific design of a solution, but maybe there are some general guidelines you've observed in your experience?

[comment]

It depends on so many factors, I couldn't possibly try and answer such question. I am also lucky enough not to deal with performance problems on a daily basis. All I can say that in general an elegant design using minimum resources has also performance benefits.

As I said, I don't really know what this is about. My gut feeling says that calculating a result involving 60-70 employees and a period of 14 days will not pose a significant performance problem. What could pose a problem is calculating such result over and over - so if you need to consult these results often, consider storing them in a table of their own. This will be much more beneficial than a table of 25k records per year storing no information.

 

Edited October 31, 2020 by comment