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;