Forum Discussion

NoahMcBride's avatar
NoahMcBride
Qrew Cadet
7 years ago

Cross Product

This is something that I have a fairly good understanding of in other database tools such as Sql, but do not know if Quickbase could handle.

I am trying to create a training application that allows for records to be created of employees attending training. All of these trainings require Test Scores and Attendance. Some of these trainings are done in Groups. For Example:

Training                              Group                     Employee

T1 G1                                 G1                         E1
T2 G1                                 G2                         E2
T3 G1                                 G3
T4 G2
T5 G2
T6 G3

I would like to be able to select a list of employees and a single group and then have a cross product of each training and employee be created, so that attendance and scores can be recorded.

Group = G1
Employeee = E1 , E2

Result
G1*E1 = ( (T1 , E1 ) , ( T2 , E1 ) , (T3 , E1)  
G1*E2 =  (T1 , E2 ) ( T2 , E2 ) , (T3 , E2)


Without this, the best way that I can think to record this is the embed a report where a record is created for each employee and training in the group individually. Is these a better way to create these records?

Thanks.
APIs and custom code
Data import and export
Forms
  • _anomDiebolt_'s avatar
    _anomDiebolt_
    Qrew Elite
    7 years ago
    This can easily be done with script. However, I don't understand the structure of your tables or where you want the cross product data to show up.

    Nevertheless, here is some sample code that will create the cross product of two arrays ["T1", "T2", "T3"] _ ["E1", "E2"]

    function crossProductOf() {
      return _.reduce(arguments, function(a, b) {
        return _.flatten(_.map(a, function(x) {
          return _.map(b, function(y) {
            return x.concat();
          });
        }), true);
      }, [ [] ]);
    };
      var array1 = ["T1", "T2", "T3"];
    var array2 = ["E1", "E2"];
      var crossProduct = crossProductOf(array1, array2); 
      console.log(JSON.stringify(crossProduct, null, "  "));

    output:
    [
      ["T1","E1"],
      ["T1","E2"],
      ["T2","E1"],
      ["T2","E2"],
      ["T3","E1"],
      ["T3","E2"]
    ]
    To implement this you need to use script to obtain the data for each array using API_DoQuery and then call the above code and either pump the cross product into a table or spit out the results as HTML. 

    MATH TRIVIA: Did you know that the cross product or Cartesian product is named after the famous mathematician Ren� Descarte?

                                               
  • QuickBaseCoachD's avatar
    QuickBaseCoachD
    Qrew Captain
    7 years ago
    There may also be a native solution but it�s unclear to me if you are wanting to do a mathematical calculation and what your data structure is for parent and child relationships.
    • NoahMcBride's avatar
      NoahMcBride
      Qrew Cadet
      7 years ago
      Okay,

      I have 5 Tables

      1. Employee
      2. Training
      3. Group
      4. Event
      5. Employee Training Map

      Parent            Child
      Employee      Employee Training Map
      Training          Employee Training Map
      Event             Employee Training Map
      Group            Training
      Event             Group

      I would like to be able to create an Event and select a Group and a number of employees. Then I would like to create a record for each Training and Employee that I have selected in the Employee Training Map.

      Example

      Before
      Training         Group        Employee      Event          Employee Training Map
      T1    G1         G1             E1                   
      T2    G1         G2             E2                                    
      T3    G2                           E3                                    

      Create an Event:                                                                                 
      Event 
      G1 E1 E2

      Quickbase will automatically create records in Employee Training Map

      After
      Training         Group        Employee      Event          Employee Training Map
      T1    G1         G1             E1                 G1 E1 E2    T1   E1
      T2    G1         G2             E2                                     T1  E2
      T3    G2                           E3                                    T2  E1
                                                                                       T2 E2
    • _anomDiebolt_'s avatar
      _anomDiebolt_
      Qrew Elite
      7 years ago
      Three points:

      (1) Can you confirm that this is the ER Diagram for your tables?



      (2) I don't understand what you are indicating by tabular information you have listed under the Before, Event, and After headings as the column headings appear to be tables names not field names. Can you clarify what the fields are? 

      (3) I get the impression you want to start on a form that creates and events and select a Group of Training and a set of Employees. A Group of Trainings can be selected with a lookup. But how do you propose to select a set of Employees?
    • NoahMcBride's avatar
      NoahMcBride
      Qrew Cadet
      7 years ago
      1. Yes, this diagram is correct.

      2. Each line is supposed to be a record in a table. When an event is added, a number of records are added equal to the cross product of the number of Employees and Trainings selected in the Event.

      3. In my mind, the employees would be selected individually. There is not really a better way to group the employees logically. So our Training Coordinator would select the Group (G1) and the Related Employees (E1, E2, E3, E4). Then the records would be created based on the cross product of the Trainings in the Group and the Employees selected.
  • JasonJohnson's avatar
    JasonJohnson
    Qrew Assistant Captain
    7 years ago
    Even though the heavy hitters have responded to this I am going to take a swing at a native solution.
    I would have a 4 table solution
    Table 1  - Training
    Table 2 - Groups
    Table 3 - Employees
    Table 4 - Test Scores

    The Trainer can create the training, then create groups and select the employees in the groups and keep the scores. The scores table could be designed to keep not just the score of each employee test but what group and training session.
    This structure would allow the evaluations of employees, groups, and trainers from the scoring I am , however, assuming that when you want to get such detail the goal is not to only evaluate the employees but the trainings as well. How else could you know when group size is having a direct affect on scores?
    You will need some automation and a few buttons based on the details of the hows of your training structure. 

    As always Dan is right anything can be done in scripting. If you want me to explain more about my response feel free to contact me.

    • _anomDiebolt_'s avatar
      _anomDiebolt_
      Qrew Elite
      7 years ago
      M>I don't do script.
      J>anything can be done in scripting.

      I am going to create a demo using the originally described tables. I think you are going to be stunned how simple the script is and my solution will introduce a new technique and perspective on how forms can be used.