i'm having trouble dynamically creating a subset of external data

[sschardan]

I am working on a personalized calendar project that is functioning, but I foresee issues in rendering time and would like to fix this.

 

Right now, my rule that generates the table for each month loops through the entire external data file to find custom events for each day. So for a 31 day month, my external data file is looped through 31 times. This works, but it is going to drastically slow down my rendering time.

 

What I would like to do is have another rule (OnRecordStart?) that will loop through my external data file to find the desired matching records, then create a subset file that my calendar table will subsequently loop through. That way my table rule is looping through 20-40 records instead of 5000 - 10,000 for each day.

I would think that this is possible, but I have no idea how to do it. Could someone point me in the right direction? Thanks

[Dan Korn]

You should be able to accomplish what you need by iterating through the data once and adding the items to a new object in memory which indexes them by date without writing the data back to disk. It's hard to describe this in the abstract, though, so if you can post a bit of what you have now it would be easier to show you what I mean.

[sschardan]

Sure, Dan. Here is my OnJobStart rule:

 

customEvents = new ExternalDataFileEx('sample customer data.txt', '\t');

 

if(customEvents.valid != true)

{

Print('Failed to link to the external data file in OnJobStart');

}

 

And here is an excerpt from my calendar table:

 

{

Rule("OnJobStart");

}

 

do

{

result += "\n<row>";

for (var day = 0; day < 8; day++)

{

result += "\n\t<cell>";

 

if (day == 0)

 

{

result += '<br>'

result += '<br>'

result += '<br>'

theDate.setDate(theDate.getDate() - 1);

}

 

else

 

if (theDate.getMonth() == JSMonth)

{

 

result += theDate.getDate();

 

numRecsExtDF = customEvents.recordCount;

 

for (recordWalker=1; recordWalker <= numRecsExtDF; recordWalker++)

 

{

if (customEvents.GetFieldValue(recordWalker, 'Date') == theDate.getDate() && customEvents.GetFieldValue(recordWalker, 'Company') == Field("Company") && customEvents.GetFieldValue(recordWalker, 'Name') == Field("Name") && customEvents.GetFieldValue(recordWalker, 'Month') == Month)

 

{

var CustomEvent = customEvents.GetFieldValue(recordWalker, 'Event')

result += '<br>'

result += '<f name = "HelveticaNeueLT Pro 57 Cn">' + CustomEvent + '</f>';

}

}

 

 

}

 

theDate.setDate(theDate.getDate() + 1);

}

}

while (theDate.getMonth() != (JSMonth+1)%12)

 

Thanks!

[Dan Korn]

Okay, thanks for that extra information. A couple of things, though:

 

It would be a lot easier (for me or anyone else) to figure out something that will work with your specific job if you posted the entire job, or at least enough of it that I could actually run the rule, including the data file. I'm kind of stabbing in the dark here without it. That is, I can take a job with what I think is similar data and write a rule that works with it, and I can make what I think are the changes necessary to work with your job, but I can't really test it.

 

Also, when you're posting code, please click on the Advanced button underneath the text box here on the forum and then after you paste your code, select it and click the # button. This will surround the code in [noparse]

[/noparse] tags and it will be more obvious where the code snippet begins and ends, and you won't lose all the indentation.  Feel free to use the [i]Preview Post[/i] button, or to edit your post after you submit it, as well.

Alright, now I'll try to answer your question. 

The thing to remember is that every JavaScript object has what's called an associative array, or map, or properties.  This means that you can access the values of properties of an object very quickly without having to iterate over all of them (like you would have to do with the ExternalDataFileEx object).  So, you can do the iteration of the external file once and cache the data using an object's property map, and then you can look up the property values by their "keys" very quickly as you need them.

So you can do something like this in your OnJobStart rule:
[code]var SortedEvents = new Object;

for (var recordWalker=1; recordWalker <= customEvents.recordCount; recordWalker++)
{
   var key = [customEvents.GetFieldValue(recordWalker, 'Month'),
               customEvents.GetFieldValue(recordWalker, 'Date'),
               customEvents.GetFieldValue(recordWalker, 'Company'),
               customEvents.GetFieldValue(recordWalker, 'Name')].join('\t');
   if (!SortedEvents[key])
       SortedEvents[key] = new Array;
   SortedEvents[key].push(customEvents.GetFieldValue(recordWalker, 'Event'));
}

(Again, I'm not 100 percent sure this will work because I don't have your job to test against.)

 

Then when you're building the table, you can access the cached data like so:

var key = [Month, theDate.getDate(), Field("Company"), Field("Name")].join('\t');
result += SortedEvents[key].join("<br>\n");

This should be much quicker than iterating the external data file multiple times.

[sschardan]

Thanks, Dan. Sorry for confusion on posting the code, I was unaware of the #.

 

Here is my original OnJobStart rule:

 

customEvents = new ExternalDataFileEx('sample customer data.txt', '\t');

if(customEvents.valid != true)
{
   Print('Failed to link to the external data file in OnJobStart');
}

 

and here is my original rule for April:

 

if(FusionPro.Composition.isPreview == true || FusionPro.inValidation == true)
{
   Rule("OnJobStart");
}


var Month = 1;
var Year = 2011;

var result = "";

var JSMonth = (Month-1)%12;
var theDate = new Date(Year, JSMonth, 1);
var MonthName = FormatDate(theDate, "lm") ;

