0

Modify subtable field contents in report designer

I am creating a report to print the subtable contents of a table. Unlike report text fields, no gear icon seems to pop up allowing me to enter scripting code to modify which rows appear when I click on the subtable field in the report designer. 

These subtables list financial transactions and i want to be able to design a report that includes only "last year" and "current year" transactions. Is this possible?

This seems like a typical scenario in financial reporting but I just don't see how to do it. Does anyone have a hint for me? 

Thanks so much!

9 replies

null
    • Viggo
    • 4 yrs ago
    • Reported - view

    Yes, you can - if I got your question right. Just click on the field - yupp, one more time - and it shows up :) Should look somthing like this:

    • Watch These Kids Bloom, Inc.
    • Jen
    • 4 yrs ago
    • Reported - view

    Thanks Viggo. This isn't working for me. I can click as many times as I like and the field just turns blue. That's all. There are no square handles like in your image. There is no gear icon. Now if I go directly into the subtable itself and click the printer icon I do have the ability to get the gear icon on any field of that table. But if I am in the parent table and click the printer icon and want to print the subtable as a part of the report, no go. 

    - Jen

    • Watch These Kids Bloom, Inc.
    • Jen
    • 4 yrs ago
    • Reported - view

    This is what it looks like:

    subtable in report problem

    Thanks for any help!

    • Mconneen
    • 4 yrs ago
    • Reported - view

    @Jen... Fortunately .. or unfortunately .. I do not do much with the printing features... That said.. When I do.. I generally create fields on the fiew from which I generate the first print layout.. and then add other hidden fields.. as opposed to using the "formula" from within the designer.. Mainly because I guess I am a bit too lazy to learn the ins / outs of the Report Designer. 

    What @Viggo was showing was a formula field within the designer. 

    reportDesigner

    • Watch These Kids Bloom, Inc.
    • Jen
    • 4 yrs ago
    • Reported - view

    @Mconneen, thanks for your input. Yes the formua field on the report designer is super for things like making a total of all the transactions in my subform's rows. Love that! Then I also created a "grand total"  by using another formula field. Yay! 

    I'm still struggling with how to filter the rows shown to limit them to date ranges. I hope someone can help with that.

    - Jen

    • Nick
    • 4 yrs ago
    • Reported - view

    Hi Jen,

     

    click 'Edit Columns' in the report designer and then apply your filters in the 'From' and 'To' date.

    • Watch These Kids Bloom, Inc.
    • Jen
    • 4 yrs ago
    • Reported - view

    @Nick - Good to know! It's a relief to realize I can filter the rows via the date field.

    That said, I would prefer to use a formula to limit rows to, say current year or prior year dates instead of hard coding the date range. That way I would not have to change the report design once it's complete. I could have a report named "Prior Year Transactions", for example, and just run it as desired. I just don't see any place where a formula like that could be inserted, however, and your solution may be the best available. Thanks for pointing it out!

    - Jen

    • Watch These Kids Bloom, Inc.
    • Jen
    • 4 yrs ago
    • Reported - view

    I learned the answer to this question today from Ninox supprt and here it is for anyone who needs it: When you click "edit columns" in the report designer, select the date column and then instead of entering a "from" and "to" date, click on "not empty" and use the formula field.

    To limit the date to dates in the current year, for example, here's your code: 

    if year(Date) = year(today()) then
    Date
    end


    Could not be simpler! Now my report shows only the current year transactions - permanently. I created another report for prior year transactions.  My users don't have to fuss about date ranges and the reports look perfect.

    - Jen

    • Nick
    • 4 yrs ago
    • Reported - view

    Thanks for sharing Jen!

Content aside

  • 4 yrs agoLast active
  • 9Replies
  • 2230Views