Formula - Most Recent Date

Question

Good Afternoon All,
Hope everybody is having a great Wednesday.&nbsp; Wondering if anyone has some insight on a problem I'm facing: I currently have a table that auto-imports fuel prices once or twice a week.&nbsp; I would like to create a formula that identifies the couple hundred records that were imported with the most recent date, so that I can create a bar chart of the most recent pricing.
Has anyone ever written a formula that can identify records with the most recent date?------------------------------Aidon Olligschlager |Flight Operations Technical SupportPentastar AviationWaterford MIaolligschlager@pentastaraviation.com | 248-202-9587------------------------------

chayceduncan · Answer

My suggestion would be to create a 'Master' table that you can essentially make act as a parent to all of your records. The setup looks like:&nbsp;
Create new table called Master, no fields needed
Create a single record in that table to create Record ID# 1
Create a relationship between your Master table and prices with Master as the parent table, and allowing QB to make the 'Related Master' field in your prices table. In that field - change the type to formula-numeric and set the value in that formula to the value of 1. No logic, just the number 1.&nbsp;
What this does is ensure that EVERY record in your prices table gets auto related to that single Master record.&nbsp;

In that relationship you can then summarize the max import date or whatever date you're targeting. You can then do a reverse lookup back down to prices, and then filter your report where Price Date = Max Date (looked up from your Master table)------------------------------Chayce Duncan------------------------------

aidonolligschla · Answer

Chayce,
I tried this over the weekend and it worked great!&nbsp; Thanks so much for the help.&nbsp; Also created a formula checkbox that checks true when the record's date equals the Max Date.&nbsp; Works great for using as a filter in reports.
Thank you!!!------------------------------Aidon Olligschlager |Flight Operations Technical SupportPentastar AviationWaterford MIaolligschlager@pentastaraviation.com | 248-202-9587------------------------------

Forum Discussion

Formula - Most Recent Date