Forum Discussion

AidonOlligschla's avatar
AidonOlligschla
Qrew Cadet
7 months ago

Formula - Most Recent Date

Good Afternoon All,

Hope everybody is having a great Wednesday.  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.  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 Support
Pentastar Aviation
Waterford MI
aolligschlager@pentastaraviation.com | 248-202-9587
------------------------------

2 Replies

  • 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: 

    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. 

    What this does is ensure that EVERY record in your prices table gets auto related to that single Master record. 

    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's avatar
      AidonOlligschla
      Qrew Cadet

      Chayce,

      I tried this over the weekend and it worked great!  Thanks so much for the help.  Also created a formula checkbox that checks true when the record's date equals the Max Date.  Works great for using as a filter in reports.

      Thank you!!!



      ------------------------------
      Aidon Olligschlager |
      Flight Operations Technical Support
      Pentastar Aviation
      Waterford MI
      aolligschlager@pentastaraviation.com | 248-202-9587
      ------------------------------