Script Tiles company

⚠️

We recommend finishing Tutorial: Task list to understand general usage before continuing here.

To create enterprise Tiles company, open terminal in the route studio by clicking the link https://web.neome.ai/studio (opens in a new tab).

Enterprise

The whole script to build the example below is available here. Open .neo files with a text editor of your choice, and copy and paste them into the terminal. Alternatively, walk the example below step by step.

Add an enterprise "Tiles company" by sending the commands below into the terminal.

// Create enterprise
add enterprise name:"Your Tiles company"
update enterprise about:"We create tiles"
update enterprise timeZone:"Asia/Kolkata"
update enterprise displayDateFormat:"dd/MM/yyyy, HH:mm:ss"

Backend

1. Variables

// Add variable to store VarTextRupeeSymbol symbol
ensure variable VarTextRupeeSymbol kind:"Text"
update variable VarTextRupeeSymbol value:"₹"
 
// Add variable to store time data
ensure variable VarSetOfTextCustomerType kind:"setOfText"
update variable VarSetOfTextCustomerType value:"Customer,Architect,Dealer"
 
ensure variable VarSetOfTextProbability kind:"setOfText"
update variable VarSetOfTextProbability value:"High,Medium,Low"
 
ensure variable VarFunctionGetPinStroke kind:"function"
update variable VarFunctionGetPinStroke outputKind:"text" javascript:"return 'solid';"
 
ensure variable VarFunctionGetPinShape kind:"function"
update variable VarFunctionGetPinShape outputKind:"text" javascript:"return 'pin';"
 
// Add variable for attendance read
ensure variable VarDurationAttendance kind:"duration"
update variable VarDurationAttendance value:"8 hours"

2. Roles

ensure role Owner
ensure role Employee

3. Spreadsheets

a. SizeMaster

• Create form

// create form FormSize to provide structure to the spreadsheet SizeMaster
 
ensure form FormSize
update form FormSize label:"Tile Size"
 
ensure section Details
 
ensure field Size type:"text"

• Associate form

// associate the form FormSize with the spreadsheet SizeMaster
ensure spreadsheet SizeMaster form:"FormSize"

• Associate permissions

ensure spreadsheetPermission insertRoles:"Owner"
ensure spreadsheetPermission updateRoles:"Owner"
ensure spreadsheetPermission removeRoles:"Owner"

• Associate layout

// add a spreadsheet list layout
ensure spreadsheetLayout ListLayout kind:"list"
 
// show search bar on top of the list
update spreadsheetLayout ListLayout showSearchBar:"yes"
 
// list layout is composed of first line with first segment
update spreadsheetLayoutListItem line:"first" segment:"first" source:"field" text:"Size"

b. TypeMaster

• Create form

// add the form FormType to provide structure to the spreadsheet TypeMaster
 
ensure form FormType
update form FormType label:"Tile Type"
 
ensure section Details
 
ensure field Type type:"text"

• Associate spreadsheet

// associate the form FormType with the spreadsheet TypeMaster
 
ensure spreadsheet TypeMaster form:"FormType"

• Associate permissions

ensure spreadsheetPermission insertRoles:"Owner"
ensure spreadsheetPermission updateRoles:"Owner"
ensure spreadsheetPermission removeRoles:"Owner"

• Associate layout

// add a spreadsheet list layout
ensure spreadsheetLayout ListLayout kind:"list"
 
 // show search bar on top of the list
update spreadsheetLayout ListLayout showSearchBar:"yes"
 
// list layout is composed of first line with first segment
update spreadsheetLayoutListItem line:"first" segment:"first" source:"field" text:"Type"

c. ItemMaster

• Create form

// add the form FormItem to provide structure to the spreadsheet ItemMaster
 
ensure form FormItem
update form FormItem label:"Tile"
 
ensure section Details
 
ensure field Name type:"text"
ensure field Description type:"paragraph"
update field Description lineCount:"5"
ensure field Price type:"Number"
update field Price prefix:"VarTextRupeeSymbol"
 
ensure field TypeRef type:"ref" spreadsheet:"TypeMaster"
update field TypeRef spreadsheetLayout:"ListLayout" categoryFilterDisplayField:"Type"
update field TypeRef copyFields:"$RowId,Type"
ensure field SizeRef type:"ref" spreadsheet:"SizeMaster"
update field SizeRef spreadsheetLayout:"ListLayout" categoryFilterDisplayField:"Size"
update field SizeRef copyFields:"$RowId,Size"

• Associate form

// Add spreadsheet ItemMaster
ensure spreadsheet ItemMaster form:"FormItem"
update spreadsheet ItemMaster searchableFields:"Name,Price,Type,Size"
update spreadsheet ItemMaster queryableFields:"TypeMasterRowId,SizeMasterRowId,Price,Type,Size"

• Associate permissions

ensure spreadsheetPermission insertRoles:"Owner"
ensure spreadsheetPermission updateRoles:"Owner"
ensure spreadsheetPermission removeRoles:"Owner"
 
ensure SpreadsheetPermissionForwardRoles role:"Employee" roles:"Employee"

• Associate list layout

// add a spreadsheet list layout
 
ensure spreadsheetLayout ListLayout kind:"list"
 
 // show search bar on top of the list along with category filter
update spreadsheetLayout ListLayout showSearchBar:"yes" categoryFields:"TypeRef,SizeRef" advanceFilterFields:"Price"
 
