Discussions

 View Only
  • 1.  Formula Syntax Error Question

    Posted 11-17-2021 17:20
    Hi All,

    Here's the formula I thought would work:

    todate(max(getfieldvalues(getrecords("{756.EX. '"&[Record ID#]&"'}", "abdc3n4ne"),1)))

    The logic is, find all records where fid 756 (related field) in table abdc3n4ne equals the record ID in the table where this formula is, then of those records tell me the values in the Date Created field, then just tell me the most recent of those dates (what I thought the max formula would do) then turn it into just the date.

    I'm getting a syntax error. The second ) after the 1 at the end is highlighted yellow and the error box says "Expecting," but that's for the max function, which I didn't think needed a comma.

    Thoughts?

    ------------------------------
    Daniel Johnson
    ------------------------------


  • 2.  RE: Formula Syntax Error Question

    Posted 11-17-2021 17:38
    There is not currently a way to process the returned records to get the max value.  Maybe in the future, but Formula Queries do not support Max at this time.

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



  • 3.  RE: Formula Syntax Error Question

    Posted 11-18-2021 11:04
    Rats!

    So, my next thought was to change the formula field type to text and then use another field to parse out the data I want. The formula was:

    totext(getfieldvalues(getrecords("{756.EX. '"&[Record ID#]&"'}", "abdc3n4ne"),1))

    But when I clicked save I got the following error:

    Uh-Oh! The way this formula is built will take too long to process. Please try refining your formula.

    Not sure what to do with that.

    ------------------------------
    Daniel Johnson
    ------------------------------



  • 4.  RE: Formula Syntax Error Question

    Posted 11-18-2021 12:02
    How large is your table, how many records?

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



  • 5.  RE: Formula Syntax Error Question

    Posted 11-18-2021 12:36
    The table the formula is querying has 42,000+ records.

    ------------------------------
    Daniel Johnson
    ------------------------------



  • 6.  RE: Formula Syntax Error Question

    Posted 11-18-2021 12:38
    You would probably have to reach out to the care team but I think right now they are throttling the use of queries on larger table sizes. 42,000 records is not really that big for Quickbase but they seem to be going very cautiously on these formulas. They are afraid the Formula Queries will hurt the performance of the app and the platform.

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



  • 7.  RE: Formula Syntax Error Question

    Posted 11-18-2021 15:22

    I may be partly to blame for the throttling.  I opened a support case because all of my apps in the realm went offline when I tried to save a formula query.  a couple days after they closed the ticket is when I saw that message for the first time.  

    It's interesting that they can present something in one of the empower workshops this year and show a query working on a data set of over 300k records, but cannot deliver that performance in production.



    ------------------------------
    Paul Peterson
    ------------------------------



  • 8.  RE: Formula Syntax Error Question

    Posted 11-18-2021 15:16
    I ran into a similar issue.  Fortunately I already had a relationship between the two tables and the table where I needed the data was the parent table of the relationship.  I created a max summary field in the relationship that returned the value I needed and used "Only summarize records where the following are true to handle the conditions of the query.

    ------------------------------
    Paul Peterson
    ------------------------------