result += '<table columns="8" ' +
         'title = "above" ' +
         'alignment = "left" ' +
         'space = "above:120;below:120" ' +
         'margins = "top:200;bottom:200;left:650;right:650" ' +
         'rowformat = "min:100;max:700;start:AsIs;keepnext:false;keepprev:false">\n'

for (var day = 1; day <= 8; day++)
 result += "< column width = 8000 />\n";

result += "<title gap = 25><para>" + MonthName + "</para></title>\n";

result += "<row><cell><cell>s<cell>m<cell>t<cell>w<cell>th<cell>f<cell>s\n";

theDate.setDate(theDate.getDate() - theDate.getDay());

do
{
 result += "\n<row>";
 for (var day = 0; day < 8; day++)
 {
   result += "\n\t<cell>";

   if (day == 0)

   {
       result += '<br>'
       result += '<br>'
       result += '<br>'
       theDate.setDate(theDate.getDate() - 1);
   }

   else

   if (theDate.getMonth() == JSMonth)
   {

       result += theDate.getDate();

       numRecsExtDF = customEvents.recordCount;

       for (recordWalker=1; recordWalker <= numRecsExtDF; recordWalker++)

           {
               if (customEvents.GetFieldValue(recordWalker, 'Date') == theDate.getDate() && customEvents.GetFieldValue(recordWalker, 'Company') == Field("Company") && customEvents.GetFieldValue(recordWalker, 'Name') == Field("Name") && customEvents.GetFieldValue(recordWalker, 'Month') == Month)

                   {   
                       var CustomEvent = customEvents.GetFieldValue(recordWalker, 'Event')
                       result += '<br>'
                       result += '<f name = "HelveticaNeueLT Pro 57 Cn">' + CustomEvent + '</f>';
                   }
           }


   }

   theDate.setDate(theDate.getDate() + 1);
 }
}
while (theDate.getMonth() != (JSMonth+1)%12)

result += "\n</table>";
return result;

 

When I change the OnJobStart rule to look like this:

 

customEvents = new ExternalDataFileEx('sample customer data.txt', '\t');

if(customEvents.valid != true)
{
   Print('Failed to link to the external data file in OnJobStart');
}

var SortedEvents = new Object;

for (var recordWalker=1; recordWalker <= customEvents.recordCount; recordWalker++)
{
   var key = [customEvents.GetFieldValue(recordWalker, 'Month'),
               customEvents.GetFieldValue(recordWalker, 'Date'),
               customEvents.GetFieldValue(recordWalker, 'Company'),
               customEvents.GetFieldValue(recordWalker, 'Name')].join('\t');
   if (!SortedEvents[key])
       SortedEvents[key] = new Array;
   SortedEvents[key].push(customEvents.GetFieldValue(recordWalker, 'Event'));
}[/Code]

 
and my April rule to look like this:
 
[code]if(FusionPro.Composition.isPreview == true || FusionPro.inValidation == true)
{
   Rule("OnJobStart");
}


var Month = 1;
var Year = 2011;

var result = "";

var JSMonth = (Month-1)%12;
var theDate = new Date(Year, JSMonth, 1);
var MonthName = FormatDate(theDate, "lm") ;

result += '<table columns="8" ' +
         'title = "above" ' +
         'alignment = "left" ' +
         'space = "above:120;below:120" ' +
         'margins = "top:200;bottom:200;left:650;right:650" ' +
         'rowformat = "min:100;max:700;start:AsIs;keepnext:false;keepprev:false">\n'

for (var day = 1; day <= 8; day++)
 result += "< column width = 8000 />\n";

result += "<title gap = 25><para>" + MonthName + "</para></title>\n";

result += "<row><cell><cell>s<cell>m<cell>t<cell>w<cell>th<cell>f<cell>s\n";

theDate.setDate(theDate.getDate() - theDate.getDay());

do
{
 result += "\n<row>";
 for (var day = 0; day < 8; day++)
 {
   result += "\n\t<cell>";

   if (day == 0)

   {
       result += '<br>'
       result += '<br>'
       result += '<br>'
       theDate.setDate(theDate.getDate() - 1);
   }

   else

   if (theDate.getMonth() == JSMonth)
   {

       result += theDate.getDate();

       var key = [Month, theDate.getDate(), Field("Company"), Field("Name")].join('\t');
       result += SortedEvents[key].join("<br>\n");


   }

   theDate.setDate(theDate.getDate() + 1);
 }
}
while (theDate.getMonth() != (JSMonth+1)%12)

result += "\n</table>";
return result;

 

I get that the variable SortedEvents is undefined. Please advise.

 

I am attaching some sample data.

Base data.txt

sample customer data.txt

[Dan Korn]

I get that the variable SortedEvents is undefined. Please advise.

Either declare the variable in the JavaScript Globals, or remove the "var" keyword from the declaration.

SortedEvents = new Object;

[sschardan]

Dan,

 

I tried both suggestions, removing the var declaration in the OnJobStart rule or declaring the variable in JavaScript globals. In both instances, I get an error message that SortedEvents[key] contains no properties.

[Dan Korn]

I get an error message that SortedEvents[key] contains no properties.

Sorry, we need to test for that:

if (SortedEvents[key])
 result += SortedEvents[key].join("<br>\n");

[sschardan]

That did it. Thank you very much for your help and your patience.