The is no one size fits all solution for populating a data-grid based on a data source, this is because every data-source may contain different columns and every data-grid could contain different components. In this article we outline one common example/patten for populating a data-grid based on a data-source.
⚠️ This is an advanced level tutorial. To follow on you will need a basic understanding of both data sources and JavaScript.
Let's use an example of a data-source containing inventory items, where each row contains the item, quantity and location as shown in the image below. We will build a form where a user can select a location and automatically list all associated items and their quantities.
Set up your SharePoint drop-down
Below are the steps to set up the SharePoint drop down to show unique Locations. When a user selects an item in this drop-down menu it will be used as a trigger to populate the data-grid.
Get the Id for your data source and note this value down as it will be used later
Drag and drop a SharePoint data component into your form and link it to the datasource from step 1.
Set the label to the field you wish to display in our example this will be "Location". You will notice when previewing this drop-down menu that some locations will be duplicated. This is because Team Forms considers all columns (including hidden ones) when checking for duplicate data.
Under the data tab for your SharePoint drop down scroll down to the field setting and select the "Location" column. This will configure the component to only look at (and store) the relevant columns when checking for duplicates.
Setup Your data grid
Drag and drop a data-grid component into your form.
Drag and drop components into data-grid for each column you wish to display. It is important to select the right component type to match the data type of the column in your data-source. For example a number component should be used to show the "Quantity" column. Mismatches in the data-types can result in columns appearing blank in later steps. For our example we will use a textfield for the item name and a number field for the quantity
Populate the data-grid with JavaScript
The last step is to use custom JavaScript to populate the data-grid. This step will be specific to each form/datasource so you will need to adapt the code example below to suit your specific scenario.
Open the component settings for the SharePoint drop down and navigate to the OnChange setting under the logic tab. The on change setting allows you to provide custom JavaScript code that will run any time the value of the component is updated.
Which the onChange enter JavaScript the will retrieve the data source information, filter it and then set the value of the data grid. We have provided the example code below, however you will need to adapt it form your specific form and requirements.
// Create a function
async function updateDataGrid(){
// Get all items form the data-source using its id (from step 1)
const items = await tf.getDataSource('2ia86v6lbv14')
// Apply a filter based on data in the form
const filteredItems = items.filter(i => i?.Location === data.storageLocation.Location)
// IMPORTANT: Use a "Map" to rename the columns from the data source to match the
// names of field names used in the data-grid
const gridItems = filteredItems.map(i => {
return {
item: i?.Item,
quantity: i?.Quantity,
comment: ''
}
})
// Set the value of the data-grid component programatically
instance.root.getComponent('inventoryList').setValue(gridItems)
}
// Invoke the function
updateDataGrid()
It is important to note that when setting the value of a data-grid you must ensure the value you provide exactly matches the schema used by your form. For example in the code example above we use a map function to change rename the columns form my data source to match the camel case used in my form.
