Hello, friends! I've been designing dashboards with Retool for a long time, and I just ran into a very strange bug.
I have a dashboard that first runs a SQL Server query on a database, and that works fine. Then, the resulting query is passed through a JS transformer before feeding into several scorecards.
Everything about the dashboard works perfectly fine most of the time, but when I select date ranges that include several specific dates, the JS transformer returns an non helpful "function run failed" error with an empty message. I've triple-checked the database and ca confirm that there is nothing unusual about the results on the date ranges where this happens, and I'd be very surprised if anything in the JS code was bugged in a way that it only failed on specific dates.
Has anyone run into something like this before? Is there anything I could try? The debugger is also not helping at all.
@EnriqueOO Hi there, are you able to share your transformer code? Then I can try to reproduce the issue and check on my end.
Thanks, Francisco! First, I'm running a very simple SQL Query that returns all results in the date range for the join of two tables:
select finance_pipedrive_person_data.DealId, finance_pipedrive_person_data.FirstClickChannel, finance_pipedrive_data.add_time, finance_pipedrive_data.status, finance_pipedrive_data.deleted, finance_pipedrive_data.value, finance_pipedrive_data.pipeline_id, finance_pipedrive_data.lost_reason from finance_pipedrive_person_data left join finance_pipedrive_data on finance_pipedrive_data.Id = finance_pipedrive_person_data.DealId where finance_pipedrive_data.add_time >= {{moment(dateRange3.value.start).subtract(1, 'days').format('YYYY-MM-DD')}} and finance_pipedrive_data.add_time <= {{moment(dateRange3.value.end).add(1, 'days').format('YYYY-MM-DD')}}
and pipeline_id = 1
That query is called MasterQuery, and then I'm referencing it in the more complex transformer below:
var queryRes = {{MasterQuery.data}};
//add 1 hour to all deals for timezone reasons
for (let i = 0; i < queryRes.add_time.length; i++) {
queryRes.add_time[i] = new Date(queryRes.add_time[i]);
queryRes.add_time[i] = new Date(queryRes.add_time[i].getTime() + 3600000);
}
//replace array entries with x if the new date is not within range
for (let i = 0; i < queryRes.add_time.length; i++) {
if (queryRes.add_time[i] < new Date({{dateRange3.value.start}}) || queryRes.add_time[i] > new Date(moment({{dateRange3.value.end}}).add(1, 'days'))) {
for (let key in queryRes) {
queryRes[key][i] = 'x';
}
}
}
//keep only all values that are not x (which means they're within date range)
var filtered = {};
for (let key in queryRes) {
for (let i = 0; i < queryRes[key].length; i++) {
filtered[key] = queryRes[key].filter((e) => e != 'x');
}
}
var open = JSON.parse(JSON.stringify(filtered));
var won = JSON.parse(JSON.stringify(filtered));
var lost = JSON.parse(JSON.stringify(filtered));
var deleted = JSON.parse(JSON.stringify(filtered));
//modify open deals object changing all properties where the deal is not open to "na"
for (let i = 0; i < open.add_time.length; i++) {
if (open.status[i] != 'open') {
for (let key in open) {
open[key][i] = 'na';
}
}
}
//filter out all na values
for (let key in open) {
for (let i = 0; i < open[key].length; i++) {
open[key] = open[key].filter((e) => e != 'na');
}
}
//modify won deals object changing all properties where the deal is not open to "na"
for (let i = 0; i < won.add_time.length; i++) {
if (won.status[i] != 'won') {
for (let key in won) {
won[key][i] = 'na';
}
}
}
//filter out all na values
for (let key in won) {
for (let i = 0; i < won[key].length; i++) {
won[key] = won[key].filter((e) => e != 'na');
}
}
//modify lost deals object changing all properties where the deal is not open to "na"
for (let i = 0; i < lost.add_time.length; i++) {
if (!lost.lost_reason[i].includes('LOST')) {
for (let key in lost) {
lost[key][i] = 'na';
}
}
}
//filter out all na values
for (let key in lost) {
for (let i = 0; i < lost[key].length; i++) {
lost[key] = lost[key].filter((e) => e != 'na');
}
}
//modify deleted deals object changing all properties where the deal is not open to "na"
for (let i = 0; i < deleted.add_time.length; i++) {
if (!deleted.lost_reason[i].includes('DELETED')) {
for (let key in deleted) {
deleted[key][i] = 'na';
}
}
}
//filter out all na values
for (let key in deleted) {
for (let i = 0; i < deleted[key].length; i++) {
deleted[key] = deleted[key].filter((e) => e != 'na');
}
}
return {open: open, won: won, lost: lost, deleted: deleted};