// list layout is composed of first line and second line with first segments
update spreadsheetLayoutListItem line:"first" segment:"first" source:"field" text:"Name" showLabels:"yes"
update spreadsheetLayoutListItem line:"second" segment:"first" source:"field" text:"Type,Size" showLabels:"yes"
update spreadsheetLayoutListItem line:"second" segment:"first" source:"field" text:"Price" showLabels:"yes"

• Associate table layout

// add a spreadsheet table layout
 
ensure spreadsheetLayout TableLayout kind:"table"
 
// showFields are the columns in the table with column sizes as AutoSize
update spreadsheetLayout TableLayout columnSizes:"AutoSize" showFields:"Name,Description,Price,Type,Size"

d. VisitSheet

• Create form

// add the form FormVisit to provide structure to the spreadsheet VisitSheet
 
ensure form FormVisit
ensure formPermission commentRoles:"Employee"
update form FormVisit label:"Visit"
 
ensure section Details
 
ensure field CustomerType type:"pickText"
update field CustomerType source:"VarSetOfTextCustomerType"
ensure field Customer type:"text"
ensure field Probability type:"pickText"
update field Probability source:"High,Medium,Low"
ensure field Location type:"location"
ensure field Remarks type:"paragraph"
update field Remarks lineCount:"5"
 
update form FormVisit chatLabelField:"CustomerType"

• Associate form

// associate the form FormVisit with the spreadsheet VisitSheet
ensure spreadsheet VisitSheet form:"FormVisit"

• Associate permissions

// spreadsheet crud permissions
 
ensure SpreadsheetPermission insertRoles:"Employee"
ensure SpreadsheetPermission readRoles:"Owner"
ensure SpreadsheetPermission updateRoles:"Employee"
 
ensure SpreadsheetPermissionForwardRoles role:"Employee" roles:"Employee"

• Associate list layout

// add a spreadsheet list layout
ensure spreadsheetLayout ListLayout kind:"list"
 
 // show search bar on top of the list
update spreadsheetLayout ListLayout showSearchBar:"yes"
 
// list layout is composed of first and second line with first and caption segments
update spreadsheetLayoutListItem line:"first" segment:"first" source:"field" text:"Customer" showLabels:"yes"
update spreadsheetLayoutListItem line:"second" segment:"first" source:"field" text:"Location" showLabels:"yes"
update spreadsheetLayoutListItem line:"first" segment:"caption" source:"field" text:"CustomerType"
update spreadsheetLayoutListItem line:"second" segment:"caption" source:"field" text:"Probability"

• Associate table layout

// add a spreadsheet table layout
ensure spreadsheetLayout TableLayout kind:"table"
 
// showFields are the columns in the table with column sizes as AutoSize
update spreadsheetLayout TableLayout columnSizes:"AutoSize"
update spreadsheetLayout TableLayout showFields:"CustomerType,Customer,Probability,Location,Remarks"
update spreadsheetLayout TableLayout showCommentCount:"yes"

e. AllowanceSheet

• Create form

// create form FormAllowance to provide structure to the spreadsheet AllowanceSheet
ensure form FormAllowance
 
//** section details
 
ensure section Details
 
ensure field Date type:"date"
update field Date defaultValue:"Now"
ensure field Destination type:"text"
ensure field TotalTime type:"duration"
update field TotalTime defaultValue:"90 minutes"
ensure field TotalKm type:"decimal"
update field TotalKm suffix:"km"
ensure field TravellingFare type:"decimal"
update field TravellingFare prefix:"VarTextRupeeSymbol"
ensure field StartKm type:"camera"
update field StartKm showLabel:"yes"
ensure field EndKm type:"camera"
update field EndKm showLabel:"yes"
 
//** section Summary
 
ensure section Summary
 
ensure field AllowanceTotal type:"decimal"
update field AllowanceTotal prefix:"VarTextRupeeSymbol"
ensure field OtherExpenseTotal type:"decimal"
update field OtherExpenseTotal prefix:"VarTextRupeeSymbol"
ensure field GrandTotal type:"decimal"
update field GrandTotal prefix:"VarTextRupeeSymbol" formula:"(${f:Details.TravellingFare} + ${f:Summary.AllowanceTotal} + ${f:Summary.OtherExpenseTotal})"

• Associate form

// associate the form FormAllowance with the spreadsheet AllowanceSheet
ensure spreadsheet AllowanceSheet form:"FormAllowance"

• Associate permissions

// spreadsheet crud permissions
ensure SpreadsheetPermission readRoles:"Owner"
ensure SpreadsheetPermission insertRoles:"Employee"

f. OrderBook

• Create form

// add the form FormOrder to provide structure to the spreadsheet OrderBook
 
ensure form FormOrder
update form FormOrder label:"Order"
ensure formPermission commentRoles:"Employee"
 
//** grid Items
 
ensure grid Items
 
ensure field Quantity type:"counter"
ensure field Total type:"number"
update field Total formula:"${f:Items.Price} * ${f:Items.Quantity}"
 
ensure field ItemRef type:"Ref" spreadsheet:"ItemMaster"
update field ItemRef spreadsheetLayout:"ListLayout"
update field ItemRef copyFields:"Name,Price,$RowId"
update field ItemRef forceOpenOnFormCreate:"yes"
update fieldRefOverlayLayout line:"first" segment:"caption" source:"field" text:"Items.Quantity"
 
