SpreadJS 15
Features / TableSheet / TableSheet Relationships
In This Topic
    TableSheet Relationships
    In This Topic

    Tablesheet provides the ability to bind multiple data tables in the same view by creating relationships.

    As shown in the flowchart image above, tablesheet relationships define the related table and the related field and it can be widely used in table structures with primary and foreign key relationships. Both the data tables in the relationship act in a two-way binding in the Data Manager and all the properties from both the tables are accessible.

    The following steps show how to add a tablesheet relationship between tables "Customers" and "Sales".

    1. Add multiple tables to the data manager using the addTable method.
      JavaScript
      Copy Code
      //add customer table to dataManager
      var customerTable = dataManager.addTable("customerTable", {
          remote: {
              read: {
                  url: 'https://demodata.grapecity.com/wwi/api/v1/customers'
      
              }
          }
      });
      
      // add sales table to dataManager
      var salesTable = dataManager.addTable("salesTable", {
          remote: {
              read: {
                  url: 'https://demodata.grapecity.com/wwi/api/v1/sales?PageNumber=10&PageSize=500'
              }
          }
      });
      

    2. Add a relationship between tables using the addRelationship method.

      It accepts the source - table, field name, relationship name, and the target - table, field name, relationship name.

      In the following code sample, the "SalesTable" is the target table and the "customerKey" is the foreign key binding both the tables.

      JavaScript
      Copy Code
      //add relationship between sales table and customer table
      dataManager.addRelationship(customerTable, "customerKey", "mySales", salesTable, "customerKey", "myCustomer");
      

    3. Create a multi-column drop down view to be used in relational View
      JavaScript
      Copy Code
      //add custom view
      customerTable.fetch().then(function () {
          var selectView = customerTable.addView("customersView",
              [
                  { value: 'customerKey' },
                  { value: 'customer' },
                  { value: 'category' }
              ]);
      
          // create a multi column drop down to be used in relational View
          var multiSelectStyle = {
              formatter: '{{=IFERROR(CONCAT(PROPERTY(@, "customer"), ", ", PROPERTY(@, "customerKey")), "")}}',
              cellButtons: [
                  {
                      imageType: "dropdown",
                      command: "openMultiColumn",
                      useButtonStyle: true,
                  }
              ],
              dropDowns: [
                  {
                      type: "multiColumn",
                      option: {
                          width: 400,
                          height: 300,
                          dataSource: selectView,
                          bindingInfos: [
                              { name: "customerKey", size: 100, caption:"Customer Key" },
                              { name: "customer", size: "*", caption: "Customer" },
                              { name: "category", size: 100, caption: "Category" }
                          ]
                      }
                  }
              ]
          };
      

    4. Bind the view to tablesheet.
      JavaScript
      Copy Code
          var myView = salesTable.addView("myView", [
              { value: "saleKey", width: 100 , caption:"Sale Key"},
              { value: "salesPerson", width: 150, caption:"Sales Person" },
              { value: "myCustomer", width: 350, caption: "Customer Information", style: multiSelectStyle  },
              { value: "address", width: 350, caption: "Address" },
              { value: "stockItem", width: 350, caption: "Stock Item" },
              { value: "quantity", width: 100, caption: "Quantity" }
          ]);
          myView.fetch().then(function () {
              console.log(myView);
              // Set custom view into tableSheet.
              tableSheet.setDataView(myView);
          });
      });