Create Dynamic Index with External Data - multi-line

[mdlivels]

Hi-

I need to be able to create a dynamic index from a provider listing that's built from external data with multi-line records. I need the index to report back the page that the Provider is listed on in the listing part (code below).

 

Seems like I should use cross reference tags, but I can't figure out how to apply that given that I'm looking for a specific provider's name among hundreds that are listed in the directory by specialty. The index is to be displayed in alphabetical order, show the provider's name, specialty and page number that it appears on. Any ideas?

 

 

This creates the provider listing just how i want it - Now I just need to create the index and populate the page that they fall on.

 

 

 

var result = "";

Provider = new ExternalDataFileEx("providers.xlsx", "Excel", "providers");
var rows = Provider.SortBy("SPECIALTY").FindRecords("Adult Companion");
var content_fields = ["last_name","address1","address2","address3","city","state","zip","phone","hours","URL"];

for (ri = 0; ri < rows.length; ri++) { 

   result += "<p>";
       // loop through each value of content_fields array
       for (i = 0; i < content_fields.length; i++) { 

              // check to make sure there is a value for the content field
           if (Provider.GetFieldValue(rows[ri], content_fields[i]) != '') {

               if (content_fields[i] == 'last_name') {
                   result += "<b/><color name=black>" + Provider.GetFieldValue(rows[ri], content_fields[i])+ "</b><br>";
               } else if (content_fields[i] == 'address1') {
                   result += "<color name=black>" + Provider.GetFieldValue(rows[ri], content_fields[i])+ "<br>";
               }else if (content_fields[i] == 'address2') {
                   result += "<color name=black>" + Provider.GetFieldValue(rows[ri], content_fields[i])+ "<br>";
               }else if (content_fields[i] == 'address3') {
                   result += "<color name=black>" + Provider.GetFieldValue(rows[ri], content_fields[i])+ "<br>";
               }else if (content_fields[i] == 'city') {
                   result += "<color name=black>" + Provider.GetFieldValue(rows[ri], content_fields[i])+ ", ";
               } else if (content_fields[i] == 'state') {
                   result += "<color name=black>" + Provider.GetFieldValue(rows[ri], content_fields[i])+ " ";
               }else if (content_fields[i] == 'zip') {
                   result += "<color name=black>" + Provider.GetFieldValue(rows[ri], content_fields[i])+ "<br>";
               }else if (content_fields[i] == 'phone') {
                   result += "<color name=black>"  + "Phone/Telefono: " + Provider.GetFieldValue(rows[ri], content_fields[i])+ " <br>";
               }else if (content_fields[i] == 'hours') {
                   result += "<color name=black>" + Provider.GetFieldValue(rows[ri], content_fields[i])+ "<br>";
               }else if (content_fields[i] == 'URL') {
                   result += "<color name=black>" + "URL: "  + Provider.GetFieldValue(rows[ri], content_fields[i])+ "<br>";
               } else {
                   result += "<color name=black>" + Provider.GetFieldValue(rows[ri], content_fields[i])+ "<br>";
               }

           }

       }

   result += "<br></p>";

}

return result;

[Dan Korn]

We call this a "table of contents" job (or an "index" job).

 

Yes, you need to use <crossref> tags. These are documented in the FusionPro Tags Reference Guide, and a Google search for "FusionPro table of contents" yields several examples here on the forum, including:

http://forums.pti.com/showthread.php?t=4351

http://forums.pti.com/showthread.php?t=4200

http://forums.pti.com/showthread.php?p=7255#poststop

 

Obviously you need to put a <destination> tag down with each of the provider listings, so that FusionPro knows what page number to put down when you emit the <crossref> tags in the table of contents/index.

 

It's hard to offer more specific suggestions for what to do in your template without seeing the template files.

 

That said, I can make a suggestion about how to reduce the amount of repetitive code in what you did post, like so:

var Provider = new ExternalDataFileEx("providers.xlsx", "Excel", "providers");
var rows = Provider.SortBy("SPECIALTY").FindRecords("Adult Companion");

var content_fields = ["last_name","address1","address2","address3","city","state","zip","phone","hours","URL"];

var prefixes = 
{
   phone: "Phone/Telefono: ",
   URL: "URL: ",
}

var delims = 
{
   city: ", ",
   state: " ",
}

var result = [];
for (ri = 0; ri < rows.length; ri++)
{ 
   var row = rows[ri];
   var entry = "";

   // loop through each value of content_fields array
   for (i = 0; i < content_fields.length; i++)
   {
       var fieldName = content_fields[i];
       var fieldVal = Provider.GetFieldValue(row, fieldName);

       // check to make sure there is a value for the content field
       if (!fieldVal)
           continue;

       entry += prefixes[fieldName] || "";
       entry += fieldVal;
       entry += delims[fieldName] || "<br>";
   }

   // TO DO: add <crossref> tag here

   result.push(entry);
}

return result.join("<br>"); // or maybe "<p>"

And I imagine that the code to actually put down the page number with each listing in the TOC would be a line something like this, right before the result.push(entry) call (where the "TO DO" comment is):

entry += '<crossref name="' + Provider.GetFieldValue(row, "last_name") + '">Page <$pagenum></crossref>';

Again, it's hard to be more specific without seeing more of your job.

[mdlivels]

Thanks, Dan. I think I'm almost there. The original code I posted was for the provider listing where the <destination>tag should go...problem is, I'm not sure how to incorporate it into the code since it's looping through an XDF and not a specific field one at a time. The code below is what I've started for the actual index, which is there I assume I'll put the <crossref> tag, right?

 

