Managing Internal Purchases by Using Driving Table and Data Mirror

Create internal shopping lists in a grid. Use the Driving Table feature to create a product list and the formula column to calculate the total of the purchase.

Introduction

You can create a stand-alone list of products using the Driving Table. Then use it in a grid as a dynamic list of available products.

The dynamic list is based on a Driving Table, maintained in the Table Grid Next Generation admin. You can save the table in the database using the Data Mirroring.

Configuration

Pre-requisites:

MySQL database which will be used to store the driving table content.
Driving table with products including the following columns:
- code
- name
- price
Table Grid with a dynamic list of products.

1. Add the database as a data source

Check out the guide on how to add a data source.

2. Create a Driving Table with products

Use the Driving Tables configuration guide to create a stand-alone table with products.

3. Configure the Data Mirroring

To save the data from the Driving Table, you need to add a Data Mirror.

Check the Data Mirror configuration guide for more details.

4. Add products to the Driving Table

5. Create a table grid with a dynamic list of products

Let's create a grid and name it Shopping list.

Then add the following columns:

Product: A single-select list column with dynamic options populated with the Driving Table Products.
A query to get a list of products and prices from the Driving Table.
```
SELECT Name, Price FROM Products
```
To display the price automatically, use the dynamic option mapping. You need to map the option label to display the product name and the option value to display the price.
Price: The formula column helps to display the product price per item.
You need to get the price column from the Driving Table together with the product name. Check the query used in the previous step.
Formula expression
```
return $(product).value;
```
Quantity: The integer column to specify the number of items for the purchase.
Total: The formula column that calculates the total price for a specific item. It also calculates the total sum of the whole order.
Formula to retrieve product price automatically
```
return $(Quanity)*$(Price)
```

Result

You can select the product from the dynamic list which is configured as a Driving Table.

When you select a product from the drop-down list, the price is added automatically to the grid. We’ve used a JavaScript-based formula column type to get the price.

The total quote for each product is calculated automatically using the formula column.

You can update the list of products and change prices in the stand-alone table used to store product-related information. This does not require changing your grid configuration.