📁 Showcase
Attendance Sheet

Attendance sheet

Backend

1. Variables

Create a VarDurationAttendance variable to reflect the duration data utilized in the AttendanceSheet spreadsheet.

ensure var VarDurationAttendance kind: duration
  deploy: fixedOnDeploy
  unit: hours
  value: 8

2. AttendanceSheet

Create an AttendanceSheet spreadsheet to store employee attendance data.

ensure spreadsheet: AttendanceSheet
  withFields: ["InTime", "InTimeImage", "OutTime", "OutTimeImage", "Partition"]
  ofTypes: [bool, camera, bool, camera, text]
  removeRoleSet: [Owner]
  insertRoleSet: [Owner]
  updateRoleSet: [Employee]
  readAfterDurationVar: VarDurationAttendance

ensure partition DailyPartition
  assignPartitionField: Details.Partition
  formula: "
    let date = new Date();
    return date.toISOString().split('T')[0];"

Configure the properties of the EntityAttendance form and its associated fields.

ensure form EntityAttendance
  label: "Attendance"
ensure section: Details
ensure field InTime
  label: "In"
  permissionMatrix: {
      'defaultPermission': 'writeOnce'
    }
  captureLocation: true
  captureTime: true
  captureUser: false
  showAsCheckbox: true
  showCapturedValuesOnAside: ["captureTime", "captureLocation"]
ensure field InTimeImage
  label: "Image"
  permissionMatrix: {
      'defaultPermission': 'writeOnce'
    }
  placeHolder: "Image"
  showLabel: false
  captureLocation: true
  captureTime: true
ensure field OutTime
  permissionMatrix: {
      'defaultPermission': 'writeOnce'
    }
  captureLocation: true
  captureTime: true
  captureUser: false
  showAsCheckbox: true
  showCapturedValuesOnAside: ["captureTime", "captureLocation"]
ensure field OutTimeImage
  label: "Image"
  permissionMatrix: {
      'defaultPermission': 'writeOnce'
    }
  placeHolder: "Image"
  showLabel: false
  captureLocation: true
  captureTime: true
ensure field Partition
  permissionMatrix: {
      'defaultPermission': 'invisible'
    }

3. Visibility rules

Apply an InTime rule to the EntityAttendance form to toggle the visibility of InTime related fields, making them either visible or concealed based on the rule.

ensure visibilityRule InTime
  condition: "<root>
      <stmt>InTime == ${d:InTime.true}</stmt>
    </root>"

ensure actionMapIfTrue Show
  comp: InTimeImage
  visibilityAction: visible
  visibilityActionOn: field

ensure actionMapIfTrue Enable
  comp: OutTime
  visibilityAction: enable
  visibilityActionOn: field

ensure actionMapIfFalse Hide
  comp: InTimeImage
  visibilityAction: invisible
  visibilityActionOn: field

ensure actionMapIfFalse Disable
  comp: OutTime
  visibilityAction: disable
  visibilityActionOn: field

Apply an OutTime rule to the EntityAttendance form to toggle the visibility of OutTime related fields, making them either visible or concealed based on the rule.

ensure visibilityRule OutTime
  condition: "<root>
      <stmt>OutTime == ${d:OutTime.true}</stmt>
    </root>"

ensure actionMapIfTrue Show
  comp: OutTimeImage
  visibilityAction: visible
  visibilityActionOn: field

ensure actionMapIfFalse Hide
  comp: OutTimeImage
  visibilityAction: invisible
  visibilityActionOn: field

4. Reports

a. ReportAbsentEmployee

• Output form

Create an OutputAbsentEmployee form to function as an output form for the ReportAbsentEmployee report.

ensure form  OutputAbsentEmployee
ensure grid: EmployeeSet
ensure field Name kind: pickUser
  roleDataSource: [Employee]

ensure layoutGrid TableLayout kind: table
  showComps: [Name]

ensure layout ContentLayout kind: content
  direction: vertical
  contentPadding: thick
  flexCenter.gridLayouts: [EmployeeSet.TableLayout]
