Forum Discussion

AndreaJohannes's avatar
Qrew Trainee
4 years ago

Use a Connected Table to populate a Request Table and making those fields non-editable once the Request is saved


We keep all of our student employee information in a Personnel application.  I am trying to create an application for my hiring managers to submit a request for a merit increase. In the Increase app, I have created an employee table connected to the Personnel table that holds the current information about each employee and their position. The connected employee table has a one to many relationship with the request table. (This allows my hiring managers to just select an employees name when submitting a request and it pulls in all of the current information such as position, position start date, current salary, effective date of current salary). They then fill out the rest of the request with the requested salary, requested effective date, etc.  I also need for them to attach a copy of the employee's review.

My team then reviews the requests, approves it, submits it into Workday (our University payroll system) and then updates the Personnel app to show that employee's new salary for that position.  I've created a couple of checkboxes to show when the request was submitted in Workday and when the employee was updated in the Personnel application.  Once complete, the review and request are printed and placed in the employee's personnel file.

Unfortunately, the way our workflow works, once my team member has updated the information in the personnel app, when she goes back into the request to check that it's been updated (which effectively removes it from her todo list) and print the request, the request now has the new salary as the current salary (so it looks like nothing has changed). 

Is there a way to make it so that once a new request is submitted and saved those fields being populated by the connected table are no longer updated? Just making them read only does not do this. 

The other thing I thought about/tried was creating a "Submitted Request" table with an automation - so essentially when a new request was saved, it copied the values in the Request table over to the Submitted Request table. That way the current salary as of the time of request didn't change. My team member then updated the submitted request with approval, notes, submitted in workday, updated personnel app etc.  My challenge with that is in relating whatever document was submitted on the initial request so that it showed up on the submitted request.  I want my hiring manager to be able to initiate a request and add a document without having to go back out of that initial request and look up the submitted request.

Kind of stuck at the moment, looking for suggestions. Thanks so much in advance for any advice!  Below is my current structure for reference...

Andrea Johannes

1 Reply

  • Andrea -

    A couple of things. First, your 'Current Salary' on the Requests table should have a matching Snapshot field. Essentially, this will capture the value of a field at the time the Parent record is selected. Just create another Numeric/Currency field and name it 'Salary at time of Request' (or something similar) and select the Snapshot option at the bottom, being sure to select 'Current Salary' for the matching Lookup field. This will resolve your issue moving forward, but not for any existing records.

    Second, Rather than updating the Salary on the Employee table directly, I would recommend using the Requests table to help calculate the 'Current Salary'. You could have the 'Starting Salary' on the Employee table, which would never change. Then you would want the following:
    • Pay Adjustment - Field on the Requests table that would capture any adjustments (up or down) to the Salary
    • Total Adjustments - Summary field on the Employee table that summarizes all approved Requests associated to the Employee
    • Current Salary - Formula field on the Employee table that calculates as follows: [Starting Salary] + [Total Adjustments]
    This way, your total is always immediately updated and accurate.

    Blake Harrison
    DataBlender - Quick Base Solution Provider
    Atlanta GA
    404.800.1702 /