Expense Tracker Question

[Tim Flick]

I have a expense tracker form that includes date, payee, sales tax, amount of purchase, gallons of gas, description, copy of receipt. I know what my available income per month and annually are. What I want to do is to be able to calculate is total spent each each minus available income to show savings each month and also to calculate the total spent for the year less available income per year

Thanks

T Flick

[Mike Schwartz]

Tim,

I don’t believe that there is an elegant way to accomplish what you want with the current capabilities of Tap Forms, but I can suggest a rather inelegant method:

ASSUMPTIONS:
(1) Each record in your Expense Tracker form contains a field with the relevant dollar amount for a single expense. I’m guessing that it’s your “Amount of Purchase” field, but for brevity below I’ll call it “Expense”. It will not matter if that field is direct-entry or calculated.
(2) Your Available Income may vary from month to month, and your yearly Available Income may vary from year to year. For brevity below, I substituted the word “Budget” for “Available Income”.

SETUP:
(1) Create the following new fields in your form:
(a) Monthly Budget — simple number field
(b) Yearly Budget — simple number field
(c) Monthly Remaining Budget — calculation field = (Monthly Budget)-(Expense)
(d) Yearly Remaining Budget — calculation field = (Yearly Budget)-(Expense)
(2) For each of the four new fields and also your Expense field, select “Total” from the Summary Calculation pop-up menu in the field definition area.

DATA ENTRY:
(Here’s where it gets a bit kludgy, but it’ll work.)
(1) For the FIRST record in a given year, enter a value for Monthly Budget and a value for Yearly Budget. For example, in your record for 1/03/2017 you might enter $1000 in Monthly Budget and $12000 in Yearly Budget.
(2) Now let’s assume your Expense for that first record is $90. The Monthly Remaining Budget will show a calculated value of $910, and the Yearly Remaining Budget will show a calculated value of $11910.
(3) For any additional records in January, you will leave the Monthly Budget field BLANK, and leave the Yearly Budget field BLANK.
(4) The calculated fields will look funny for the remaining records in January. Let’s assume the Expense for your next record is $120. Because of the ZERO budgets in that record, the Monthly Remaining Budget will show a calculated value of $ -120, and the Yearly Remaining Budget will show a calculated value of $ -120.
(5) Continue this way throughout the year. For ONLY THE FIRST record of each new month, enter your monthly budget. And you’re done done done with Annual Budget. You will only enter a value once for the entire year. But when you get to December, do make sure that the Annual Budget equals the sum of the twelve monthly budgets for the year.

VIEWING YEARLY AND MONTHLY REMAINING BUDGETS:
(1) You will see the desired summary calculations by filtering for date ranges of interest, and viewing with the Multi-column List View.
(2) Start with the yearly view. Create a saved search named “2017”. The Search Rules are [All] of the following are true: [Date] [is on or after] [1/1/2017] and [Date] [is on or before] [12/31/2017].
(3) View the 2017 saved search using Multi-column List View. Beneath all the rows of records you’ll see a row with the Summary Totals. You’ll see totals for all the individual expenses as well as monthly and annual budgets and remaining budgets. The total under Yearly Remaining Budget will correctly reflect your unspent income for the year. Also, let’s say you’re looking at four month’s worth of data at the end of April. You’ll see cumulative expenses through April, cumulative monthly budget through April, and cumulative remaining monthly budget through April.
(4) Create similar saved searches for individual months, using appropriate date ranges. When you view the search in Multi-column List View, you’ll see correct single-month totals for Expenses, Monthly Budget, and Monthly Remaining Budget. However, the summary totals for Yearly Budget and Yearly Remaining Budget will not be meaningful when looking at a data for a single month.

Hope that helps!

— Mike

[Tim Flick]

Mike thanks it works clever

[Brendan]

That is clever Mike!

The question I have is… what could I do in Tap Forms to make this sort of thing easier? But also flexible to handle any situation. One option of course would be to display totals for each section. So with the monthly section headings, it would be good to have a sub-total row displayed right above the next section heading. Just thinking out loud here :)

[Mike Schwartz]

