Most Recent Record

Question

Every month we accumulate accounting data from our balance sheet into one application with only one table and no relationships. We only generate one record per month, and each record contains only a few fields of information plus four common accounting metrics (Current Ratio, Quick Ratio, etc.).&nbsp; From there we put the most recent metrics on tiles in the new dashboard (using the gas gauge report).&nbsp; How can I filter the report to only take the metrics from the most recent (highest Accounting Date) record?&nbsp;
I thought about something as simple as filtering the date created for the previous month, but that would not work because, for example, the July figures have not yet been entered, which is common since it takes a couple of weeks to close the books. If I used that method, it would try and fetch the record created from 7/31, which will be null since it does not yet exist.&nbsp; I need some way for QB to simply choose the record with the highest 'Accounting Date' value (not Date Created).&nbsp; In my little example, that would be the 6/30/21 record. &nbsp;This should be easy, but I've drawn a blank...------------------------------Keith Hendrick------------------------------

markshnier__you · Answer

No problem.Make a new Table called Max Accounting Mth &nbsp;with no fields. &nbsp;Enter one record. That Record will be Record ID# of 1.Make a new field in your Balance Sheet Data Table called [Link to Max Accounting Mth] as a formula numeric with a formula of 1.make a relationship between Max Accounting Mth and the balance sheet data based in the reference field of the Link to Max Accounting Mth] field .Make a Summary maximum of the highest Accounting date field. &nbsp;Then look that up down to Balance sheet.Make a formula checkbox field to identify the Accounting month where the Max Accounting Month equals the Accounting month.Now, finally filter the gauge report to only include the flagged record.------------------------------Mark Shnier (YQC)Quick Base Solution ProviderYour Quick Base Coachhttp://QuickBaseCoach.commark.shnier@gmail.com------------------------------

keithhendrick · Answer

Worked perfectly, Mark.&nbsp; Thanks!&nbsp; Follow-up question, not that I have the active (most recent) accounting month, I'd like to display it on a tile on the new dashboard.&nbsp; So, how can I take that date value and convert it to a formatted numeric such that it will display on the gas gauge report and reflect the date, or is that possible?------------------------------Keith Hendrick------------------------------

markshnier__you · Answer

Are you asking if the Gas Gauge report itself can somehow include a text field (ie the date) , then no it cannot.

But I think that you are asking how you can use a different tile to show the date words for which is the Most Recent Month. The only way I can think of right now, would be to have a small narrow report of the Active month. I tried it and I can get that to work with using up 4 "squares" of the dashboard.

------------------------------
Mark Shnier (YQC)
Quick Base Solution Provider
Your Quick Base Coach
http://QuickBaseCoach.com
mark.shnier@gmail.com
------------------------------

keithhendrick · Answer

Unfortunately, I don't have the screen real estate for a 4-tile report.&nbsp; I wish this new dashboard was a big longer, but I suppose that's where the tabs com in.&nbsp; Anyway, it's okay since this was a 'nice to have' feature anyway.&nbsp; The bigger issue was the auto selection of the most recent accounting date and you solved that.&nbsp; Thanks again.------------------------------Keith Hendrick------------------------------

markshnier__you · Answer

Yes, unlike an Application Home Page, the Dashboards are pretty much only as big as the screen with no vertical scrolling, so it sal about the Tabs for more space,------------------------------Mark Shnier (YQC)Quick Base Solution ProviderYour Quick Base Coachhttp://QuickBaseCoach.commark.shnier@gmail.com------------------------------

Forum Discussion

Most Recent Record