External Data Question

[Susan]

I am linking to an external data file for a table. I wanted to use that external data to pick up just one field for a text box. Each Customer may have multiple entries in the data with the same USERACCOUNTNUMBER and USERID. I wanted to pick up the USERID, but if a customer has multiple entries the USERID repeats itself (USER ID: sr189sr189sr189). Can I tell it to just pick up the first one that it finds so that the result would be USER ID: sr189?

 

 

My rule is:

 

returnStr = '';

if(FusionPro.Composition.isPreview == true || FusionPro.inValidation == true)

{

Rule("OnJobStart");

}

 

numRecsExtDF = externalDF.recordCount;

 

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

{

if (externalDF.GetFieldValue(recordWalker, 'USERACCOUNTNUMBER') == Field("USERACCOUNTNUMBER"))

{

{

returnStr += externalDF.GetFieldValue(recordWalker, 'USERID');

 

}

}

}

 

 

return returnStr;

[step]

Sure, you can do that by inserting a "break" statement into your for loop (note that the brackets in orange aren't doing anything and aren't needed):

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

numRecsExtDF = externalDF.recordCount;

for (recordWalker=1; recordWalker <= numRecsExtDF; recordWalker++)
{
if (externalDF.GetFieldValue(recordWalker, 'USERACCOUNTNUMBER') == Field("USERACCOUNTNUMBER"))
{
	[color="DarkOrange"]{[/color]
	returnStr += externalDF.GetFieldValue(recordWalker, 'USERID');
	[color="Red"]break;[/color]
	[color="darkorange"]}[/color]
}
}


return returnStr;

 

Or, you could just return the line as soon as it's found (which is essentially the same thing):

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

numRecsExtDF = externalDF.recordCount;

for (recordWalker=1; recordWalker <= numRecsExtDF; recordWalker++)
{
if (externalDF.GetFieldValue(recordWalker, 'USERACCOUNTNUMBER') == Field("USERACCOUNTNUMBER"))
{
	[color="darkorange"]{[/color]
	[color="red"]return externalDF.GetFieldValue(recordWalker, 'USERID');[/color]
	[color="darkorange"]}[/color]
}
}


return returnStr;

 

But the whole thing could be simplified by using the "FindRecord" method of the "ExternalDataFileEx" object. Like this:

if(IsPreview() || FusionPro.inValidation)
Rule("OnJobStart");

return externalDF.GetFieldValue(externalDF.FindRecord('USERACCOUNTNUMBER', Field('USERACCOUNTNUMBER')), 'USERID') || '';

[Susan]

Thanks Ste!