ensure gridLayout ListLayout kind:"list"
update gridLayoutListItem line:"first" segment:"first" source:"field" text:"Name"
update gridLayoutListItem line:"first" segment:"caption" source:"field" text:"Quantity"
update gridLayoutListItem line:"second" segment:"first" source:"field" text:"Price" showLabels:"yes"
update gridLayoutListItem line:"second" segment:"caption" source:"field" text:"Total"
 
ensure gridLayout TableLayout kind:"table"
update gridLayout TableLayout columnSizes:"AutoSize" showFields:"Name,Price,Quantity,Total"
 
update grid Items asideDefaultLayout:"ListLayout"
 
//** section details
 
ensure section Details
 
ensure field CustomerRef type:"ref" spreadsheet:"VisitSheet"
update field CustomerRef spreadsheetLayout:"ListLayout"
update field CustomerRef copyFields:"$RowId,Customer,Location"
ensure field Date type:"date"
update field Date defaultValue:"Now"
ensure field Remarks type:"paragraph"
 
//** section Summary
 
ensure section Summary
 
ensure field TotalBill type:"number"
update field TotalBill formula:"SUM(${f:Items.Total})"
ensure field OrderStatus type:"pickText"
update field OrderStatus source:"Created,CreditCheck,Dispatched,Completed,Cancelled" defaultOption:"Created"

• Form layout header

// add form layout header
update FormLayoutHeader showEnterprise:"true"

• Form layout content

 // add DlContentlayout, a form content layout
 
ensure FormLayoutContent DlContentlayout
update FormLayoutContent DlContentlayout direction:"vertical" defaultLayout:"true"
 
update FormLayoutContent DlContentlayout position:"start" fields:"Date,Customer,Location"
update formLayoutContent DlContentlayout position:"start" showItemBorders:"top,bottom" showItemPadding:"top,bottom" padding:"thick"
 
update FormLayoutContent DlContentlayout position:"flexCenter" gridLayouts:"Items.TableLayout"
update formLayoutContent DlContentlayout position:"flexCenter" showItemPadding:"top,bottom" padding:"thick"
 
update FormLayoutContent DlContentlayout position:"end" fields:"TotalBill,OrderStatus"
update formLayoutContent DlContentlayout position:"end" showItemBorders:"top" showItemPadding:"top,bottom" padding:"thick"

• Associate form

// associate the form FormOrder with the spreadsheet OrderBook
 
ensure spreadsheet OrderBook form:"FormOrder"
update spreadsheet OrderBook searchableFields:"Location,Customer,Items.Name"

• Associate permissions

// spreadsheet crud permissions
 
ensure SpreadsheetPermission insertRoles:"Employee"
ensure SpreadsheetPermission readRoles:"Owner"
ensure SpreadsheetPermission updateRoles:"Owner"
 
ensure SpreadsheetPermissionForwardRoles role:"Employee" roles:"Employee"

• Associate table layout

// Associate layout to spreadsheet OrderBook
ensure spreadsheetLayout TableLayout kind:"table"
update spreadsheetLayout TableLayout columnSizes:"AutoSize"
update spreadsheetLayout TableLayout showCommentCount:"yes"
update spreadsheetLayout TableLayout showFields:"Customer,Date,Remarks,OrderStatus" masterDetailGrids:"Items"

• Associate kanban layout

ensure spreadsheetLayout KanbanLayout kind:"kanban"
update spreadsheetLayout KanbanLayout showCommentCount:"yes"
update spreadsheetLayout KanbanLayout showFields:"Customer,Date,TotalBill,OrderStatus" allowToSwitchLayouts:"TableLayout"
update spreadsheetLayout KanbanLayout kanbanField:"OrderStatus"
 
update spreadsheetLayout TableLayout allowToSwitchLayouts:"KanbanLayout"

g. AttendanceSheet

• Create form

// add the form FormAttendance to provide structure to the spreadsheet AttendanceSheet
 
ensure form FormAttendance
 
ensure section Details
 
ensure field InTime type:"bool"
update field InTime captureTime:"yes" captureUser:"yes" showCapturedValuesOnAside:"captureTime"
ensure fieldPermission default:"writeOnce"
ensure field OutTime type:"bool"
update field OutTime captureTime:"yes" captureUser:"yes" showCapturedValuesOnAside:"captureTime"
ensure fieldPermission default:"writeOnce"
ensure field PartitionField type:"text"
ensure fieldPermission default:"invisible"

• Associate form

// associate the form FormAttendance with the spreadsheet AttendanceSheet
ensure spreadsheet AttendanceSheet form:"FormAttendance"

• Associate permissions

// spreadsheet crud permissions
ensure SpreadsheetPermission insertRoles:"Owner"
ensure SpreadsheetPermission readRoles:"Employee"
ensure SpreadsheetPermission updateRoles:"Employee"
ensure SpreadsheetPermission readAfterDuration:"VarDurationAttendance"

• Associate partition

// spreadsheet partition
ensure SpreadsheetPartition PartitionDaily field:"PartitionField"
update SpreadsheetPartition PartitionDaily formula:"new Date().toISOString().split('T')[0]"

h. EmployeeMaster

• Create form

// add the form FormEmployee to provide structure to the spreadsheet EmployeeMaster
ensure form FormEmployee
 
ensure section Details
 
ensure field Employee type:"pickUser"
update field Employee dataSourceRoles:"Employee"
ensure field Gender type:"pickText"
update field Gender source:"Male,Female,Others"
ensure field HourlyWage type:"number"
update field HourlyWage helperText:"Hourly wages in INR"
ensure field DateOfBirth type:"Date"
ensure field AadhaarCard type:"document"
ensure field PanCard type:"document"