• Report

Create a ReportAbsentEmployee report to give a full overview of employee absence records.

ensure report ReportAbsentEmployee kind: query
  outputForm: OutputAbsentEmployee
  fromSpreadsheets: [AttendanceSheet]

  // compound query
  neoQL: "WITH

    q1 AS (
      SELECT entUserId AS `employeeId`
      FROM `DevWorldDb`.`neome`.`ent_user`
      WHERE type = #{GetEntTable(${ctx:ent.id})} AND userId IS NOT NULL
    ),

    q2 AS (
        SELECT ${ctx:row.createdBy} AS `employeeId`
        FROM ${ss}
        WHERE ${ctx:row.type} = ${ss:AttendanceSheet}
        AND DATE_PART_MILLIS(${ctx:row.createdOn}, 'year') = DATE_PART_MILLIS(MILLIS(NOW_STR()), 'year')
        AND DATE_PART_MILLIS(${ctx:row.createdOn}, 'month') = DATE_PART_MILLIS(MILLIS(NOW_STR()), 'month')
        AND DATE_PART_MILLIS(${ctx:row.createdOn}, 'day') = DATE_PART_MILLIS(MILLIS(NOW_STR()), 'day')
    )

    SELECT q1.employeeId AS ${out:EmployeeSet.Name}
    FROM q1
    EXCEPT
      SELECT q2.employeeId AS ${out:EmployeeSet.Name}
      FROM q2"

b. ReportAttendance

• Input form

Create a FilterVisit form to function as an input form for the ReportAttendance report.

ensure form FilterVisit
ensure section: Details
ensure field LabelFilterDate kind: label
  label: "Filter Date"
  bold: true
  textPattern: "Filter Date"
ensure field FromDate kind: date
  defaultValue: "startOfMonth"
ensure field ToDate kind: date
  defaultValue: "endOfMonth"
ensure field Divider kind: divider
ensure field LabelFilterEmployee kind: label
  label: "Filter Employee"
  bold: true
  textPattern: "Filter Employee"
ensure field Employee kind: pickUser
  roleDataSource: [Employee]
• Output form

Create an OutputAttendance form to function as an output form for the ReportAttendance report.

ensure form OutputAttendance
ensure grid: Attendance
ensure field Date kind: date
ensure field Employee kind: pickUser
  roleDataSource: [Employee]
ensure field In kind: bool
ensure field InTime kind: dateTime
ensure field InLocation kind: location
ensure field Out kind: bool
ensure field OutTime kind: dateTime
ensure field OutLocation kind: location
  captureMode: manual
ensure field TotalHours kind: decimal
  numberOfDigitsAfterPeriod: 2
ensure field OnLeave kind: bool
ensure field LeaveReason kind: paragraph

Apply a TableLayout to the Attendance grid to display data in tabular format.

ensure layoutGrid TableLayout kind: table
  columnSizeSet: ["AutoSize"]
  showComps: [Date, Employee, In, InTime, InLocation, Out, OutTime, OutLocation, TotalHours,
    OnLeave,LeaveReason]

Apply a ReportLayout to the OutputAttendance form.

ensure layout ReportLayout kind: content
  direction: vertical
  renderingMode: fullScreen
  contentPadding: thick
  flexCenter.gridLayouts: [Attendance.TableLayout]
• Report

Create a ReportAttendance report to give a full overview of employee attendance records.

