Cross Product

  • 0
  • 1
  • Question
  • Updated 3 months ago
  • In Progress
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.
Photo of Noah McBride

Noah McBride

  • 356 Points 250 badge 2x thumb

Posted 3 months ago

  • 0
  • 1
Photo of Ⲇanom the ultimate (Dan Diebolt)

Ⲇanom the ultimate (Dan Diebolt), Champion

  • 26,262 Points 20k badge 2x thumb
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, "  "));

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?

                                           
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 50,850 Points 50k badge 2x thumb
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.
Photo of Noah McBride

Noah McBride

  • 356 Points 250 badge 2x thumb
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.
Photo of Ⲇanom the ultimate (Dan Diebolt)

Ⲇanom the ultimate (Dan Diebolt), Champion

  • 26,242 Points 20k badge 2x thumb
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.
Photo of Noah McBride

Noah McBride

  • 356 Points 250 badge 2x thumb
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.
Photo of Ⲇanom the ultimate (Dan Diebolt)

Ⲇanom the ultimate (Dan Diebolt), Champion

  • 26,242 Points 20k badge 2x thumb
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
https://haversineconsulting.quickbase.com/db/bkfwuwx2p?a=td

Pastie Database
https://haversineconsulting.quickbase.com/db/bgcwm2m4g?a=dr&rid=462
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 50,850 Points 50k badge 2x thumb
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.
Photo of Jason

Jason

  • 1,012 Points 1k badge 2x thumb
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.

Photo of Ⲇanom the ultimate (Dan Diebolt)

Ⲇanom the ultimate (Dan Diebolt), Champion

  • 26,242 Points 20k badge 2x thumb
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.
Photo of Jason

Jason

  • 1,012 Points 1k badge 2x thumb
I look forward to it as always