• Associate form

// associate the form FormEmployee with the spreadsheet EmployeeMaster
 
ensure spreadsheet EmployeeMaster form:"FormEmployee"
update spreadsheet EmployeeMaster searchableFields:"Gender,HourlyWage"

• Associate permission

// spreadsheet crud permissions
 
ensure SpreadsheetPermission insertRoles:"Owner"
ensure SpreadsheetPermission updateRoles:"Owner"
ensure SpreadsheetPermission readRoles:"Owner"
ensure SpreadsheetPermission removeRoles:"Owner"

• Associate layout

ensure spreadsheetLayout TableLayout kind:"table"
update spreadsheetLayout TableLayout columnSizes:"AutoSize" showFields:"Employee,Gender,HourlyWage,DateOfBirth"

4. Reports

a. VisitMap

• Output form

// add output form for the report
 
ensure form FormOutputVisit
 
//** grid VisitSet
ensure grid VisitSet
 
ensure field CustomerRowId type:"rowId"
ensure field CustomerType type:"pickText"
update field CustomerType source:"VarSetOfTextCustomerType"
ensure field Customer type:"text"
ensure field Location type:"location"
ensure field Probability type:"pickText"
update field Probability source:"VarSetOfTextProbability"
ensure field Remarks type:"paragraph"
ensure field VisitedBy type:"pickUser"
update field VisitedBy dataSourceRoles:"Employee"
ensure field MapLineStroke type:"lineStroke"
update field MapLineStroke formula:"VarFunctionGetPinStroke()"
ensure field MapPinShape type:"PinShape"
update field MapPinShape formula:"VarFunctionGetPinShape(${f:VisitSet.CustomerType})"
ensure field UserColor type:"Color"
update field UserColor formula:"getUserColor(${f:VisitSet.VisitedBy})"
 
// attach table layout to grid Items
ensure gridLayout TableLayout kind:"table"
update gridLayout TableLayout columnSizes:"AutoSize" showFields:"VisitedBy,CustomerType,Customer,Location,Probability,Remarks"
 
ensure gridLayout MapLayout kind:"map"
update gridLayout MapLayout locationField:"Location" colorField:"UserColor" shapeField:"MapPinShape" groupByField:"VisitedBy" strokeField:"MapLineStroke"

• Output table layout

// add Table, a form content layout with table layout
ensure formLayoutContent Table
update formLayoutContent Table direction:"vertical" position:"flexCenter" gridLayouts:"VisitSet.TableLayout"

• Output map layout

// add Map, a form content layout with map layout
ensure formLayoutContent Map
update formLayoutContent Map position:"flexCenter" direction:"horizontal" gridLayouts:"VisitSet.MapLayout"
 
update formLayoutContent Map allowToSwitchLayouts:"Table"
update formLayoutContent Table allowToSwitchLayouts:"Map"

• Output mapping

 // add a mapping variable
 ensure variable VarMappingVisit kind:"mapping"
 update variable VarMappingVisit fromForm:"FormVisit" toForm:"FormOutputVisit" toGrid:"VisitSet"
 ensure variableMappingField to:"VisitedBy" fromContext:"row.createdBy"
 ensure VariableMappingField to:"CustomerRowId" fromField:"SysRowId"
 ensure VariableMappingField to:"CustomerType" fromField:"CustomerType"
 ensure VariableMappingField to:"Probability" fromField:"Probability"
 ensure VariableMappingField to:"Customer" fromField:"Customer"
 ensure VariableMappingField to:"Location" fromField:"Location"
 ensure VariableMappingField to:"Remarks" fromField:"Remarks"

// add condition variable to get my visits only
 
ensure variable VarConditionMyVisitMap kind:"condition"
update variable VarConditionMyVisitMap sourceForm:"FormVisit"
update variable VarConditionMyVisitMap condition:"context:row.createdBy == context:caller.entUserId"

• Spreadsheet report

// Add report ReportVisitMap for visit map
 
ensure report ReportVisitMap kind:"spreadsheet"
 
update report ReportVisitMap fromSpreadsheet:"VisitSheet"
update report ReportVisitMap outputForm:"FormOutputVisit" outputFormMapping:"VarMappingVisit"

// Add report ReportMyVisitMap to see my visit only
 
ensure report ReportMyVisitMap kind:"spreadsheet"
 
update report ReportMyVisitMap fromSpreadsheet:"VisitSheet"
update report ReportMyVisitMap outputForm:"FormOutputVisit" outputFormMapping:"VarMappingVisit"
update report ReportMyVisitMap filterCondition:"VarConditionMyVisitMap"

b. VisitInfo

• Output form

// add output form for the report
 
ensure Form FormOutputVisitInfo
 
ensure Section Details
 
ensure field CustomerType type:"pickText"
update field CustomerType source:"VarSetOfTextCustomerType"
ensure Field Customer type:"text"
ensure Field Address type:"location"
ensure field Remarks type:"paragraph"

• Output layout

// add ReportLayout, a form content layout
ensure FormLayoutContent ReportLayout
update FormLayoutContent ReportLayout direction:"vertical" displayLabel:"Visit Info" position:"start" fields:"CustomerType,Customer,Address,Remarks"

• Query

// Add report ReportVisitInfo for visit information
 