ensure report ReportAttendance kind: query
  inputForm: FilterVisit
  outputForm: OutputAttendance
  fromSpreadsheets: [AttendanceSheet]

  // embedding JavaScript with NeoQL
  neoQL: "SELECT
        ${ctx:row.createdOn} AS ${out:Attendance.Date},
        ${ctx:row.createdBy} AS ${out:Attendance.Employee},
        ${ss:AttendanceSheet.Details.InTime} AS ${out:Attendance.In},
        ${ss:AttendanceSheet.Details.OutTime} AS ${out:Attendance.Out},
        IFMISSINGORNULL(((${ss:AttendanceSheet.Details.OutTime.captureTime} - ${ss:AttendanceSheet.Details.InTime.captureTime})/3600000),0) AS ${out:Attendance.TotalHours},
        ${ss:AttendanceSheet.Details.InTime.captureTime} AS ${out:Attendance.InTime},
        ${ss:AttendanceSheet.Details.OutTime.captureTime} AS ${out:Attendance.OutTime},
        ${ss:AttendanceSheet.Details.InTime.captureLocation} AS ${out:Attendance.InLocation},
        ${ss:AttendanceSheet.Details.OutTime.captureLocation} AS ${out:Attendance.OutLocation}
    FROM ${ss}
    WHERE
      ${ctx:row.type} = ${ss:AttendanceSheet}
      AND ${ctx:row.createdOn} >= ${in:Details.FromDate}
      AND ${ctx:row.createdOn} <= ${in:Details.ToDate}
      #{
        HAS_VALUE(${in:Details.Employee})
          ? 'AND ${ctx:row.createdBy} = ${in:Details.Employee}'
          : null}
    ORDER BY ${ctx:row.createdBy} ASC;"

Frontend

1. Actions

a. AddAttendance

Implement an AddAttendance action to create an attendance bubble for employees to record their attendance.

ensure action AddAttendance kind: rowInsert
  icon: "LibraryAddRounded"
  spreadsheet: AttendanceSheet
  chatBubbleHeader.subTitle: "Submit your today's Attendance before expiry"
  chatBubbleHeader.title: "Today's Attendance"
  sendMessageToInbox: true

b. ReportAttendance

Implement a ReportAttendance action to generate a comprehensive overview of the attendance data for all employees.

ensure action ReportAttendance kind: report
  icon: "ListAltRounded"
  report: ReportAttendance
  outputFormContentLayout: ReportLayout
  sendMessageToInbox: false

c. EditAttendanceSheet

Implement an EditAttendanceSheet action to facilitate the modification and updating of existing attendace record within the VisitBook spreadsheet.

ensure spreadsheet AttendanceSheet
ensure layoutSpreadsheet TableLayout kind: table
  columnSizeSet: ["AutoSize"]
  showComps: [$CreatedBy, InTime, InTimeImage, OutTime, OutTimeImage]

ensure action EditAttendanceSheet kind: spreadsheetEditor
  icon: "ViewListRounded"
  spreadsheet: AttendanceSheet
  layoutSpreadsheet: TableLayout

2. Group actions

Group all these actions into a MyPortal section for more convenient access.

ensure group MyPortal
  pinnedActions: [AddAttendance, ReportAttendance]
  pinnedActionSetMobile: [AddAttendance]
  actionPermission: {
      'AddAttendance': {
        'menuGroup': '1',
        'roles': [
          'Owner'
        ]
      },
      'EditAttendanceSheet': {
        'menuGroup': '2',
        'roles': [
          'Owner'
        ]
      },
      'ReportAttendance': {
        'menuGroup': '3',
        'roles': [
          'Owner'
        ]
      }
    }

Automation

1. DailyAttendance

Create a scheduled automation called DailyAttendance to send the attendance bubble to employees for them to record their attendance.

ensure automation DailyAttendance kind: scheduled
  startDateTime: "09/01/2025, 01:00:00 AM"
  timeZone: "Asia/Kolkata"
  repeatFrequencyKind: days
  frequency: 1

a. OnFire

Add an OnFire event to the DailyAttendance automation.

ensure event OnFire fire: onExpiry
• SendAttendance

Add a SendAttendance step to the OnFire event to send the attendance bubble to the MyPortal group.

ensure step SendAttendance kind: partitionSend
  senderRole: $Self
  targetSpreadsheet: AttendanceSheet
  chatBubbleHeader.subTitle: "Submit your today's Attendance before expiry"
  chatBubbleHeader.title: "Daily Attendance"
  targetGroups: [MyPortal]