Web Apps – Timecard Duplication Enhancement

How many rows do you have on your timecard? As the row count increases, are you noticing that Dynamics SL web apps is taking longer to duplicate your timecard? One of the latest enhancements that I just released was taking over the duplication process.

Currently, web apps is going through an iterative (i.e not set based) process to return every line on the timecard you want to duplicate. This builds a model in web apps, then loads the grid, then presses the timecard save button to send all that data back to SQL server to save as a timecard, then needs to get the data again, then reloads the grid. Got all that?

I thought it would be much more efficient to just pass a document number and a ‘pe_date’ to a stored procedure and let SQL server do the heavy lifting. Then just load the grid for the newly created timecard.

To do this, you will need to create a new function to call your custom SQL code. Here is my custom create function in web apps that passes the document number to copy, the employee who owns the timecard, the pe_date to which we want to copy, and the current web apps user.

<ValidateAntiForgeryToken()> _
    Function CreateDuplicateDEA(docnbr As String, CopyInto As Date)
        Try
            Dim lh As LookupHelper = New LookupHelper()
            Dim r As DataRowCollection = lh.GetLookupExtended("zsp_DuplicateTimecard", {"@docnbr", "@employee", "@pedate", "@user"}, _
                                                              {docnbr,
                                                               Session("EmployeeID"),
                                                               CopyInto,
                                                               UIHelper.CurrentSLUserID}, _
                                                               UIHelper.LoginSession, False)
            If r IsNot Nothing Then
                Return RedirectToAction("ErrorPage", "Home", New With {.ErrorMsg = r(0).Item(0)})
            Else
                Return MyAssignments(CopyInto)
            End If
        Catch
            Return RedirectToAction("ErrorPage", "Home", New With {.ErrorMsg = "there was an error caught while duplicating the timecard."})
        End Try
    End Function

Then you need to edit the History and the Outstanding functions to run this custom function instead of the normal ‘CreateDuplicate’ function. (I apologize for the formatting below, but if you click the + sign to the right of the code, it exports nicely.) Essentially, we are creating a variable called DocNbr, assigning the document number of the timecard that needs to be duplicated to it, and then passing that and the week-ending date we want to create to our function.  If the creation was successful, we will then go directly to MyAssignments to load our newly created and saved timecard. If an error is encountered, I actually wrote a custom web apps error page to display.

'duplicate called.
'MSDE 7/11/19 taking over the duplicate process to improve performance

'CreateDuplicate(ProjectTimecardUIHelper.TimecardList(Convert.ToInt32(Duplicate)), CopyInto)
Dim DocNbr As String
DocNbr = ProjectTimecardUIHelper.TimecardList(Convert.ToInt32(Duplicate)).InnerTimecard.TimecardNumber
Return (CreateDuplicateDEA(DocNbr, CopyInto))

I can’t post all the SQL code as it’s pretty specific to our company but the main points you need to hit are:

  1. Get and Increment the document number that is stored in PJDOCNUM; this is your new docnbr.
  2. Handle any existing timecards that may exist for the week the user selected – get out if any are posted, approved, completed, etc. Delete if there was one in-process (we have a trigger that backs up any deleted timecards just in case)
  3. After checking for existing, then insert your row into PJLABHDR and rows into PJLABDET
  4. There is a temp table with 1 column that I am using the insert any SQL errors. Hence the If r isNot Nothing (errors were encountered)

Along they way, I am doing error checking and removing any inactive Project, Task, Acct, Sub combos. I am actually also removing projects that the employee has not charged time to in the last 3 months to keep things clean.

Here we are side by side…My new process on the left, the current web apps process on the right. I’ll even start the stock Web Apps (again…on the right) first, so there is no perceived advantage given! These are slightly extreme cases of 100+ row timecards, but they do happen. Make sure to wait until the end as the screen on the right loads, but has to then be saved and reloaded.

Feel free to share...

Leave a Reply

Your email address will not be published. Required fields are marked *

Close Menu