Add a Derived column

The derived column is used to automatically copy a value from another table. You can use it in two ways:


1) The derived column load a value from an external table based on the value selected in a Dropdown column in the same table.


Example: given two tables Customer and Orders. In every new order you want to add in addition to Customer Name also the Address, City and Telephone.


Procedure:

  1. Create the Customers and Orders tables.
  2. Add Name, Address, City, and Phone columns to the Customers table.
  3. Create a Dropdown column in Orders table called Customer that takes data from the Name column of the Customer table.
  4. Add a Derivated column in the Orders table.
  5. Enter Address in column name.
  6. In Derivated column settings choose, under "Column to use as a reference," the Customer column.
  7. Under "Column in the external table from which to take the value" choose Address column.
  8. Repeat steps 4, 5, 6 and 7 for other Derivated columns City and Telephone.

 

2) The Derived column load a value from an external table by "Created by" column (this is a system column).


Example: we have a table "Orders" in which the sales agents enter new orders. For every new entry you want automatically add the Fees associated with each agent.


Procedure:

  1. Create an Agents and Orders tables.
  2. Add a Name column to the Agents table.
  3. Add a numeric column called Fees.
  4. Enter data in the Agents table. In the Name column you must be entered user names used by agents registered in BaseBear. You can view the username on the Share page of a database.
  5. In Orders table add Order No., Description, Date columns.
  6. Add in Orders table a Derived column called Fees.
  7. Set Agents table in "External table from which to take the value".
  8. Set Name in "column in the table to use as a reference to".
  9. Set Fees in "column in the external table from which to take the value".
  10. Now for every new order the Fees of the Agent added automatically. The value source is the Agents table.