Created A,B and C gruop of 10 records
Hello all
I need help
does anybody know a formula for creating a group of 10 records and labeling those records as A, B etch based on levels 1, 2, 3, etc?
Level 1, max 10 records = "A"
...
Forum Discussion
MarkShnier__You
Qrew Legend
Qrew LegendThis can be done using a formula query. The FQ will rank the records based on Recird ID within each set of Program and Level. Then use that ranking which will be sequential from 1 to whatever, to assign a group using either a long IF statement or pulling in a group ABC code off a relationship.
I can get back to you tomorrow with the formula for the Formula Query to Rank unless someone gets to you first.
------------------------------
Mark Shnier (Your Quickbase Coach)
mark.shnier@gmail.com
------------------------------
Hi Mark,
I knew that you would have some guidance for it. Nevertheless, looking forward to seeing the formulas.
thank you for your help on this.
------------------------------
Data System Analyst (Administrator)
------------------------------
- MarkShnier__You
Here is an example
var text QUERY = "{7.EX.'" & [Program] & "'}" // change the 7 to the fid for program& " AND " &"{8.EX.'" & [Level] & "'}" // change 8 to the fid of the Level.& " AND " && "{3.LTE.'" & [Record ID#] & "'}";var number Ranking =Size(GetRecords($QUERY));If($Ranking <=10, "A",$Ranking <=20, "B",$Ranking <=30, "C",....etc$Ranking <=260, "Z")
------------------------------
Mark Shnier (Your Quickbase Coach)
mark.shnier@gmail.com
------------------------------HI Mike.this is awsome
I made all modifications but placed all records with the letter "A" the max of records per letter needs to be 10 max
var text QUERY = "{344.CT.'" & "ESL" & "'}" &
"{362.EX.'" & [Testing ESL Level] & "'}" &
"{3.LTE.'" & [Placement ID] & "'}";
var number Ranking =Size(GetRecords($QUERY));
If(
$Ranking <=10, "A",
$Ranking <=20, "B",
$Ranking <=30, "C", "Z"
)
------------------------------
Data System Analyst (Administrator)
------------------------------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
------------------------------