ensure report ReportVisitInfo kind:"query"
update report ReportVisitInfo inputForm:"FormOutputVisit" outputForm:"FormOutputVisitInfo" fromSpreadsheets:"VisitSheet" neoQL:"select\n ${ss:s4.Details.Customer} as ${out:Details.CustomerType},\n ${ss:s4.Details.Customer} as ${out:Details.Customer},\n ${ss:s4.Details.Location} as ${out:Details.Address},\n ${ss:s4.Details.Remarks} as ${out:Details.Remarks}\n from ${ss}\n where\n ${ctx:row.type} = ${ss:s4}\n and ${ctx:row.id} = ${arg:selectedGridId.selectedGridRowId.CustomerRowId}"

c. ReportMonthlySales

• Input form

// add input form for the report
ensure form FormFilterMonthly
 
ensure section Details
 
ensure field DateRange type:"dateRange"
update field DateRange fromDefaultVarId:"startOfMonth"
update field DateRange toDefaultVarId:"endOfMonth"

• Output form

// FormOutputMonthlySales => ReportMonthlySalesTotalAmount + ReportMonthlySalesTotalOrder + ReportMonthlySalesOrderCancelled + ReportMonthlySalesNewCustomers + ReportMonthlySalesLastMonthSale
 
// add output form for the report
ensure Form FormOutputMonthlySales
 
ensure Section Summary
 
ensure Field TotalSale type:"number"
update Field TotalSale prefix:"VarTextRupeeSymbol"
ensure Field TotalOrders type:"number"
ensure Field OrdersCancelled type:"number"
ensure Field NewCustomers type:"number"
ensure Field LastMonthSale type:"number"
update Field LastMonthSale prefix:"VarTextRupeeSymbol"

• Output layout

// add ReportLayout, a form content layout
 
ensure formLayoutContent ReportLayout
update formLayoutContent ReportLayout direction:"vertical" position:"start" Fields:"TotalSale,TotalOrders,OrdersCancelled,NewCustomers,LastMonthSale"
update formLayoutHeader showEnterprise:"yes"

• Queries

// add report ReportMonthlySalesTotalAmount for total amount in a month
ensure report ReportMonthlySalesTotalAmount kind:"query"
update report ReportMonthlySalesTotalAmount inputForm:"FormFilterMonthly" outputForm:"FormOutputMonthlySales" fromSpreadsheets:"OrderBook" 
update report ReportMonthlySalesTotalAmount neoQl:"Select sum(${ss:s6.Summary.TotalBill}) as ${out:Summary.TotalSale}\nfrom ${ss}\nwhere ${ctx:row.type} = ${ss:s6}\nand ${ss:s6.Details.Date} >= ${in:Details.DateRange.from}\nand ${ss:s6.Details.Date} <= ${in:Details.DateRange.to}"
 
// add report ReportMonthlySalesTotalOrder for total orders in a month
ensure report ReportMonthlySalesTotalOrder kind:"query"
update report ReportMonthlySalesTotalOrder inputForm:"FormFilterMonthly" outputForm:"FormOutputMonthlySales" fromSpreadsheets:"OrderBook"
update report ReportMonthlySalesTotalOrder neoQl:"Select count(*) as ${out:Summary.TotalOrders}\nfrom ${ss}\n where ${ctx:row.type} = ${ss:s6}\nand ${ss:s6.Details.Date} >= ${in:Details.DateRange.from}\nand ${ss:s6.Details.Date} <= ${in:Details.DateRange.to}"
 
// add report ReportMonthlySalesOrderCancelled for orders cancelled in a month
ensure report ReportMonthlySalesOrderCancelled kind:"query"
update report ReportMonthlySalesOrderCancelled inputForm:"FormFilterMonthly" outputForm:"FormOutputMonthlySales" fromSpreadsheets:"OrderBook"
update report ReportMonthlySalesOrderCancelled neoQl:"Select count(*) as ${out:Summary.OrdersCancelled}\n from ${ss}\n where ${ctx:row.type} = ${ss:s6}\n and ${ss:s6.Details.Date} >= ${in:Details.DateRange.from}\nand ${ss:s6.Details.Date} <= ${in:Details.DateRange.to}\nand ${ss:s6.Summary.OrderStatus.optionId} == 'Cancelled'"
 
// add report ReportMonthlySalesNewCustomers for new customer in a month
ensure report ReportMonthlySalesNewCustomers kind:"query"
update report ReportMonthlySalesNewCustomers inputForm:"FormFilterMonthly" outputForm:"FormOutputMonthlySales" fromSpreadsheets:"VisitSheet,OrderBook"
update report ReportMonthlySalesNewCustomers neoQl:"Select count(*) as ${out:Summary.NewCustomers}\n from ${ss} as main\n where ${ctx:row.type} = ${ss:s6}\n and ${ctx:row.createdOn} >= ${in:Details.DateRange.from}\n  and ${ctx:row.createdOn} <= ${in:Details.DateRange.to}\n and ${ss:s6.Details.VisitSheetRowId} NOT IN  (select distinct ${ctx:row.id}\n from ${ss} as sub\n where ${ctx:row.type} = ${ss:s4}\n and ${ctx:row.createdOn} <= ${in:Details.DateRange.to})\n group by ${ss:s6.Details.VisitSheetRowId}"
 
