Employee Entitlements Import
Executive Summary
Employee Entitlements Import loads employee time-off entitlement transactions in bulk from an Excel file. Payroll or scheduling staff upload a spreadsheet with one row per transaction (for example an opening balance, an accrual, an adjustment or a payout). Each row is checked against AlayaCare and the entitlement rules, and the valid rows are added to the employee's entitlement history with the balance updated. Every import is saved in a history list with its pass and fail counts, and each import can be opened to see which rows passed or failed and why.
Key Features
- Upload an Excel file (.xlsx or .xls) of entitlement transactions
- Confirm each employee exists in AlayaCare by employee ID
- Check the time-off type, transaction type, quantity, date and time on every row
- Create the employee's entitlement for a time-off type automatically when it does not exist yet, using that type's default maximum balance and allow-negative setting
- Reject rows that would take the balance below zero (when negatives are not allowed), exceed the maximum balance, or duplicate an existing transaction
- Keep a history of imports with rows passed, rows failed and total rows
- Open an import to review each row and the reason a row failed
Typical Workflow
- Prepare the Excel file with the required columns (see the Import Templates dialog below).
- Click Import Entitlements, choose the file, and click Process.
- When Import Finished appears, find the new import in the list, which shows Rows Passed, Rows Failed and Total Rows.
- Open the import (right-click the row and choose Edit, or use the edit icon) to review the rows. Open the Failed tab to see the reason for each failed row.
- Correct failed rows in the spreadsheet and import them again. Rows that passed are not added twice, because a matching transaction is reported as "Transaction Already Exists".
Available Permissions
The following permissions are available to be assigned to AlayaCare Roles:
| Attribute | What It Controls |
|---|---|
| View | Open the import list and the detail of an import |
Main Grid
Lists each import that has been run, 14 rows per page.
| Column | Description |
|---|---|
| ID | Import number. |
| Imported | Date and time the import was run, in your local time. |
| Import Filename | Name of the Excel file that was imported. |
| Rows Passed | Number of rows that were accepted and added. |
| Rows Failed | Number of rows that were rejected. |
| Total Rows | Rows processed (passed plus failed). |
| Manage Records | An edit icon that opens the detail of the import. |
Main Grid Filters
| Filter | Purpose |
|---|---|
| Filter Filename | Limits the list to imports whose file name contains the text entered (not case-sensitive). |
Click Apply Filter to refresh the list. Use Clear Filter to reset the filter and reload the list.
Actions
| Button | Description |
|---|---|
| Import Entitlements | Opens the Import Templates dialog to choose and process a file. |
| Apply Filter | Applies the filename filter. |
| Clear Filter | Clears the filename filter. |
| Edit (right-click a row, or edit icon) | Opens the detail of the selected import. |
Dialog Components
Import Templates
Opens from Import Entitlements. Use Excel file to process to choose a .xlsx or .xls file (up to 100 MB).
| Button | Description |
|---|---|
| Process | Starts the import of the chosen file. |
| Clear | Removes the chosen file. |
| Close | Closes the dialog without importing. |
The file must have a second sheet named Transactions. On it, the first row holds the column headings, the second row is a description row that is skipped, and data starts on the third row. The columns, in order, are:
| Column | Heading | Description |
|---|---|---|
| A | EmployeeId* | The employee's AlayaCare ID. |
| B | EmployeeName | Name for reference only. The name found in AlayaCare is shown next to it in the results. |
| C | TimeOffType* | Name of the time-off type, exactly as set up. |
| D | TransactionType* | One of Set Balance, Adjustment - Deduct, Adjustment - Add, Accrual, Payout. |
| E | TransactionDescription* | Description saved with the transaction. |
| F | Quantity* | The amount in days or hours. |
| G | TransactionDate* | The transaction date. |
| H | TransactionTime* | The time, as HH:MM. |
If the sheet is missing, the column count is wrong or the headings do not match, a warning is shown and nothing is imported.
Employee Entitlement Import Detail
Opens when you edit an import. The heading shows the file name, and the back arrow returns to the list.
Tabs
| Tab | Purpose |
|---|---|
| Passed | Rows that were accepted and added. |
| Skipped | Rows with a Skipped status, if any. |
| Failed | Rows that were rejected, with the reason. |
Detail — Grid
Lists the rows for the selected tab, 14 per page. Expand a row to read the full reason.
| Column | Description |
|---|---|
| Line | The row number in the Excel file. |
| Employee UID | The employee ID from the file. |
| Employee Name | The name from the file, with the AlayaCare name found shown as "(Found: name)". |
| Time Off Type | The time-off type from the file. |
| Transaction Type | The transaction type from the file. |
| Transaction Description | The description from the file. |
| Quantity | The quantity from the file. |
| Transaction Date | The transaction date. |
| Time | The transaction time. |
| Status | Passed or Failed. |
| Reason | Why the row failed. |
Reasons you may see:
| Reason | Meaning |
|---|---|
| Employee ID is not valid | The employee ID is empty or not a number. |
| Employee Id Not Found In AlayaCare | AlayaCare has no employee with that ID. |
| TimeOffType Not Found | The time-off type name does not match a configured type. |
| The TimeOffType does not have Days/Hours specified | The time-off type has not been set to Days or Hours. |
| Transaction Type Invalid | The type is not one of the five accepted values. |
| Quantity must be less than or equal to 24 for Hours | Hours-based types cannot exceed 24 per row. |
| Transaction Date must be a valid date / Transaction Time must be a valid time HH:MM | The date or time could not be read. |
| Quantity exceeds available balance of n | A deduction or payout would take the balance below zero, and the entitlement does not allow negatives. |
| Quantity plus current balance of n will cause the Maximum Balance to be exceeded | The amount would take the balance over the entitlement's maximum. |
| Transaction Already Exists | The employee already has a transaction of that time-off type at that date and time with the same quantity. |
Dependencies
The following tables must be configured for lookups on this view to work:
| Table | Description |
|---|---|
| Time Off Types | Used to match the TimeOffType on each row. A type must be set to Days or Hours, and supplies the default maximum balance and allow-negative setting for a new entitlement. |
Processing Overviews
This section describes what happens after you confirm a processing action.
Process
- Signs in to AlayaCare. If sign-in fails, "Login To Alayacare failed" is shown and nothing is imported.
- Checks that the file is a readable Excel file with a Transactions second sheet and the eight expected headings.
- Saves a new import in the history with the file name, the date and time, and your user.
- For each data row, starting at the third row, looks up the employee in AlayaCare by ID.
- Finds the time-off type and confirms it is set to Days or Hours.
- Looks for the employee's entitlement for that type. If none exists, creates one with the type's default maximum balance and allow-negative setting, marked as imported.
- Checks the transaction type, quantity, date and time, then the balance rules (no negative balance unless allowed, maximum balance not exceeded) using the balance just before the transaction's date and time, and finally whether the same transaction already exists.
- Saves the row result as Passed or Failed with its reason.
- For each Passed row, adds the transaction to the employee's entitlement and recalculates the balance.
- Updates the totals on the import. A row that cannot be read at all shows "Invalid Data in row n" and processing continues.
- When finished, shows "Import Finished" and refreshes the list.