Lookup and calculated fields in DB Studio

Show area names while storing their IDs, then calculate stock value from quantity and unit cost in the Prototype workshop sample.

Watch
2:34 · English narration; English captions are on by default.
What you'll learn

What you'll learn

You will configure a lookup for AreaID in Tool kits, add a calculated Stock value field, and check the stored ID and calculated value with a query.

Before you begin

Before you begin

Open https://db.pyroom.app/ on a desktop or tablet. Table design needs a larger screen; the query designer used at the end is desktop-only, so use a desktop to complete the whole guide.

Start with a fresh Prototype workshop sample. It contains fictional teaching records. If table design is new to you, follow Create a table first.

Make sure the interface is in English before you create the sample: open the Application menu and choose English under Language. The sample is created in the interface language.

Steps

Steps

  1. In the library, click All samples and create Prototype workshop. Open Work areas and check that it contains four records with IDs and area names.
  2. Open Tool kits, click Design, and select the AreaID field.
  3. Click Lookup Wizard…. In Lookup type, choose Table or query. Set Source to Work areas, Bound column to ID, and Display column to Area name. Click Save. The ID is stored; the area name is displayed.
  4. Click New field…, enter Stock value in Field name, and choose Calculated in Data type.
  5. Enter [Units]*[Unit cost] in Expression and press Tab. Set Result type to Currency.
  6. Click Save table. If a change preview appears, review the dependent objects and click Apply change. Switch to Data and find Stock value.
  7. Click the pencil in the Prototype kit P-101 row. In its record panel, choose Shape laboratory for AreaID, click Save, then Close panel. The row now displays the area name.
  8. Open the same record with its pencil again. Change Units from 2 to 5, leave Unit cost at 17.35, and click Save. Check that Stock value becomes 86.75, then click Close panel.
  9. Click New query, choose Design, open the SQL tab, and click Edit SQL.
  10. Enter SELECT "AreaID", "Stock value" FROM "Tool kits" WHERE "ID" = 1;, press Escape to leave the editor, and click Run. Check that the result shows AreaID 2 and Stock value 86.75.
Expected result / Common mistakes

Expected result

Prototype kit P-101 displays Shape laboratory while storing AreaID 2. Its Stock value is 86.75, calculated from 5 units at 17.35 each. Changing the inputs updates the calculated field without typing a total.

Common mistakes

  • Keep ID as Bound column and Area name as Display column. Swapping them changes what the lookup stores and shows.
  • Use square brackets around both field names in [Units]*[Unit cost], including the space in Unit cost.
  • Save the table design before switching to Data. Edit Units in the record panel rather than typing into Stock value.
  • The final query uses the English sample names exactly. If you created a Ukrainian sample, its table and field names differ.
  • Use a desktop for the final query designer steps. Table design is available on desktop and tablet.
Try it in DB Studio
Try it in DB StudioStart with a sample database
Open DB Studio
Related tutorials