The provider listing is different from the index listing as they are sorted by specialty and alphabetically, respectively. Here's the current index code;

 

var result = "";
IndexXDF = new ExternalDataFileEx("providersIndex.xlsx", "Excel", "providers");

var looping = true;

var starting_row = 1;

var response = '';

while (looping) { // while looping is true

   if (IndexXDF.GetFieldValue(starting_row, 'last_name') != '') {
       //response += IndexXDF.GetFieldValue(starting_row, 'last_name'); TESTING RESPONSE 

       response += "<color name=black>" + IndexXDF.GetFieldValue(starting_row, 'last_name') ;

       if (IndexXDF.GetFieldValue(starting_row, 'SPECIALTY') != '') {
           response += "<color name=black> (" + IndexXDF.GetFieldValue(starting_row, 'SPECIALTY')+ ") <br>";
       }

       starting_row++;

   } else {
       looping = false;
   }

}

return response;

Edited September 17, 2018 by mdlivels

[Dan Korn]

So again, it's hard to figure this out by looking at your job through the narrow keyhole of just a rule or two, without having all of the job files. If you posted the entire job, then you would very likely get an answer to your question (either from me or someone else) much more quickly than with all this back-and-forth.

 

Anyway, your sample index code has a couple of problems. One, you have a "return response" line about halfway down (at line 29), followed by a bunch of other code which will never be run, since you return before reaching it. And two, in that unreachable code (at line 44), you have a "<crossref=>" tag, which is malformed and bogus. The <crossref> tag requires a "name" attribute, as does the <destination> tag. What that name attribute should be is a unique identifier for every item that you want to have a cross-reference to.

 

That unique identifier has to be put as the "name" attribute of both (a) the <destination> tag, where the content in the main listing area is (i.e. what you want to have a reference to), and (b) the <crossref> tag in the table of contents or index (i.e. where you put down a reference).

 

Now, what that unique identifier should be, I'm not sure, especially since I don't have the data. I would assume you want something other than just the last name, and maybe add on the specialty, for disambiguation. But if you want to just use the last name, then what I suggested in my previous post should work:

entry += '<crossref name="' + Provider.GetFieldValue(row, "last_name") + '">Page <$pagenum></crossref>';

That needs to go into the TOC, somewhere like where you have that bogus "<crossref=>" tag.

 

And basically the same code needs to go into the listing, but as a <destination> tag, like so:

entry += '<destination name="' + Provider.GetFieldValue(row, "last_name") + '">';

[mdlivels]

So very close here - everything works except one thing - as soon as I try to make the index page overflow into an overflow page (which will be necessary once the real data flows in) the page numbers disappear. It works and displays page numbers but only if not trying to use the overflow page. I am stumped.

 

This time I'm including the collected files for a last stab at wrapping this thing up. The main rules are "Index" and "Adult Companion". I will simply stack the "Specialty" rules in the listing section, and that works fine, it's just the page numbers in the index that are currently out of my reach.

ProviderDirectory.zip

[Dan Korn]

So very close here - everything works except one thing - as soon as I try to make the index page overflow into an overflow page (which will be necessary once the real data flows in) the page numbers disappear. It works and displays page numbers but only if not trying to use the overflow page. I am stumped.

I can answer this one without even looking at the job files.

 

As I noted in this post:

Since you can use a variable like $pagenum in a text frame, obviously FusionPro has to know the page numbers before it typesets them. So it has to compose any text flows which can overflow to new pages first, to figure out exactly how many pages there are in the record, and what the page number is for each page. Then it goes and composes all the other text flows (which don't overflow), and in fact, it's only in these flows that don't overflow where you can use the $pagenum and $pagecount variables.

 

You can only typeset (output) page numbers in frames (flows) that DO NOT overflow to other pages.

 

To reiterate the explanation quoted above: FusionPro doesn't yet know how many pages there are, or even where any text on overflowing flows will end up, until it first composes all the overflowing flows into all of their overflowing pages. Only after all that is done do we know where everything is going to end up, and only then we can typeset all the non-overflowing flows, which can use the page numbers (both the number of the page that the frame itself is on, and the numbers of any pages where other content is for <crossref> tags.

 

So, you need to have a fixed number of pages for your index/TOC. Most of the time, one or two pages should be enough. You can copyfit the text in the TOC frame as needed.

[mdlivels]

Thanks, Dan. It's not a good outcome, but you've saved me from continuing to chase my tail! I'll have to find another solution as these index pages (even for a very small provider directory) are 4-5 pages. Large ones can get into the 10's of pages, so I've got to find a way to automate it. I appreciate all of your help along the way here, the directory listing tips you gave me were extremely helpful. Thanks!

[Dan Korn]

Thanks, Dan. It's not a good outcome, but you've saved me from continuing to chase my tail! I'll have to find another solution as these index pages (even for a very small provider directory) are 4-5 pages. Large ones can get into the 10's of pages, so I've got to find a way to automate it. I appreciate all of your help along the way here, the directory listing tips you gave me were extremely helpful. Thanks!

So to clarify, you can typeset page numbers in a multi-frame text flow, even if that text flow has frames on multiple Body pages. You just can't typeset page numbers in a flow that's set to overflow to Overflow pages.

 

I do understand the problem you're facing. I'll talk to my colleagues here and see if we can think of a better way to handle this.

Edited September 20, 2018 by Dan Korn
typo