Jump to content

ExetrnalDataFileEx Question


MIB

Recommended Posts

Hi,

 

I have 2 databases where in one database (which is directly linked to the Acrobat document via FusionPro) I have customer information where each customer has a unique Account Number ie. under the heading "AccID".

 

In the 2nd database, it is external and in the same folder as the 1st database and the Fusionpro Acrobat document. The heading "AccID" is also listed here, but also contains other data ie. SavingsType

 

So, what I want to do is create a rule where I presume I use ExternalDataFileEx funtion to grab the data under the heading "SavingsType" when the "AccID" in the first database matches the "AccID" in the second database and then display that on the document page.

 

Example DB 1

"AccID" "Custname"

000011 James Smith

203030 Carl Wilson

 

Example DB 2

"AccID" "SavingsType"

200001 Easy Saver

000011 High Interest

002000 Interest MAX

203030 Platinum Account

 

Have looked at various examples, but still haven't managed to pull it of.

 

Thanks in advance!

Link to comment
Share on other sites

I think this is what you want.

 

 

I find that FP can use ( in your example ) DB1 through ODBC just fine, but you will need to use ExternalDataFileEx for DB2

 

for DB2 you need to open the file, and remember that columns, if referenced by the number, not name, start their numbering at 0.

What I do below is just bring the relevant row(s) into a tabbed line, with a newline at the end of the row.

This way I simply return the recordwalker contents to a text box, and set the tabs for that text box as I need them.

 

I assume you may have more than one row in DB2 with the same AccID, and this is why you are not using only one file.

 

Example DB 1

"AccID" "Custname"

000011 James Smith

203030 Carl Wilson

 

Example DB 2

"AccID" "SavingsType"

200001 Easy Saver

000011 High Interest

002000 Interest MAX

203030 Platinum Account

000011 Easy Saver

 

 

The above would return :

 

000011 High Interest

000011 Easy Saver

 

for AccID 000011

 

 

 

Copy and paste the text below into a an empty Javascript rule, and you should be good to go

 

XDF = new ExternalDataFileEx("..\\DB2.txt", "\t");

var returnStr = ""

var numRecsExtDF = XDF.recordCount;

{

// this will go through the entire DB2.txt file, from first row to last

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

{

// If the value of the first column in XDF equals the value of your current record's value for AccID, then start grabbing the field values you want

// build to suit, but this works for me.

// Note: you can use XDF.GetFieldValue(recordWalker, 0) or XDF.GetFieldValue(recordWalker, 'AccID') interchangeably, whichever you prefer

if (XDF.GetFieldValue(recordWalker, 0) == Field('AccID'))

{

returnStr += XDF.GetFieldValue(recordWalker, 'AccID');

returnStr += '\t' + XDF.GetFieldValue(recordWalker, 'SavingsType');

returnStr += '\n';

}

}

}

return returnStr;

Link to comment
Share on other sites

  • 2 weeks later...
Thanks Murrayc! When I changed \t to \c to use the comma delimited file, it didn't work, but it does work with \t when I create a tab delimited file.

If it's a comma-delimited file, you should just specify ',' as the delimiter. There's no '\c' escape in JavaScript, but '\t' represents the non-printing tab character.

Link to comment
Share on other sites

  • 2 weeks later...

Hi, everything works really well. It pulls in info from an external DB for each customer. But now I want to start inserting pages based on expiry dates in the external database.

 

I have added an IF statement (see below) to compare the expiry date in the external database with the current date that is in the first DB connected to FusionPro. If 1 month or less away, I want to insert a special page that has a new offer. I have added this page to the FusionPro document and given it a Page name "Offer". It will also include some simple variable data on it.

 

It works to the Point where each customer who has one account (1 record) in the external DB, it inserts the Offer page. But if the customer has several accounts (records) in the external DB and more than one is about to expire, it will insert the page based on the first record it finds in the external database, but will ignore the next record that also has 1 month less to expire.

 

Any ideas?

 

 

XDF = new ExternalDataFileEx("BarnDB.txt", "\t");

var returnStr = ""

var currentmonth = DateFromString(Field("SendDate"))

var numRecsExtDF = XDF.recordCount;

{

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

{

//

if (XDF.GetFieldValue(recordWalker, 'AccID') == Field('AccID'))

{

var expiry = XDF.GetFieldValue(recordWalker, 'Expiry')

if (expiry != "")

{

expirydate = DateFromString(expiry);

var cm = FormatDate (currentmonth, "m");

var bd = FormatDate (expirydate, "m");

var result = bd - cm;

}

if (result > 0 && result < 2)

{ FusionPro.Composition.SetBodyPageUsage("Offer", true);

}

}

}

}

