Having trouble with the video? Try this link.


Let’s say you want to include budget limits for different departments as a part of an app, but you also want the budget limit to be updated with every new item that goes through.

To do this, first go to the Masters tab. Create a Master that includes the name of the department and the budget. Be sure to note the exact name of each of the columns and the Master. Save your Master before exiting.

In the form of your app, create a dropdown field called ‘Department’ and list out the different departments. Also be sure to note the Field ID.

Then make a currency field called ‘Total Budget’. This field will display the full budget available at the start of the period. Make it a computed field and enter this formula:

 

DLOOKUP(DepartmentBudgets.Budget,DepartmentBudgets.Deptartmentname=Department_)

 

The formula shows (1) the Master Name, dot (2) the Column in the Master you want to display, comma (3) the Master Name, dot (4) the column used to select the right budget. In our case, we want the DeptName column in the Master to match with the value chosen in the Department dropdown field we just created.

Now create another currency field called ‘Amount’. The user will enter the amount they plan to spend from their budget here.

Finally, we’ll make another currency field called ‘Available Budget’. We want this field to display the budget remaining after the previous requests.

This is another computed field and here is the formula:

 

Total_Budget-DSUM(self.Amount,AND(self.CreatedAt<CreatedAt,self.Department_=Department_))

 

This formula starts with the total budget and subtracts the amount made from previous items for the same department.

Now, let’s look at the live form.

Here’s what the form looks like for a user on the first request – they have the full budget available to them. On the second request, you can see that the amount from the first was subtracted automatically.

Did this answer your question?