I have 2 tables, one of those table is master formula which has some records like this :

Table1: MasterFormula

```
No Description Formula
1 Occupancy
2 Leased
3 Rate Consumed
4 Rate street lighting
5 AVG Installed Capacity
6 Consumed a: Minimum Charges
7 Consumed a: Minimum Charges (*)
8 Consumed b: other than Minimum Charges
9 Consumed b: other than Minimum Charges [3]*[8]
10 Total Consumed a+b [6]+[8]
11 Total Consumed a+b [7]+[9]
12 Street lighting [4]*[11]/100
13 Admin fee ([11]+{12])/9
14 TOTAL SALES - B2 [11]+[12]+[13]
```

Table2: TransactionFormula

Only has 2 fields, No & Amount

the formula fields means, we sum the Amount from TransactionFormula table for each No., for example if the formula is [3]+[8] means, we sum the Amount from No=3 added with the Amount from No=8 and we added records on TransactionFormula for row 9

Here's the example result for TransactionFormula records

```
No Amount
1 100
2 100
3 10
4 3
5 10
6 10
7 20
8 10
9 20 [3]+[8]
10 20 [6]+[8]
11 40 [7]+[9]
12 1.2 [4]*[11]/100
13 4.58 ([11]+{12])/9
14 45.78 [11]+[12]+[13]
```

actually my design is calculating the formula based on the value of each rows. I made it because the user can freely adding any rows for a new formula. You know, excel can put any formula which calculate for each rows.

Here's i give you more details on what it looks like

```
CREATE TABLE [FormulaTemplate](
[BudgetID] [varchar](10) NOT NULL,
[SeqNo] [int] NOT NULL,
[Description] [varchar](100) NOT NULL,
[Description1] [varchar](100) NOT NULL,
[Formula] [varchar](50) NOT NULL
)
insert into FormulaTemplate values ('B.01',1,'Occupancy','%','')
insert into FormulaTemplate values ('B.01',2,'Leased','SQM','')
insert into FormulaTemplate values ('B.01',3,'Rate Consumed','IDR','')
insert into FormulaTemplate values ('B.01',4,'Rate street lighting','%','')
insert into FormulaTemplate values ('B.01',5,'AVG Installed Capacity','KVA','')
insert into FormulaTemplate values ('B.01',6,'Consumed a: Minimum Charges','KWH','')
insert into FormulaTemplate values ('B.01',7,'Consumed a: Minimum Charges (*)','IDR','')
insert into FormulaTemplate values ('B.01',8,'Consumed b: other than Minimum Charges','KWH','')
insert into FormulaTemplate values ('B.01',9,'Consumed b: other than Minimum Charges','IDR','[3]*[8]')
insert into FormulaTemplate values ('B.01',10,'Total Consumed a+b','KWH','[6]*[8]')
insert into FormulaTemplate values ('B.01',11,'Total Consumed a+b','IDR','[7]*[9]')
insert into FormulaTemplate values ('B.01',12,'Street lighting','IDR','[4]*[11]/100')
insert into FormulaTemplate values ('B.01',13,'Admin fee','IDR','([11]+{12])/9')
insert into FormulaTemplate values ('B.01',14,'TOTAL SALES - B2','IDR','[11]+[12]+[13]')
CREATE TABLE [BudgetTransaction](
[BudgetID] [varchar](10) NOT NULL,
[SeqNo] [int] NOT NULL,
[Amount] [money] NOT NULL
)
insert into BudgetTransaction values ('B.01',1,100)
insert into BudgetTransaction values ('B.01',2,100)
insert into BudgetTransaction values ('B.01',3,10)
insert into BudgetTransaction values ('B.01',4,3)
insert into BudgetTransaction values ('B.01',5,10)
insert into BudgetTransaction values ('B.01',6,10)
insert into BudgetTransaction values ('B.01',7,20)
insert into BudgetTransaction values ('B.01',8,10)
```

FormulaTemplate table is the master table for the formula of each BudgetID which maybe in the next time, i can add a new Seqno like SeqNo=15 which keep a new formula. The formula field is an expression formula for calculating amount field from BudgetTransaction table which has previous SeqNo.

The concept is like in excel, where the formula is based on rows.

Inside the UI programming for budget transaction, if we add a new seqno which that SeqNo inside the formula, then it automatically insert a new record in BudgetTransaction for the SeqNo which has formula..

For example :

insert into BudgetTransaction values ('B.01',1,100) -> just insert Seqno=1

insert into BudgetTransaction values ('B.01',2,100) -> just insert SeqNo=2

insert into BudgetTransaction values ('B.01',3,10) -> insert SeqNo=3 then insert SeqNo=9, because SeqNo=3 was calculated in formula for SeqNo=9 which has formula [3]+[8]. But since SeqNo=8 still not yet exists in BudgetTransaction table, so the amount for SeqNo=9 same as SeqNo=3 (10)

insert into BudgetTransaction values ('B.01',4,3) -> insert SeqNo=4, then insert SeqNo=12, because SeqNo=4 was calculated in formula for SeqNo=12 which has formula [4]*[11]/100. But since SeqNo=11 still not yet exists in BudgetTransaction table, so the amount for SeqNo=12 become 0

and so on...

Thanks..