return returnStr;

Link to comment
Share on other sites

Okay, I've played around with different scenarios and added return commands to view the data, so the logic seems to work. But, of course I can't see how the page selection works until I compose the document. Whatever I do it won't select the 'Offer' page more than once if the Account holder has more than one account expiry date in the external DB that matches the condition more than once (ie. expiry date is within 1 month. So, totally stuck now. Any advice appreciated.

XDF = new ExternalDataFileEx("Accounts.txt", "\t");

var returnStr = "";

currentmonth = DateFromString(Field("SendDate"));

var numRecsDB2 = XDF.recordCount;

{

for (var recordWalker = 1; recordWalker <= numRecsDB2; recordWalker++)

{

if (XDF.GetFieldValue(recordWalker, 'AccID') == Field('AccID'))

{

expiry = Trim('\t' + XDF.GetFieldValue(recordWalker, 'Expiry'));

if (expiry != "")

{

expirydate = DateFromString(expiry);

cm = FormatDate (currentmonth, "m");

ed = FormatDate (expirydate, "m");

result += (recordWalker, ed - cm);

result += '\n';

}

if (result > 0 && result < 1)

{

FusionPro.Composition.SetBodyPageUsage ("Offer", true)

}

}

returnStr += Trim('\t' + XDF.GetFieldValue(recordWalker, 'Accounttype'));

returnStr += '\n';

}

}

}

return (returnStr);

DB1

AccID

100

200

300

XDF (DB2)

AccID Expiry

100 30-10-2014

200 20-08-2015

300 30-10-2014

300 10-10-2014

300 15-10-2014

400

So for AcciD 100, that record will have an extra page called 'Offer' assuming current date 29-09-2014

AccID 200 will not

AccID 300 should get the same page called 'Offer' 3 times as the accounts expire within 1 month

AccID 400 does not get an extra page as its empty

All work apart from AccID 300 where I only get one 'Offer' page

Thanks in advance!

Link to comment
Share on other sites

It's hard to diagnose exactly what's going on with this job just by looking at the rule. But with the FusionPro.Composition.SetBodyPageUsage function, all you can do is output the page either one time for the record, or not at all. In other words, you can only get either 0 or 1 instance of any given Body Page per each output record. You can't "use" a page more than once. So, you need to do something else. There are several options, but exactly which one would be best for you job, is, again, hard for me to say without seeing more of it. Anyway, here are some options:

  • Use an Overflow page, and flow text to it from the Body Page.
  • Use a Repeatable Component (Template page), along with an Overflow Page.
  • Repeat the record, with FusionPro.Composition.repeatRecordCount, and emit certain pages based on the FusionPro.Composition.repeatRecordNumber.
  • Create multiple Unused Body Pages, and turn them on in groups as needed.

Link to comment
Share on other sites

Thanks Dan. Then my rule will definitely not work as you say you can only call the page once and I want call it more than once.

 

I want to repeat a page for each record in the XDF that matches the condition, which in this case if the 'expirydate' in XDF is = 1 then call the 'offer' page, which will be the same background for all matches, (so in effect duplicates but will contain variable data on each one taken from both DB1 and the XDF.

 

I've noticed that I did the wrong if --- if (result > 0 && result < 1). I should simply say if (result ==1) then call the page.

 

So for each record in DB1, it will check the XDF if the field data matches in both databases. If it does, it will then display that information ie. 'accounttype'. So in my example AccID 300 has 3 accounts in the XDF. It then runs the condition on 'expirydate' for each account that AccID 300 has. Then each one that fulfils that condition will add a page that has a new offer.

 

So in my example below, the result I'm aiming for is that there is always a 1 page PDF for all records in DB1 and if they have matchings records in the XDF that meet the condition as explained above, the result based on the data below should be, assuming current month is September (09):

 

AccID 100 - 2 pages (1 std + 1 offer)

AccID 200 - 1 page (1 std)

AccID 300 - 4 pages (1 std + 3 offers)

 

DB1

AccID

100

200

300

 

XDF (DB2)

AccID Expiry

100 30-10-2014

200 20-08-2015

300 30-10-2014

300 10-10-2014

300 15-10-2014

 

Hopefully that has clarified it?

Link to comment
Share on other sites

Hopefully that has clarified it?

Sort of. But what's your question? Are you asking which of those options I listed in my previous post to use? I still can't answer that without seeing the job, and exactly how the information is flowing to the output page. But I suspect you will get the best results with a repeatable component, flowing to an Overflow page. Search the FusionPro User Guide or this forum for "FPRepeatableComponent" for some examples.

Link to comment
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.
Note: Your post will require moderator approval before it will be visible.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...