Brendan,

Thanks for the compliment on my “inelegant” solution. Regarding your general musing about product enhancements …

(1) Your thought to display a sub-total row for each section in the Multi-column View would be an EXTREMELY useful enhancement. Using Tim’s Expenses form as an example, it would alleviate the need to create all the month-at-a-time saved searches in order to see the monthly expense totals and comparison to the monthly budget. Tim would still need my wacky calculation fields with their negative values, which only make sense when you look at the sub-totals. But this would be a great start.

(2) For simplicity and clarity, consider renaming “Single-column List View” and “Multi-column List View” as “Form View” and “Table View”. I think that most users would understand intuitively what those terms mean.

(3) Don’t cram too much canned functionality into the Table View, since it won’t be flexible. Add the sub-total row for each grouped section and declare victory.

(4) Longer term, add a “View as List” option to custom layouts. That is, the top section in the Layout Definition sidebar would include a checkbox called “View as List”. Each separate custom layout could have that option either checked or unchecked. If “View as List” is checked, then the Form View presents itself as one long scrollable list. It doesn’t matter how tall the layout is, it just scrolls from one record to the next. Just like scrolling through a multi-page Word document. The sidebar to the left of the layout, which contains the list of records, would act as a shortcut to scroll immediately to the selected record — similar to clicking on a sidebar thumbnail to jump-scroll to a particular document page in Preview.

Now if my custom form fills an entire page, then I probably wouldn’t check “View as List” and things would work same as they do today. But if it suits my purpose I could create a layout that’s an inch tall, include a few key fields NOT LIMITED to a single row, and maybe place a line object beneath them to serve as a visual separator. Then if I enable “View as List”, the screen would conveniently show multiple records at a time and scroll through all of them. Printouts would work the same way, listing multiple records per page and ensuring that it doesn’t chop a record in half at the bottom of the page.

(5) To extend the functionality, Tap Forms could allow the creation of summary layout sections that can be placed above and/or below the primary record layout. For example, a Section Heading could appear at the top of each new group, and a subtotal calculation below each group. You should be able to control the vertical size of the record and summary sections. You can drag multiple desired fields into the Summary Section, and for each one specify the summary value to be the usual Total, Avg, Min, or Max. I would also like to see “Count” included as another summary choice. The Summary Section(s) only appear when a “Group Records By” field is selected in the Form Definition.

(6) As a further extension, allow multiple nested summary layouts controlled by the First, Second, and Third Sort Fields.

(7) Now to revisit Tim’s Expense form and the wacky calculation fields mentioned in Item (1): I could envision that Tim creates a secondary Form (table) that lists his monthly budget for each month, and maybe another one that lists his annual budget for each year. Then if his Expense form contains a Summary Section and is grouped by Month, then the Summary Section would show his total expenses for the month, pull in that month’s corresponding budget from the ancillary table, and calculate the difference. I know this is a little half-baked.

(8) ==> All the capabilities described above should work exactly the same either viewing on-screen or in printed reports.

Is that enough? ;-)

Thanks,
Mike

[Brendan]

Hi Mike,

Hehe… you’ve pretty much described the advanced reporting engine I’ve been planning to write. :)

I want to build a system that lets you create a flexible tabular format that has rows who’s heights grow and shrink to accommodate the content. You should also be able to create a header and footer as well. And sub-sections too. I was contemplating using the label engine, with a header and footer that you can customize. Right now you can customize a label to make it fit the full width of the page and have it repeat the content down the page, similar to what you describe (but for printing only). You just can’t add a header or footer to it.

In Tap Forms 5.1 you’ll have the ability to reference linked fields in the formula editor. So you could have a parent form that contains the annual budget amount that is referenced in a calculation in the child form.

[Tim Flick]

I find myself leaing and then coming back to tap forms because of its simplicity

[Jan Wybe Oosterkamp]

Wow, I see you’re discussing the problem I am searching a solution for!

May I ask about the further developments?

Thanks!

[Brendan]

Hi Jan,

I had already added section totals to Tap Forms long ago.

Thanks,

Brendan

[Author]

Posts