MIB Posted September 5, 2014 Share Posted September 5, 2014 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! Quote Link to comment Share on other sites More sharing options...
Murrayc ARCAS Posted September 5, 2014 Share Posted September 5, 2014 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; Quote Link to comment Share on other sites More sharing options...
MIB Posted September 17, 2014 Author Share Posted September 17, 2014 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. Quote Link to comment Share on other sites More sharing options...
Dan Korn Posted September 17, 2014 Share Posted September 17, 2014 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. Quote Link to comment Share on other sites More sharing options...
MIB Posted September 30, 2014 Author Share Posted September 30, 2014 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; Quote Link to comment Share on other sites More sharing options...
MIB Posted October 1, 2014 Author Share Posted October 1, 2014 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! Quote Link to comment Share on other sites More sharing options...
Dan Korn Posted October 2, 2014 Share Posted October 2, 2014 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. Quote Link to comment Share on other sites More sharing options...
MIB Posted October 2, 2014 Author Share Posted October 2, 2014 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? Quote Link to comment Share on other sites More sharing options...
Dan Korn Posted October 2, 2014 Share Posted October 2, 2014 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. Quote Link to comment Share on other sites More sharing options...
Recommended Posts
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.