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
-
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:
-
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
-
This is what it looks like:
Thanks for any help!
-
@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.
-
@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 -
Hi Jen,
click 'Edit Columns' in the report designer and then apply your filters in the 'From' and 'To' date.
-
@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 -
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
-
Thanks for sharing Jen!
Content aside
- 5 yrs agoLast active
- 9Replies
- 2236Views