Connecting Vacation Request To Ledger

You can connect a template to a log spreadsheet. When users use the template, each submission will be linked to the log to allow easy organization and access. This tool is especially useful in the case of vacation requests, given that it compiles submissions by many users and allows administrators to organize vacation days and make necessary accommodations. In the following guide, we will guide you through utilizing the Vacation Request template and connecting it to a ledger spreadsheet.

Accessing Collavate’s Templates #

First, login to Collavate and go to the Template page. Then, click on the Public Template tab to access templates that Collavate provides for its users. On this page, search for the Vacation Request template in the search bar and save it to your My Templates by clicking on the checkbox, clicking the Copy to button on the top right, and then finally, click My Templates in the dropdown menu.

Following the same process, copy the Vacation Request Tracking – Database/Ledger to My Templates by returning to the Public Templates page and searching for Vacation Request Tracking – Database/Ledger and save it to your My Templates.

Now that the templates are in your Templates page, we need to connect the Vacation Request template to the ledger. Upon clicking on the Expense Report template, a popup will be generated. After selecting the Edit tab,  click on the Add result to tab. In this tab, click the Choose button and select the file named Vacation Log (you can use the search bar to find it easier).

After selecting the spreadsheet you want connected to the vacation request template, you will see a dropdown menu with several process information variables. Follow the steps below to choose the correct process information variables for this vacation log.

We need to add a few process variables to follow the Vacation Request Tracking – Database/Ledger. Click the + button reference and follow the steps below:

    1. For Date:

  • In the dropdown select Submission Date
  • Now select Column
    • In the popup, go to CV-Process-Log and select Column A
  1. Employee Name
  • In the dropdown select Submitter’s name
  • Now select Column
    • In the popup, go to CV-Process-Log and select Column B
  1. For Annual Allowance
  • In the dropdown select Columns in Process
  • Now select Column
    • In the popup, go to CV-Process-Log and select Column C
  1. For Start Date
  • In the dropdown select Columns in Process
    • In the popup select the cell next to Date of Departure (C4)
  • Now select Column
    • In the popup, go to CV-Process-Log and select Column D
  1. For End Date:
  • In the dropdown select Columns in Process
    • In the popup select the cell next to Date of Return (C5)
  • Now select Column
    • In the popup, go to CV-Process-Log and select Column E
  1. For Number of Duty Days
  • In the dropdown select Columns in Process
    • In the popup select the cell next to Number of days on Vacation (C6)
  • Now select Column
    • In the popup, go to CV-Process-Log and select Column G

Click Run Test to send test data to the spreadsheet ledger to make sure the connection is working. You should see an alert saying the data was successfully copied. To verify the test data was successfully sent, click on the spreadsheet ledger and in that spreadsheet look for a second sheet titled CV-Process-Log. You should see the process information variables in the first row of that sheet.

Transferring Data from CV-Process-Log to Vacation Request Tracking – Database/Ledger #

We want the data from “CV-Process-Log” to show in our “Vacation Request” . To do this we will use the formula “=INDIRECT(“‘CV-Process-Log’!P”&ROW(A2))” It is important to note that A2 is the first cell that will contain saved dates in “CV-Process-Log”. This formula will be added to cell B6 because Column B is where our saved dates will show in the “Vacation Report Log”. Next we want to click on the cell and click the little square in the bottom right corner and drag it all the way down to the bottom of the “Database Ledger”

Now we will want to do the exact same for Employee Name, Annual Allowance,Start Date, End Date, No. of Duty Days and Approval Status. Below are the formulas you will need. Remove the brackets when inserting the formula to the Database Ledger

  • Employee Name = [=INDIRECT(“‘CV-Process-Log’!G”&ROW(B2))]
  • Approval Status = [=INDIRECT(“‘CV-Process-Log’!G”&ROW(C2))]
  • Start Date = [=INDIRECT(“‘CV-Process-Log’!G”&ROW(D2))]
  • Annual Allowance = [=INDIRECT(“‘CV-Process-Log’!G”&ROW(E2))]
  • End Date = [=INDIRECT(“‘CV-Process-Log’!G”&ROW(F2))]
  • Number of Duty Days = [=INDIRECT(“‘CV-Process-Log’!G”&ROW(G2))]

NOTE: Remember to drag the data explained above so that each cell in Vacation Request Tracking – Database/Ledger has a formula

Now to see if our formulas are working, we will click Run Test in the Template edit page like we did above. After running the test, you should see test variables in the Vacation Request Tracking – Database/Ledger.

Once you confirm you see the test variables, you are all set! Whenever you submit the template Expense Report the data of that process will be saved at CV-Process-Log sheet and inserted to the main spreadsheet of the Database Ledger.

example

use case

vacation request