Expand all | Collapse all

Cross Product

  • 1.  Cross Product

    Posted 07-31-2018 15:08
    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

    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?


  • 2.  RE: Cross Product

    Posted 08-02-2018 04:58
    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([y]);
        }), true);
      }, [ [] ]);
    var array1 = ["T1", "T2", "T3"];
    var array2 = ["E1", "E2"];
    var crossProduct = crossProductOf(array1, array2); 
    console.log(JSON.stringify(crossProduct, null, "  "));

    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?


  • 3.  RE: Cross Product

    Posted 08-02-2018 10:11
    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.

  • 4.  RE: Cross Product

    Posted 08-02-2018 15:42

    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.


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

    Create an Event:                                                                                 
    G1 E1 E2

    Quickbase will automatically create records in Employee Training Map

    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

  • 5.  RE: Cross Product

    Posted 08-03-2018 11:09
    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?

  • 6.  RE: Cross Product

    Posted 08-06-2018 12:07
    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.

  • 7.  RE: Cross Product

    Posted 08-07-2018 08:48
    This can easily be done with script. However, the GUI portion of the solution would be a lot simpler if there was a way to select a set of Employees as a group (department, job title, location, manager etc) in a similar way that Training are selected through a common Group.

  • 8.  RE: Cross Product

    Posted 08-07-2018 11:41
    I agree. Unfortunately, our goal is to create a training app that is flexible, and will allow our training coordinator to sign up very different groups of employees for different employees.

    Could you point me toward a java script resource that could get me started, or provide me with some basic instructions.

    Thank you so much.

  • 9.  RE: Cross Product

    Posted 08-07-2018 12:36
    I think you should refine the workflow and criteria that would be used to select which Employees are to participate in the cross product. You will be creating a substantial GUI to select "random" employees. See this demo:

    Process Selected

    Pastie Database

  • 10.  RE: Cross Product

    Posted 08-07-2018 12:46
    My approach would be native as despite Dan's efforts, I don't do script.

    I would make an Event record and then allow the user to "Quick Select" off a list of employees to add those employees to the Event.  by Quick Select, I mean that I have a technique which basically has the user click a button on the Event which creates or edits the current user in a table called User Focus and records the Record ID# of the Event.  Then I present the user with a list of employees where the user then can click click click to select which employees attended that Event.  Each click will create a record under that Event Record as a child, but does not disturb the report, so that it is quick for the user to continue to quickly select employees.  The user may use the normal filter box and dynamic filters on the table report to help narrow down which employees to select.

    Then on that report there would be a button to return to the Focus Event.

    So now we have our group of employees defined under the Event and now we need to create a record for each employee for each training type that was covered in that event.  So then the user would click to see a list of available training courses, and when they click, it would create an employee-training record for each employee for that training course.  That would be done with API_RunImport to basically import the employee list for the Focus group into the table of employee-trainings for the Focus Training Course.

    If you wanted to work together to get this working on a Go To Meeting session, contact me via the information on my website  QuickBaseCoach.com.

    It would probably take a couple or maybe three of hours to get this working.

    I know that my approach may feel not normal for someone used to script, but it works well if you get yourself into a low code / no code mindset.

  • 11.  RE: Cross Product

    Posted 08-09-2018 20:36
    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.

  • 12.  RE: Cross Product

    Posted 08-10-2018 12:30
    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.

  • 13.  RE: Cross Product

    Posted 08-10-2018 12:32
    I look forward to it as always