Forum Discussion

Qrew Cadet

9 months ago

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" ...

Data_SystemAnal

Qrew Cadet

Hi Mark (Happy Holidays)

Yes. For example, I said that I have 100 records.

I need to create groups of 10 records, so let's. I am saying that I have 50 records set at level one (1) and 50 defined at level two (2), so the formula I am looking to create will set a letter for each group.

GED-Level 1-A; GED-Level 1-B; GED-Level 1-C.....
GED-Level 2-A; GED-Level 2-B; GED-Level 2-C.....

I should end with

5 grups (A,B,C,D,E) for Level 1 and
5 grups (A,B,C,D,E) for Level 2

Plus, if more records are created, the number of groups increases.

Fields

Level
record id
program (GED)

I hope makes sense.

100/10 will end with 10 groups that each grup will have a letter that represents it.

------------------------------
Data System Analyst (Administrator)
------------------------------

MarkShnier__You

Qrew Legend

9 months ago

This 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
------------------------------

Data_SystemAnal
Qrew Cadet
9 months ago
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
  Qrew Legend
  9 months ago
  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
  ------------------------------
  - Data_SystemAnal
    Qrew Cadet
    9 months ago
    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)
    ------------------------------