expenses report with lines as catergories totals

Tap Forms – Organizer Database App for Mac, iPhone, and iPad Forums Using Tap Forms expenses report with lines as catergories totals

Viewing 13 reply threads
  • Author
    Posts
  • December 13, 2018 at 1:57 PM #32646

    Paul Lynch
    Participant

    HI

    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

    Brendan
    Keymaster

    Hello 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 to less 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 Lynch
    Participant

    Thanks 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

    Brendan
    Keymaster

    Hi 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 the Print layout option. You might want to switch to horizontal page orientation to fit the columns on the page. Make sure you select the Print 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 Lynch
    Participant

    “…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

    Brendan
    Keymaster

    Did 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 Lynch
    Participant

    Yup 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

    Brendan
    Keymaster

    Ya, 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 Lynch
    Participant

    Using 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

    Brendan
    Keymaster

    The 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 Lynch
    Participant

    Subtotals 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….$43

    Or 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

    Brendan
    Keymaster

    The 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 Lynch
    Participant

    Hi 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

    Brendan
    Keymaster

    Hi 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.

Viewing 13 reply threads

You must be logged in to reply to this topic.