Discussions

 View Only
  • 1.  Min/Max Dates with Formula Queries

    Posted 11-21-2021 00:16
    I need to use a formula query to find the minimum and maximum date/time for a group of records (Flight Arrival Date/Time) in the same table. I know that there is currently no ability to find the min/max inside the formula query, but I'm trying to figure a way around that.

    I can get the query formula field to display "09-24-2021 01:45pm; 09-24-2021 4:00pm; 09-24-2021 12:24pm..." and so on.

    At the end of the day, I want a date/time field that will show me "09-24-2021 12:24pm" as the minimum (earliest) of the three date/times listed above.

    If I use this formula, it gives me a single date/time, but it's not the earliest one.
    Min(
    Part(ToText([FormulaQuery]),1,";"),
    Part(ToText([FormulaQuery]),2,";"),
    Part(ToText([FormulaQuery]),3,";"),
    Part(ToText([FormulaQuery]),4,";"))

    I can get it to work if I use only the time portion, as that can be converted to a numeric decimal, but that won't work for my purposes since a flight may come in at a later time but an earlier date.

    Doesn't seem I can take a textlist field, "Part" it, convert it to a date/time, and find the min/max.

    Am I missing anything?

    ------------------------------
    Michael Frishman
    ------------------------------


  • 2.  RE: Min/Max Dates with Formula Queries

    Posted 11-21-2021 11:23

    Try converting your text result to a date. 

    Min(
    ToDate(Part(ToText([FormulaQuery]),1,";")),
    ToDate(Part(ToText([FormulaQuery]),2,";")),
    ToDate(Part(ToText([FormulaQuery]),3,";")),
    ToDate(Part(ToText([FormulaQuery]),4,";")))



    ------------------------------
    Mark Shnier (YQC)
    mark.shnier@gmail.com
    ------------------------------



  • 3.  RE: Min/Max Dates with Formula Queries

    Posted 11-21-2021 19:00
    Thanks for the help, but that's not working. If I keep the field as a Date/Time, then I get "Expecting datetime but found date". But if I change the field to a Date field, I lose the flight arrival time which I need to get the Min.

    ------------------------------
    Michael Frishman
    ------------------------------



  • 4.  RE: Min/Max Dates with Formula Queries

    Posted 11-21-2021 22:53
    Ok, right.  You need to convert to a date time field

    https://login.quickbase.com/db/6ewwzuuj?a=dr&rid=143&rl=gyq

    The issue is that before you can use the ToTimeStamp function, you need  it parse out the date from the time in each Part. 

    Post back if you need help with thay syntax.

    ------------------------------
    Mark Shnier (YQC)
    mark.shnier@gmail.com
    ------------------------------