// add report ReportMonthlySalesLastMonthSale for total sales last month
ensure report ReportMonthlySalesLastMonthSale kind:"query"
update report ReportMonthlySalesLastMonthSale inputForm:"FormFilterMonthly" outputForm:"FormOutputMonthlySales" fromSpreadsheets:"OrderBook"
update report ReportMonthlySalesLastMonthSale neoQL:"Select COALESCE(sum(${ss:s6.Summary.TotalBill}), 0)  as ${out:Summary.LastMonthSale}\n from ${ss}\n where ${ctx:row.type} = ${ss:s6}\n and ${ctx:row.createdOn} >= DATE_ADD_MILLIS(${in:Details.DateRange.from} , -30, 'day')\n and ${ctx:row.createdOn} < ${in:Details.DateRange.from}"

• Composite report

// add report ReportMonthlySales, a composite report
ensure report ReportMonthlySales kind:"composite"
update report ReportMonthlySales inputForm:"FormFilterMonthly" outputForm:"FormOutputMonthlySales"
update report ReportMonthlySales mergeReports:"ReportMonthlySalesTotalAmount,ReportMonthlySalesTotalOrder,ReportMonthlySalesOrderCancelled,ReportMonthlySalesNewCustomers,ReportMonthlySalesLastMonthSale"

d. MonthlyAttendance

• Output form

// add output form for the report
ensure form FormOutputAttendance
 
//** grid AttendanceSet
ensure grid AttendanceSet
 
ensure field Name type:"pickUser"
update field Name dataSourceRoles:"Employee"
ensure field TotalHours type:"number"
ensure field HourlyWage type:"number"
ensure field Payment type:"number"
update field Payment formula:"${f:AttendanceSet.TotalHours} * (${f:AttendanceSet.HourlyWage})"
 
update grid AttendanceSet indexFieldName:"Sr No."
 
// add a table layout to the grid
 
ensure gridLayout TableLayout kind:"table"
update gridLayout TableLayout columnSizes:"AutoSize" showFields:"Name,TotalHours,HourlyWage,Payment"
 
//** section Summary
ensure section Summary
 
ensure field TotalHours type:"number"
update field TotalHours formula:"SUM(${f:AttendanceSet.TotalHours})"
ensure field TotalPayment type:"number"
update field TotalPayment formula:"SUM(${f:AttendanceSet.Payment})"

• Output layout

// add SummaryLayout, a form content layout
 
ensure formLayoutContent SummaryLayout
update formLayoutContent SummaryLayout direction:"horizontal" position:"flexCenter" fields:"TotalHours,TotalPayment"
 
// add ReportLayout, a form content layout
ensure formLayoutContent ReportLayout
update formLayoutContent ReportLayout direction:"vertical" position:"flexCenter" gridLayouts:"AttendanceSet.TableLayout"
update formLayoutContent ReportLayout position:"end" contentLayouts:"SummaryLayout"

• Query

ensure Report ReportMonthlyAttendance kind:"query"
update Report ReportMonthlyAttendance inputForm:"FormFilterMonthly" outputForm:"FormOutputAttendance" fromSpreadsheets:"AttendanceSheet,EmployeeMaster"
update Report ReportMonthlyAttendance neoQL:"WITH q1 AS (\n  SELECT entUserId as `eUserId`\n  FROM `DevWorldDb`.`neome`.`ent_user`\n   WHERE type = #{GetEntTable(${ctx:ent.id})}),\n  q2 AS (\n  SELECT rCb as `eUserId` ,\n  (SUM(${ss:s7.Details.OutTime.captureTime} - ${ss:s7.Details.InTime.captureTime})) as `timeDiff`\n  FROM ${ss}\n  WHERE ${ctx:row.type} = ${ss:s7}\n   and ${ctx:row.createdOn} >= ${in:Details.DateRange.from}\n  and ${ctx:row.createdOn} <= ${in:Details.DateRange.to}\n group by rCb),\n q3 AS (\n    SELECT\n     ${ss:s8.Details.Employee.value} as eUserId,\n    ${ss:s8.Details.HourlyWage} AS `salary`\n    FROM ${ss}\n     WHERE ${ctx:row.type} = ${ss:s8})\n SELECT\n q1.eUserId as ${out:AttendanceSet.Name}, (SUM(q2.timeDiff)/3600000) as ${out:AttendanceSet.TotalHours}, MAX(q3.salary) as ${out:AttendanceSet.HourlyWage}\n FROM\n  q1\n LEFT JOIN q3 ON q3.eUserId = q1.eUserId\n LEFT JOIN q2 ON q1.eUserId = q2.eUserId\nGROUP BY\n   q1.eUserId,q2.eUserId,q3.eUserId;"

Frontend

1. Actions

a. AddVisit

ensure Action AddVisit kind:"rowInsert"
 
update Action AddVisit spreadsheet:"VisitSheet"
update Action AddVisit icon:"AddLocationRounded"
update Action AddVisit sendMessageToInbox:"yes"

b. AddAllowance

ensure Action AddAllowance kind:"rowInsert"
 
update Action AddAllowance spreadsheet:"AllowanceSheet"
update Action AddAllowance icon:"AddRounded"
update Action AddAllowance sendMessageToInbox:"yes"

c. AddSize

ensure Action AddSize kind:"rowInsert"
 
update Action AddSize spreadsheet:"SizeMaster"
update Action AddSize icon:"AddRounded"

d. AddType

ensure Action AddType kind:"rowInsert"
 
update Action AddType spreadsheet:"TypeMaster"
update Action AddType icon:"AddRounded"

e. AddItem

ensure Action AddItem kind:"rowInsert"
 
