# Compute new records based on existing ones (e.g., sum of 2 records)?

Posted on

### Question :

Background
I have an inventory where I register the number of fruits I have in stock — both expected and actual quantities.

For some fruits (e.g., here Banana and Cherry), I might not have both records.

My table looks then like this:

``````| Fruit  | RecordType | Qty |
|--------|------------|----:|
| Apple  | Expected   |  13 |
| Apple  | Actual     |  17 |
| Banana | Actual     |   8 |
| Cherry | Expected   |  42 |
| Durian | Actual     |   0 |
| Durian | Expected   |   1 |
``````

This table gets partially updated over time (i.e. I might only change the `Apple | Actual` quantity, or add the `Banana | Expected` record).

Question
➥ Is it possible to automatically compute new records based on the existing ones? If yes, how?

In our example, I might want to compute the `Variation` between the actual and the expected value: i.e. add the `{fruit} | Variation | [{fruit | Actual}(Qty) − {fruit | Expected}(Qty)]` record when I have both `Actual` and `Expected` records for a given `{fruit}`.

In our case, I’d like that my table becomes this:

``````| Fruit  | RecordType | Qty |
|--------|------------|----:|
| Apple  | Expected   |  13 |
| Apple  | Actual     |  17 |
| Banana | Actual     |   8 |
| Cherry | Expected   |  42 |
| Durian | Actual     |   0 |
| Durian | Expected   |   1 |
| Apple  | Variation  |   4 | ← newly computed record
| Durian | Variation  |  -1 | ← newly computed record
``````

You can see that there is no `Variation` record for `Banana` or `Cherry`, because they are missing their `Expected` or `Actual` record, respectively.

``````select fruit, 'Variation' as recordtype, act_qty-exp_qty as qty from