Forum Discussion
JoseVieira
8 years agoQrew Trainee
Thanks for the quick repy,
Let me try to explain with a little more details. The app is to track people skills, so we know who needs training on what. So, the tables are:
Table 1: People
This is the script. It's a simplified version, without error handling. All I need is to execute it each time a new person is added. Actually, I'll need a similar script for new skills, but the idea is the same:
Thanks!
Let me try to explain with a little more details. The app is to track people skills, so we know who needs training on what. So, the tables are:
Table 1: People
- ID,User (unique),Role
- 1,Joe,Role1
- 2,Janet,Role2
- 3,Carl,Role1
- 4,Mary,Role3
- 5,Carol,Role2
- 6,Thomas,Role1
- ID,Description,Role
- 1,Knows A,Role1
- 2,Knows B,Role1
- 3,Knows C,Role2
- 4,Knows D,Role1
- 5,Knows E,Role3
- 6,Knows F,Role1
- 7,Knows G,Role2
- ID,Person ID,Person name,Role from Person,Skill ID,Skill Description,Role from skill,Assigned,Status
- 1,1,Joe,Role1,1,Knows A,Role1,1,0
- 2,1,Joe,Role1,2,Knows B,Role1,1,1
- 3,1,Joe,Role1,3,Knows C,Role2,0,0
- 4,1,Joe,Role1,4,Knows D,Role1,1,0
- 5,1,Joe,Role1,5,Knows E,Role3,0,0
- 6,1,Joe,Role1,6,Knows F,Role1,1,0
- 7,1,Joe,Role1,7,Knows G,Role2,0,0
- 8,2,Janet,Role2,1,Knows A,Role1,0,1
- 9,2,Janet,Role2,2,Knows B,Role1,0,0
- 10,2,Janet,Role2,3,Knows C,Role2,1,0
- 11,2,Janet,Role2,4,Knows D,Role1,0,0
- 12,2,Janet,Role2,5,Knows E,Role3,0,0
- 13,2,Janet,Role2,6,Knows F,Role1,0,0
- 13,2,Janet,Role2,7,Knows G,Role2,1,1
- ...
This is the script. It's a simplified version, without error handling. All I need is to execute it each time a new person is added. Actually, I'll need a similar script for new skills, but the idea is the same:
$(document).ready(function(){ //get person id from querystring
var person = getQueryVariable('rid');
//clean up matrix items without related skills
var url = 'bmzta34wf?a=API_PurgeRecords&query={6.EX.}&apptoken=bh89wwc8sxc9ac2g83fme83nsi';
var promise= $.get(url);
//clean up matrix items without related people
url = 'bmzta34wf?a=API_PurgeRecords&query={31.EX.}&apptoken=bh89wwc8sxc9ac2g83fme83nsi';
promise= $.get(url);
//get all skills from Skills table
url = 'bmzizdnpr?a=API_DoQuery&clist=a&apptoken=bh89wwc8sxc9ac2g83fme83nsi';
promise= $.get(url);
$.when(promise).then(function(xml){
//loop through skills
$(xml).find('record').each(function(){
//get skill id
var skill = $(this).find("record_id_").text();
//check for existing combination of skill and person on 'SKills List' table
var url = 'bmzta34wf?act=API_DoQueryCount&clist=a&query={6.EX.' + skill + '}AND{31.EX.' + person + '}&apptoken=bh89wwc8sxc9ac2g83fme83nsi';
var promise= $.get(url);
$.when(promise).then(function(xml){
$(xml).find('qdbapi').each(function(){
//check if an item with the skill x person combination already exists
var matches = $(this).find('numMatches').text();
//if it does not exist, create it
if(matches == 0) {
var url = 'bmzta34wf?act=API_AddRecord&clist=a&_fid_6=' + skill + '&_fid_31=' + person + '&apptoken=bh89wwc8sxc9ac2g83fme83nsi';
var promise= $.get(url);
}
});
});
});
})
})
//function to get querystring values from variables
function getQueryVariable(variable) {
var query = window.location.search.substring(1);
var vars = query.split('&');
for (var i=0;i<vars.length;i++) {
var pair = vars.split('=');
if(pair[0] == variable){return pair[1];}
}
return(false);
}
Thanks!