Tap Forms – Organizer Database App for Mac, iPhone, and iPad › Forums › Using Tap Forms › expenses report with lines as catergories totals
Tagged: expenses report
- This topic has 13 replies, 2 voices, and was last updated 5 years, 3 months ago by Brendan.
-
AuthorPosts
-
December 13, 2018 at 1:57 PM #32646
Paul LynchParticipantHI
Using TF 5.3.4, Mac high Sierra.
I have an expenses form with fields:
date, $amount, category (rent, food, etc.)Is there a way to generate a report where I enter a date range and the report shows a list of all categories along with each of their respective total $amounts only for the date range entered?
Where can I find how to do this please?
Thanks!!
December 13, 2018 at 6:02 PM #32651
BrendanKeymasterHello Paul,
Yes, you can do this by creating a Saved Search. For a date range you’ll need to use the same field twice in your form, but one field with the comparison operator set to
greater than or equal to
and the other field’s comparison operator set toless than or equal to
. Although that depends on whether or not you want the dates to be inclusive.Click the Filter button just to the left of the Search field and you’ll be able to customize your searches. On the iOS version the filter button is at the top of the records list screen. You can add a search there and then specify the search rules.
December 13, 2018 at 6:40 PM #32653
Paul LynchParticipantThanks Brendan.
How do I format the form so it creates a report
where each horizontal line summarizes one category’s total?PS Love this product.
December 13, 2018 at 9:37 PM #32654
BrendanKeymasterHi Paul,
First switch to the multi-column list view. Then enable the Show Group Summaries option from that little menu at the top-right of the records list. It looks like a tiny pull down menu. You can show and hide section headings, group summaries, etc.
You should see sub-totals for each category and grand totals for each field. If you don’t see the totals, click the Sigma (∑) button to display the calculation totals row.
Now use the Print command in the File menu and make sure you have
Table of records
selected for thePrint layout
option. You might want to switch to horizontal page orientation to fit the columns on the page. Make sure you select thePrint all records
option too.Now go to the Fields tab and then enable or disable the fields you want or don’t want to print. Double-click on the Width value for each field and type in a new number to adjust the widths of the columns when printing.
Hope that gets you close to what you want.
Thanks!
Brendan
December 14, 2018 at 12:45 PM #32667
Paul LynchParticipant“…You should see sub-totals for each category and grand totals for each field. If you don’t see the totals, click the Sigma (∑) button to display the calculation totals row/…’
When I do that, I don’t see sub-totals for each category. I see the attached.
I need one line per category.
What am i doing wrong?ps first sort field is ‘category’
THNx!!
Attachments:
You must be logged in to view attached files.December 14, 2018 at 1:04 PM #32670
BrendanKeymasterDid you also enable the Show Group Summaries function? That’s available from the little menu button at the top-right of the multi-column list view.
see attachments.
Attachments:
You must be logged in to view attached files.December 14, 2018 at 2:08 PM #32673
Paul LynchParticipantYup Group Summaries is on.
(Turning it off shows all records and no subtotals).And clicking Sigma shows only one grand total, no subtotals.
Print layout Table is chosen
I cant figure it out.thnx
December 14, 2018 at 5:05 PM #32674
BrendanKeymasterYa, I’m not sure why it’s not working for you.
So you see the subtotals on the multi-column list view but not on the printout?
I just printed my sample and it does have the sub-totals.
See attached.
Attachments:
You must be logged in to view attached files.December 14, 2018 at 6:14 PM #32677
Paul LynchParticipantUsing TF 5.3.4, Mac high Sierra.
weird…. (with the ‘Show Groups Summaries’ and Sigma always on)
sometimes the subtotals show, sometimes not…
both on the form and on print.
Tried hitting the refresh button, no change.
Thnx again.
December 14, 2018 at 6:34 PM #32678
BrendanKeymasterThe Show Group Summaries switch can be enabled or disabled on the form and any saved searches you may have. So maybe you’re switching between searches and the main form. Maybe that explains why sometimes it shows and sometimes it doesn’t?
December 15, 2018 at 1:21 AM #32679
Paul LynchParticipantSubtotals now show properly.
U were right it was the saved searches! Thnks.OK two more questions and I’ll be out of your hair. :)
Please see attached first….
1. How can I make it so there’s only one horizontal line per category and subtotal, like this…
FOOD………..$7
RENT……….$49
etc…and
2. RENT and Road Trips are both in the same group summary because both start with the letter ‘R’. Is there a way for RENT and Road Trip to each have their own separate lines and subtotals so it will look like this…?
FOOD………..$7
RENT……….$49
Road Trips….$43Or let me know where in the user’s manual I can find this info. I could not find it.
Thnx again Brendan.
Attachments:
You must be logged in to view attached files.December 15, 2018 at 2:38 AM #32681
BrendanKeymasterThe report lists all the records. It doesn’t group them together such that there’s only a summary line for every group of records. You’ll always get all the detail records as well as the summaries.
If you set the Group Records By setting on your form (and Saved Searches) to your Cat field, then you’ll get separate sections for each Cat value. So Rent and Road Trips will appear in separate groups.
December 16, 2018 at 3:02 PM #32723
Paul LynchParticipantHi Brendan.. I understand that the report lists all the records and doesn’t group them together into a summary line for each group of records.
Is there any way you can think of to export (or by any other means) the content so I get just the category summary line for each group and total (FOOD………..$7. RENT……….$49) to bring into Apple Numbers.
(Tap Forms is better for data entry. Apple Numbers can create the report of category summary line totals.
Thanks again!
December 16, 2018 at 10:22 PM #32731
BrendanKeymasterHi Paul,
Tap Forms will only export the detailed records, so you’d have to get Numbers to generate the sub-totals by category. I’m not a Numbers or Excel guru, but it may be possible.
-
AuthorPosts
You must be logged in to reply to this topic.