I had to write a script to update a database that I call last location. This particular script makes sure that a record is the most recent in the database. The updating is done based on a field called Case_Number. If the value in Case_Number exists, it will compare the Action_Date and Action_Time fields of the incoming records with that of the one that already exists. The most current record will be kept in the database.
If the value in Case_Number doesn't exist, then the script adds the record to the database and moves on to the next incoming record. (Mind the fact that the incoming records are coming from another database which has the identical field names and setup.) The script looks as follows:'Append_New2Last
'The following will update Last_Location.dbf with current information from New_Location.dbf
'==========================================================================================
tblNew=table.open("C:Program FilesA5V4trackerNew_Location.dbf")
tblNew.fetch_first()
tblLast=table.open("C:Program FilesA5V4trackerLast_Location.dbf")
tblLast.fetch_first()
WHILE .NOT. tblNew.fetch_EOF()
tblLast.batch_begin()
WHILE .NOT. tblLast.fetch_EOF()
IF tblNew.Case_Number = tblLast.Case_Number
IF tblNew.Action_Date > tblLast.Action_Date
GOTO MatchCase
ELSEIF tblNew.Action_Date = tblLast.Action_Date
IF tblNew.Action_Time >= tblLast.Action_Time
GOTO MatchCase
END IF
END IF
GOTO CloseTblLast
END IF
tblLast.fetch_next()
END WHILE
NewCase:
tblLast.enter_begin(.T.)
tblLast.Current_Location = tblNew.Current_Location
tblLast.Action_By = tblNew.Action_By
tblLast.Movement_Type = tblNew.Movement_Type
tblLast.Case_Number = tblNew.Case_Number
tblLast.Action_Date = tblNew.Action_Date
tblLast.Action_Time = tblNew.Action_Time
tblLast.enter_end(.T.)
GOTO CloseTblLast
MatchCase:
tblLast.change_begin(.T.)
tblLast.Current_Location = tblNew.Current_Location
tblLast.Action_By = tblNew.Action_By
tblLast.Movement_Type = tblNew.Movement_Type
tblLast.Case_Number = tblNew.Case_Number
tblLast.Action_Date = tblNew.Action_Date
tblLast.Action_Time = tblNew.Action_Time
tblLast.change_end(.T.)
CloseTblLast:
tblLast.batch_end()
tblLast.close()
tblLast=table.open("C:Program FilesA5V4trackerLast_Location.dbf")
tblLast.fetch_first()
tblNew.fetch_next()
END WHILE
tblLast.pack()
tblLast.close()
tblNew.close()
'Dumby Line for TEsting
The Question! How do I make this script run faster???? It has a tendancy to go slow when a number of records are being added. I can email the script if necessary.
If the value in Case_Number doesn't exist, then the script adds the record to the database and moves on to the next incoming record. (Mind the fact that the incoming records are coming from another database which has the identical field names and setup.) The script looks as follows:'Append_New2Last
'The following will update Last_Location.dbf with current information from New_Location.dbf
'==========================================================================================
tblNew=table.open("C:Program FilesA5V4trackerNew_Location.dbf")
tblNew.fetch_first()
tblLast=table.open("C:Program FilesA5V4trackerLast_Location.dbf")
tblLast.fetch_first()
WHILE .NOT. tblNew.fetch_EOF()
tblLast.batch_begin()
WHILE .NOT. tblLast.fetch_EOF()
IF tblNew.Case_Number = tblLast.Case_Number
IF tblNew.Action_Date > tblLast.Action_Date
GOTO MatchCase
ELSEIF tblNew.Action_Date = tblLast.Action_Date
IF tblNew.Action_Time >= tblLast.Action_Time
GOTO MatchCase
END IF
END IF
GOTO CloseTblLast
END IF
tblLast.fetch_next()
END WHILE
NewCase:
tblLast.enter_begin(.T.)
tblLast.Current_Location = tblNew.Current_Location
tblLast.Action_By = tblNew.Action_By
tblLast.Movement_Type = tblNew.Movement_Type
tblLast.Case_Number = tblNew.Case_Number
tblLast.Action_Date = tblNew.Action_Date
tblLast.Action_Time = tblNew.Action_Time
tblLast.enter_end(.T.)
GOTO CloseTblLast
MatchCase:
tblLast.change_begin(.T.)
tblLast.Current_Location = tblNew.Current_Location
tblLast.Action_By = tblNew.Action_By
tblLast.Movement_Type = tblNew.Movement_Type
tblLast.Case_Number = tblNew.Case_Number
tblLast.Action_Date = tblNew.Action_Date
tblLast.Action_Time = tblNew.Action_Time
tblLast.change_end(.T.)
CloseTblLast:
tblLast.batch_end()
tblLast.close()
tblLast=table.open("C:Program FilesA5V4trackerLast_Location.dbf")
tblLast.fetch_first()
tblNew.fetch_next()
END WHILE
tblLast.pack()
tblLast.close()
tblNew.close()
'Dumby Line for TEsting
The Question! How do I make this script run faster???? It has a tendancy to go slow when a number of records are being added. I can email the script if necessary.
Comment