update Action AddItem spreadsheet:"ItemMaster"
update Action AddItem icon:"AddRounded"
update Action AddItem sendMessageToInbox:"yes"

f. AddOrder

ensure Action AddOrder kind:"rowInsert"
 
update Action AddOrder spreadsheet:"OrderBook"
update Action AddOrder icon:"AddShoppingCartRounded"
update Action AddOrder sendMessageToInbox:"yes"

g. ReportVisitMap

ensure Action ReportVisitMap kind:"report"
 
update Action ReportVisitMap label:"Visit Map"
update Action ReportVisitMap report:"ReportVisitMap"
update Action ReportVisitMap outputFormContentLayout:"Map"
update Action ReportVisitMap icon:"MapRounded"

h. TypeMaster

ensure Action TypeMaster kind:"spreadsheetEditor"
 
update Action TypeMaster spreadsheet:"TypeMaster"
update Action TypeMaster spreadsheetLayout:"ListLayout"
update Action TypeMaster icon:"CategoryRounded"

i. SizeMaster

ensure Action SizeMaster kind:"spreadsheetEditor"
 
update Action SizeMaster spreadsheet:"SizeMaster"
update Action SizeMaster spreadsheetLayout:"ListLayout"
update Action SizeMaster icon:"CategoryRounded"

j. ItemMaster

ensure Action ItemMaster kind:"spreadsheetEditor"
 
update Action ItemMaster spreadsheet:"ItemMaster"
update Action ItemMaster spreadsheetLayout:"TableLayout"
update Action ItemMaster icon:"FeaturedPlayListRounded"

k. ReportMonthlySales

ensure Action ReportMonthlySales kind:"report"
 
update Action ReportMonthlySales report:"ReportMonthlySales"
update Action ReportMonthlySales outputFormContentLayout:"ReportLayout"
update Action ReportMonthlySales icon:"ListAltRounded"

l. OrderBook

ensure Action OrderBook kind:"spreadsheetEditor"
 
update Action OrderBook spreadsheet:"OrderBook"
update Action OrderBook spreadsheetLayout:"KanbanLayout"
update Action OrderBook icon:"AddShoppingCartRounded"

m. VisitSheet

ensure Action VisitSheet kind:"spreadsheetEditor"
 
update Action VisitSheet spreadsheet:"VisitSheet"
update Action VisitSheet spreadsheetLayout:"TableLayout"
update Action VisitSheet icon:"EditNoteRounded"

n. AddAttendance

ensure action AddAttendance kind:"rowInsert"
 
update Action AddAttendance spreadsheet:"AttendanceSheet"
update Action AddAttendance title:"Todays Attendance"
update Action AddAttendance subtitle:"Submit your todays Attendance before expiry"
update Action AddAttendance icon:"LibraryAddRounded"
update Action AddAttendance sendMessageToInbox:"yes"

o. ReportMonthlyAttendance

ensure Action ReportMonthlyAttendance kind:"report"
 
update Action ReportMonthlyAttendance report:"ReportMonthlyAttendance"
update Action ReportMonthlyAttendance outputFormContentLayout:"ReportLayout"
update Action ReportMonthlyAttendance icon:"ArticleRounded"
update Action ReportMonthlyAttendance sendMessageToInbox:"yes"

p. AddEmployee

ensure action AddEmployee kind:"rowInsert"
 
update Action AddEmployee spreadsheet:"EmployeeMaster"
update Action AddEmployee icon:"AddCircleOutlineRounded"
update Action AddEmployee sendMessageToInbox:"yes"

q. EmployeeMaster

ensure Action EmployeeMaster kind:"spreadsheetEditor"
 
update Action EmployeeMaster spreadsheet:"EmployeeMaster"
update Action EmployeeMaster spreadsheetLayout:"TableLayout"
update Action EmployeeMaster icon:"EditNoteRounded"

r. ReportVisitInfo

ensure Action ReportVisitInfo kind:"report"
 
update Action ReportVisitInfo report:"ReportVisitInfo"
update Action ReportVisitInfo outputFormContentLayout:"ReportLayout"
update Action ReportVisitInfo sendMessageToInbox:"no"
 
// Assign this action to grid VisitSet
 
goto form FormOutputVisit
goto grid VisitSet
ensure gridMenuRow action:"ReportVisitInfo" roles:"Employee" showMessageTooltip:"yes"

s. ReportMyVisitMap

ensure Action ReportMyVisitMap kind:"report"
 
update Action ReportMyVisitMap label:"My Visit Map"
update Action ReportMyVisitMap report:"ReportMyVisitMap"
update Action ReportMyVisitMap outputFormContentLayout:"Map"
update Action ReportMyVisitMap icon:"MapRounded"

2. Groups

a. Add groups

ensure group HR
ensure Group OrderBook
ensure Group NoticeBoard
ensure group VisitBook
ensure group ItemMaster
ensure group ChitChat

b. Associate actions

goto group HR
ensure GroupAction action:"AddAllowance" roles:"Employee" notAllowedRoles:"Owner" menuGroup:"Allowance"
ensure GroupAction action:"AddAttendance" roles:"Owner" menuGroup:"Attendance"
ensure GroupAction action:"ReportMonthlyAttendance" roles:"Owner" menuGroup:"Attendance"
ensure GroupAction action:"AddEmployee" roles:"Owner" menuGroup:"Employee"
ensure GroupAction action:"EmployeeMaster" roles:"Owner" menuGroup:"Employee"
update Group HR pinnedActions:"AddAttendance,AddAllowance,EmployeeMaster"
update Group HR pinnedActionsMobile:"AddAllowance,AddAttendance"
 
