Forum Discussion

ArielSantiago's avatar
ArielSantiago
Qrew Member
11 months ago

Formula Query Limit?

Hi,

I'm just wondering what is the limit for using formula queries in a report? I have a table with 88,000 rows of data. I also have a formula query to find the first instance of a duplicate in the data set. When I try to import the table data to another table I get the, "The formula query execution took too long."

Is there a number data limit for something like this? If so, is there a workaround?



------------------------------
Ariel Combs
------------------------------

2 Replies

Sort By
  • MarkShnier__You's avatar
    MarkShnier__You
    Qrew Champion
    11 months ago

    So if you do the math, in order to check the first record to see it is a duplicate, the system needs to look at he other 88,000 records.  So to scan 88,000 record for duplicates it needs to look at 88,000 * 88,000 = a very big number 7.7 Billion. 

    Alas, I had the same problem though with a smaller record count.  I think mine was about 30,000 records.

    So I created a formula to get the last digit of the Record ID, so that would be a number from 0 to 9.  The I created 9 more copies of my Saved Table to Table Import each one filtered to just import 1/10th, so like batch 0, batch 1 etc ....  of the data.  Then created the URL formula button to run  the 10 imports.  Individually they will take 1/10 of the amount of time each and in my case it worked.  

    This is part of my code

    var text UpdateMembershipWithEMRIDIFMembershipEMRIsBlankANDMatchesOnLastFirstInitialDOBBatchOne = URLRoot() & "db/" 
    & [_DBID_MEMBERSHIP_TRACKING_1] & "?act=API_RunImport&ID=16";
     
    var text UpdateMembershipWithEMRIDIFMembershipEMRIsBlankANDMatchesOnLastFirstInitialDOBBatchTwo = URLRoot() & "db/" 
    & [_DBID_MEMBERSHIP_TRACKING_1] & "?act=API_RunImport&ID=20";
     
    var text UpdateMembershipWithEMRIDIFMembershipEMRIsBlankANDMatchesOnLastFirstInitialDOBBatchThree = URLRoot() & "db/" 
    & [_DBID_MEMBERSHIP_TRACKING_1] & "?act=API_RunImport&ID=21";
     
    var text UpdateMembershipWithEMRIDIFMembershipEMRIsBlankANDMatchesOnLastFirstInitialDOBBatchFour = URLRoot() & "db/" 
    & [_DBID_MEMBERSHIP_TRACKING_1] & "?act=API_RunImport&ID=22";
     
    var text UpdateMembershipWithEMRIDIFMembershipEMRIsBlankANDMatchesOnLastFirstInitialDOBBatchFive = URLRoot() & "db/" 
    & [_DBID_MEMBERSHIP_TRACKING_1] & "?act=API_RunImport&ID=23";
     
    var text UpdateMembershipWithEMRIDIFMembershipEMRIsBlankANDMatchesOnLastFirstInitialDOBBatchSix = URLRoot() & "db/" 
    & [_DBID_MEMBERSHIP_TRACKING_1] & "?act=API_RunImport&ID=24";
     
    var text UpdateMembershipWithEMRIDIFMembershipEMRIsBlankANDMatchesOnLastFirstInitialDOBBatchSeven = URLRoot() & "db/" 
    & [_DBID_MEMBERSHIP_TRACKING_1] & "?act=API_RunImport&ID=25";
     
    var text UpdateMembershipWithEMRIDIFMembershipEMRIsBlankANDMatchesOnLastFirstInitialDOBBatchEight = URLRoot() & "db/" 
    & [_DBID_MEMBERSHIP_TRACKING_1] & "?act=API_RunImport&ID=26";
     
    var text UpdatePartOneOrPartTwo = URLRoot() & "db/" & Dbid() & "?act=API_EditRecord&rid=1"
    & "&_fid_153=" & URLEncode("Part 1 of 2");
     
    var text Refresh = URLRoot() & "db/" & Dbid() & "?a=dr&rid=1";
     
    $UpdateLastSeenBasedOnMatchingLastFirstDOB 
     
    & "&rdr=" & URLEncode($UpdateNextApptBasedOnMatchingLastFirstDOB)
     
    & URLEncode("&rdr=" & URLEncode($UpdateMembershipWithEMRIDIFMembershipEMRIsBlank))
     
    & URLEncode(URLEncode("&rdr=" & URLEncode($UpdateMembershipWithEMRIDIFMembershipEMRIsBlankANDMatchesOnLastFirstInitialDOBBatchOne)))
     
    & URLEncode(URLEncode(URLEncode("&rdr=" & URLEncode($UpdateMembershipWithEMRIDIFMembershipEMRIsBlankANDMatchesOnLastFirstInitialDOBBatchTwo))))
     
    & URLEncode(URLEncode(URLEncode(URLEncode("&rdr=" & URLEncode($UpdateMembershipWithEMRIDIFMembershipEMRIsBlankANDMatchesOnLastFirstInitialDOBBatchThree)))))
     
    & URLEncode(URLEncode(URLEncode(URLEncode(URLEncode("&rdr=" & URLEncode($UpdateMembershipWithEMRIDIFMembershipEMRIsBlankANDMatchesOnLastFirstInitialDOBBatchFour))))))
     
    & URLEncode(URLEncode(URLEncode(URLEncode(URLEncode(URLEncode("&rdr=" & URLEncode($UpdateMembershipWithEMRIDIFMembershipEMRIsBlankANDMatchesOnLastFirstInitialDOBBatchFive)))))))
     
    & URLEncode(URLEncode(URLEncode(URLEncode(URLEncode(URLEncode(URLEncode("&rdr=" & URLEncode($UpdateMembershipWithEMRIDIFMembershipEMRIsBlankANDMatchesOnLastFirstInitialDOBBatchSix))))))))
     
    & URLEncode(URLEncode(URLEncode(URLEncode(URLEncode(URLEncode(URLEncode(URLEncode("&rdr=" & URLEncode($UpdateMembershipWithEMRIDIFMembershipEMRIsBlankANDMatchesOnLastFirstInitialDOBBatchSeven)))))))))
     
    & URLEncode(URLEncode(URLEncode(URLEncode(URLEncode(URLEncode(URLEncode(URLEncode(URLEncode("&rdr=" & URLEncode($UpdateMembershipWithEMRIDIFMembershipEMRIsBlankANDMatchesOnLastFirstInitialDOBBatchEight))))))))))
     
    & URLEncode(URLEncode(URLEncode(URLEncode(URLEncode(URLEncode(URLEncode(URLEncode(URLEncode(URLEncode("&rdr=" & URLEncode($Refresh)))))))))))
     
    // here is the generic syntax to next 11 URL calls such as run the 10 imports and refresh the page
    $URLONE 
    & "&rdr=" & URLEncode($URLTwo)
    & URLEncode("&rdr=" & URLEncode($URLThree))
    & URLEncode(URLEncode("&rdr=" & URLEncode($URLFour)))
    & URLEncode(URLEncode(URLEncode("&rdr=" & URLEncode($URLFive))))
    & URLEncode(URLEncode(URLEncode(URLEncode("&rdr=" & URLEncode($URLSix)))))
    & URLEncode(URLEncode(URLEncode(URLEncode(URLEncode("&rdr=" & URLEncode($URLSeven))))))
    & URLENcode(URLEncode(URLEncode(URLEncode(URLEncode(URLEncode("&rdr=" & URLEncode($URLEight)))))))
    & URLENcode(URLENcode(URLEncode(URLEncode(URLEncode(URLEncode(URLEncode("&rdr=" & URLEncode($URLNine))))))))
    & URLENcode(URLENcode(URLENcode(URLEncode(URLEncode(URLEncode(URLEncode(URLEncode("&rdr=" & URLEncode($URLTen)))))))))
    & URLENcode(URLENcode(URLENcode(URLENcode(URLEncode(URLEncode(URLEncode(URLEncode(URLEncode("&rdr=" & URLEncode($URLEleven))))))))))



    ------------------------------
    Mark Shnier (Your Quickbase Coach)
    mark.shnier@gmail.com
    ------------------------------

  • AustinK's avatar
    AustinK
    Qrew Commander
    11 months ago

    What I have noticed with summarized data in Quickbase is it will never warn you when it isn't able to summarize it all. It will present inaccurate data to you instead and you won't know unless you are auditing it.

    We had issues summarizing numbers and it wasn't the length of the numbers it was the amount of data combed over I think. It was for commissions for an entire month and what we noticed is when we filter for specific locations we get accurate data but when doing the calculations for all locations at once it skips data in random places and the numbers do not add up.

    I anticipate this to also be an issue with formula queries but I have not seen it in action yet because I have not had to grab and summarize that amount of data with them. It is also entirely possible because formula queries leap frogs the typical relationship it may not be subject to this same issue. It may depend how many levels of related data there are as well. Just figured I'd throw this out there though in case others have noticed issues like this.