Forum Discussion
Just a suggestion looking at your query - formula queries are both very powerful but finicky in their performance if implemented less than optimal. In your first query parameter you're doing a contains statement - if possible you should try and swap that to be EX so its more efficient for QB than doing a string contains query. If you're not able to - I would refactor the order of your query so that the CT is the last thing you're doing. The idea being you want to minimize the work QB has to do so something like:
"{362.EX.'" & [Testing ESL Level] & "'}" & "AND"
"{3.LTE.'" & [Placement ID] & "'}" & "AND" &
"{344.CT.'" & "ESL" & "'}";
------------------------------
Chayce Duncan
------------------------------
Hello. Thank you for the suggestion; I haven't been able to find the solutions, even with your help. I will keep digging to find a solution. Thank you both.
------------------------------
Data System Analyst (Administrator)
------------------------------
- MarkShnier__You4 months agoQrew Champion
Sorry, I assumed from your last response that we had this solved. Can you show (copy paste) your current code and explain what the problem is now?
------------------------------
Mark Shnier (Your Quickbase Coach)
mark.shnier@gmail.com
------------------------------ - Data_SystemAnal4 months agoQrew Cadet
Hi Mark, no worries,
var text QUERY =
"{362.EX.'" & [Testing ESL Level] & "'}" & "AND"&
"{3.LTE.'" & [Placement ID] & "'}" & "AND"&
"{344.CT.'" & "ESL" & "'}" ;
var number Ranking =Size(GetRecords($QUERY));
If(
$Ranking <=1000, "A",
$Ranking <=1020, "B",
$Ranking <=1030, "C",
$Ranking <=1040, "D",
$Ranking <=1050, "E",
$Ranking <=1060, "F",
$Ranking <=1070, "G",
$Ranking <=1080, "H",
$Ranking <=1090, "I",
$Ranking <=1000, "J",
$Ranking <=2010, "J",
$Ranking <=2020, "L",
$Ranking <=2030, "M",
$Ranking <=2040, "N",
$Ranking <=2050, "O",
$Ranking <=2060, "P",
$Ranking <=2070, "Q",
$Ranking <=2080, "R",
$Ranking <=2090, "S",
$Ranking <=19000, "T",
"Z"
)The placement record ID is nos 10, 11, 12, 14..., and so on; when I re-write (trying out) 1000 and 1020...., I got 3 groups with different amounts of records for each group. The idea is that each group has a max of 10 records.
I hope this makes sense.
Thank you again.
------------------------------
Data System Analyst (Administrator)
------------------------------ - MarkShnier__You4 months agoQrew Champion
Puzzling.
This is probably not the problem, but I always include a space before and after the AND
var text QUERY =
"{362.EX.'" & [Testing ESL Level] & "'}" & " AND "&
"{3.LTE.'" & [Placement ID] & "'}" & " AND "&
"{344.CT.'" & "ESL" & "'}" ;Are you sure when you found extra records in each group that they were within the same ESL Level? And also in the example above, obviously there will be many A's (I guess 1000 of them) because you started at 1000 and also the last group jumps up to 19,000. I suppose you have ~ 20,000 records?
Also I assume that you renamed your [Record ID#] field to be called [Placement ID]
------------------------------
Mark Shnier (Your Quickbase Coach)
mark.shnier@gmail.com
------------------------------ - ChayceDuncan4 months agoQrew Captain
When it's all said and done - your syntax all looks functionally ok from below so at face value it's not a syntax issue.
var text QUERY =
"{362.EX.'" & [Testing ESL Level] & "'}" & "AND"&
"{3.LTE.'" & [Placement ID] & "'}" & "AND"&
"{344.CT.'" & "ESL" & "'}" ;If you're not getting a value at all or not getting a value you're expecting - then it likely has something to do with how you're forming the query. I would suggest picking an arbitrary record and trying to test the 'Ranking' query syntax your testing in a report. So pick a record that you know the general ranking it should be potentially - and then make a temporary report where you set the report filters to be the same as your query and plug in the values for Testing ESL Level and Placement ID against Field 362 and Record ID# and see if the report spits out the # of records you're trying to match. If the number of records is 1007 then you know it's working as intended. If it returns 0 then you know it's not. Then you can modify the report filters to get the results you want to make whatever updates might be needed in the query itself.
------------------------------
Chayce Duncan
------------------------------ - Data_SystemAnal4 months agoQrew Cadet
Hi Mark
yes, ten records per group if they are on the same level
the ranking part of the formula was just a test if I place back your original ranking distribution,
yes placement ID original was {Record ID#].
Fields 344 and 362 are formula fields that combine lookup view field types. So, I duplicated both fields to pretend there were data user input fields, so when I made those changes, your formula performed flawlessly ( 10 records per group and same level). Did you know if formula query cannot be used with formulas that use a lookup view field? Or did you know how to use lookup view fields in formula queries?
------------------------------
Data System Analyst (Administrator)
------------------------------ - Data_SystemAnal4 months agoQrew Cadet
Thank you for your follow-up. I find that part of the issue is fields used on the query are lookup field formulas.
------------------------------
Data System Analyst (Administrator)
------------------------------ - MarkShnier__You4 months agoQrew Champion
I'm not aware of any limitations like that with using lookup fields in an FQ. I suggest a support ticket and post back if you have found a bug in FQs.
------------------------------
Mark Shnier (Your Quickbase Coach)
mark.shnier@gmail.com
------------------------------