goto group OrderBook
ensure GroupAction action:"AddOrder" roles:"Employee"
ensure GroupAction action:"ReportMonthlySales" roles:"Owner"
ensure GroupAction action:"OrderBook" roles:"Owner"
update Group OrderBook pinnedActions:"AddOrder,OrderBook"
update Group OrderBook pinnedActionsMobile:"AddOrder,OrderBook"
 
goto group NoticeBoard
update group NoticeBoard groupPermissions:"Employee" chatPermissions:"Owner"
 
goto group ItemMaster
ensure GroupAction action:"AddType" roles:"Owner" menuGroup:"ADD"
ensure GroupAction action:"AddSize" roles:"Owner" menuGroup:"ADD"
ensure GroupAction action:"AddItem" roles:"Owner" menuGroup:"ADD"
ensure GroupAction action:"TypeMaster" roles:"Owner" menuGroup:"MASTER"
ensure GroupAction action:"SizeMaster" roles:"Owner" menuGroup:"MASTER"
ensure GroupAction action:"ItemMaster" roles:"Owner" menuGroup:"MASTER"
update group ItemMaster pinnedActions:"ItemMaster,TypeMaster,SizeMaster"
update group ItemMaster pinnedActionsMobile:"ItemMaster"
 
goto group VisitBook
ensure GroupAction action:"AddVisit" roles:"Employee" notAllowedRoles:"Owner"
ensure GroupAction action:"ReportVisitMap" roles:"Owner"
ensure GroupAction action:"ReportMyVisitMap" roles:"Employee" notAllowedRoles:"Owner"
ensure GroupAction action:"VisitSheet" roles:"Owner"
update Group VisitBook pinnedActions:"AddVisit,ReportVisitMap,VisitSheet,ReportMyVisitMap"
update Group VisitBook pinnedActionsMobile:"AddVisit,ReportVisitMap,ReportMyVisitMap"
 
goto group ChitChat
update group ChitChat groupPermissions:"Employee" chatPermissions:"Employee"

3. Deeplinks

a. AddOrder

// Create deeplinks
 
// share order form to customers for them to order tiles
ensure deeplink DlAddOrder kind:"spreadsheetInsert"
 
update deeplink DlAddOrder spreadsheet:"OrderBook"
update deeplink DlAddOrder visibilityConstraint:"enforceUserSignIn"
update deeplink DlAddOrder creationRoles:"Employee"
update deeplink DlAddOrder showHeader:"yes"
update deeplink DlAddOrder showEnterprise:"yes"
update deeplink DlAddOrder showSeparator:"yes"
update deeplink DlAddOrder showEnterprisePreviewImage:"yes"
update deeplink DlAddOrder expiry:"noExpiry"
 
// to show success message after order
 
update deeplink DlAddOrder successMessage:"Thank you, ${p1} for your purchase of ${p2} with us!"
 
ensure deeplinkProp prop:"successMessage" param:"p1" value:"Name" binder:"field"
ensure deeplinkProp prop:"successMessage" param:"p2" value:"TotalBill" binder:"field"
 
// add a button to order again
 
update deeplink DlAddOrder showRepeatButton:"yes"
update deeplink DlAddOrder repeatButtonLabel:"Order Again"
 
// add hyperlink to show your website link
 
ensure variable VarHyperlinkWebsite kind:"Hyperlink"
update variable VarHyperlinkWebsite value:"https://neome.ai/" displayText:"Tiles Company"
update variable VarHyperlinkWebsite kind: "general"
 
update deeplink DlAddOrder hyperlinkVars:"VarHyperlinkWebsite"

b. VisitMapReport

// share visit report to whatsapp via deeplink
ensure deeplink DlVisitMapReport kind:"report"
 
update deeplink DlVisitMapReport report:"ReportVisitMap" outputFormContentLayout:"Map"
update deeplink DlVisitMapReport creationRoles:"Employee"
update deeplink DlVisitMapReport visibilityConstraint:"enforceUserSignIn"
update deeplink DlVisitMapReport expiry:"noExpiry"

c. FormOrder

ensure deeplink DlFormOrder kind:"spreadsheetRow"
 
update deeplink DlFormOrder spreadsheet:"OrderBook" formContentLayout:"DlContentlayout"
update deeplink DlFormOrder creationRoles:"Employee"
update deeplink DlFormOrder visibilityConstraint:"enforceUserSignIn"
update deeplink DlFormOrder expiry:"noExpiry"

Deploy

deploy enterprise

Try your creation

To add users to enterprise application Tiles company through a UI,

Open the route production by clicking the link https://web.neome.ai/production (opens in a new tab).
Add users and give them the roles.

Alternatively you can add users by executing following scripts in the route terminal (opens in a new tab).

a. Try for yourself

ensureSelf user roles:"Owner,Employee"

b. Try for others

add user name:"SomeOwner" handle:"owner@yourcompany.com" roles:"Owner"
add user name:"SomeEmployee1" handle:"salesagent1@yourcompany.com" roles:"Employee"
add user name:"SomeEmployee2" handle:"salesagent2@yourcompany.com" roles:"Employee"
add user name:"SomeEmployee3" handle:"salesagent3@yourcompany.com" roles:"Employee"
 
inviteAll users

Build it Automate it