Data Filter

Open the demo in new window can open the designer

There are several uses for the data filter:

1. Filter data by a static value:

   const spread = new GC.Spread.Sheets.Workbook('ss', { sheetCount: 1 });
   const ordersTable = spread.dataManager().addTable('Orders', {
       remote: {
           read: {
               url: 'https://demodata.mescius.io/northwind/api/v1/orders'
           }
       }
   });
   ordersTable.fetch().then(() => {
       const reportSheet = spread.addSheetTab(0, 'report1', GC.Spread.Sheets.SheetType.reportSheet);
       const templateSheet = reportSheet.getTemplate();
       // set value and binding for the template
       templateSheet.setTemplateCell(1, 0, {
           type: 'List',
           binding: `Orders[customerId]`,
           filter: {
               condition: { // filter value by static value.
                   and: [
                       {
                           column: "orderDate",
                           operator: "GreaterThan",
                           value: new Date("1997/1/1"),
                       },
                       {
                           column: "orderDate",
                           operator: "LessThan",
                           value: new Date("1997/12/31");
                       }
                   ]
               }
           }
       });
       templateSheet.setTemplateCell(1, 1, {
           type: 'List',
           binding: `Orders[freight]`,
       });
       templateSheet.setTemplateCell(1, 2, {
           type: 'List',
           binding: `Orders[shipName]`,
       });
       // refresh the report
       reportSheet.refresh();
   });
   

2. Filter data using a formula:

   1. The whole condition rule is formula:

   templateSheet.setTemplateCell(1, 0, {
       type: 'List',
       binding: `Orders[customerId]`,
       filter: {
           condition: { // filter value formula.
               formula: "=YEAR([@orderDate])=1997"
           }
       }
   });
   

   2. The condition rule is a formula on the column:

   templateSheet.setTemplateCell(1, 0, {
       type: 'List',
       binding: `Orders[customerId]`,
       filter: {
           condition: { // filter value formula.
               column: "=YEAR([@orderDate])",
               operator: "Equal",
               value: 1997
           }
       }
   });
   

   3. The column value is formula (Can also use the parameter in the formula):

   templateSheet.setTemplateCell(1, 0, {
       type: 'List',
       binding: `Orders[customerId]`,
       filter: {
           condition: { // filter value formula.
               column: "=YEAR([@orderDate])",
               operator: "Equal",
               formula: `=YEAR("1997-01-01")`
           }
       }
   });
   

3. Filter data using join tables:

   1. Add a table join relationship in the dataManager:

   const dataManager = spread.dataManager();
   const ordersTable = dataManager.addTable('Orders', {
       remote: {
           read: {
               url: 'https://demodata.mescius.io/northwind/api/v1/orders'
           }
       }
   });
   const employeesTable = dataManager.addTable('Employees', {
       remote: {
           read: {
               url: 'https://demodata.mescius.io/northwind/api/v1/employees'
           }
       }
   });
   // add relationship between ordersTable and employeesTable by employeeId.
   dataManager.addRelationship(ordersTable, "employeeId", "employeesTable", employeesTable, "employeeId", "ordersTable");
   templateSheet.setTemplateCell(1, 0, {
       type: 'List',
       binding: `Orders[customerId]`
   });
   templateSheet.setTemplateCell(1, 1, {
       type: 'List',
       binding: `Employees[firstName]`,
   });
   templateSheet.setTemplateCell(1, 2, {
       type: 'List',
       binding: `Orders[freight]`,
   });
   

   2. Add a table join filter in the filter condition:

   const dataManager = spread.dataManager();
   const ordersTable = dataManager.addTable('Orders', {
       remote: {
           read: {
               url: 'https://demodata.mescius.io/northwind/api/v1/orders'
           }
       }
   });
   const employeesTable = dataManager.addTable('Employees', {
       remote: {
           read: {
               url: 'https://demodata.mescius.io/northwind/api/v1/employees'
           }
       }
   });
   templateSheet.setTemplateCell(1, 0, {
       type: 'List',
       binding: `Orders[customerId]`
   });
   templateSheet.setTemplateCell(1, 1, {
       type: 'List',
       binding: `Employees[firstName]`,
       filter: {
           condition: { // add table join filter condition.
               column: "employeeId",
               operator: "equal",
               dataColumn: {
                   tableName: "Orders",
                   columnName: "employeeId"
               }
           }
       }
   });
   templateSheet.setTemplateCell(1, 2, {
       type: 'List',
       binding: `Orders[freight]`,
   });