DB Format issue

[blackbelt60]

Hello,

I entered this in FP Producer discussion this afternoon.

I prefer to enter here too, because of its meaning in VDP Creator. Also I did not explain very well there... I hope to do better here.

 

I usually had customers that provided me excel / csv files with titles in 1st row, and each row was an output record.

Now I have a new one who gave me the attached one. At a 1st look it does not look to be usable in FP Producer (VDP creator as well of course)

 

Now, depending on the “tipo” column (H or L), I have either Customer details / order summary (H), or article details (L) referring to the customer above.

Row numbers are variable for each customer.

They also have a “double” 1st line, depending whether “tipo” row is either H or L.

 

Of course same H - L group should be composed in the same output record: L referring rows one under the other, while customer / order details on several locations within the same page.

Customer will get a FP Producer, but this is a common question, valid for VDP Creator as well.

 

Should I drive customer to reassemble DB and have variable names on the 1st row, or is there a different way to identify variable names ?

Thanks Maurizio

dpc.zip

[blackbelt60]

Hello,

I tried to learn by user a simpler example, then apply later on mine...

I used an input file made like this:

 

ID;name;city;number

1;pippo;milano;2345643

1;giacomo;bari;345687474

2;pluto;novara;2334785

3;paperino;mantova;3429345

4;topolino;como;6361238

 

I need to get same ID row on the same page.

 

By looking at Rules Manual and some entries I found here around, I used the following onJobStart rule

 

Dir = new ExternalDataFile("C:\\Users\\Production Printig\\Documents\\Progetti_PTI\\Testrules\\DBMultipleRecord.csv", ";");

var contaRecord = Dir.recordCount;

 

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

{

if (Dir.GetFieldValue(recordWalker, 'ID') == Field("ID"))

{

Final = Final + Dir.GetFieldValue(recordWalker, "name") + " " +

Dir.GetFieldValue(recordWalker, "city") + " " +

Dir.GetFieldValue(recordWalker, "number") + "<br><br>";

}

}

return;

 

Output is empty... Any idea? Thanks Maurizio

[step]

I usually had customers that provided me excel / csv files with titles in 1st row, and each row was an output record.

Now I have a new one who gave me the attached one. At a 1st look it does not look to be usable in FP Producer (VDP creator as well of course)

You will probably save yourself a lot of headache if you remove that first header row from the Excel file if it's not being used.

 

Now, depending on the “tipo” column (H or L), I have either Customer details / order summary (H), or article details (L) referring to the customer above.

Row numbers are variable for each customer.

They also have a “double” 1st line, depending whether “tipo” row is either H or L.

 

Of course same H - L group should be composed in the same output record: L referring rows one under the other, while customer / order details on several locations within the same page.

Customer will get a FP Producer, but this is a common question, valid for VDP Creator as well.

I'm not really understanding what you're attempting to achieve through that description. Are you making a table for each record out of this data? And each table is comprised of a "H" row (header) and a variable number of "L" rows? In other words, is it the "H" in the "tipo" field that indicates it's time to start a new record or is that determined elsewhere in the data? A screenshot or posting your current template could help here.

 

By looking at Rules Manual and some entries I found here around, I used the following onJobStart rule

 

Dir = new ExternalDataFile("C:\\Users\\Production Printig\\Documents\\Progetti_PTI\\Testrules\\DBMultipleRecord.csv", ";");

var contaRecord = Dir.recordCount;

 

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

{

if (Dir.GetFieldValue(recordWalker, 'ID') == Field("ID"))

{

Final = Final + Dir.GetFieldValue(recordWalker, "name") + " " +

Dir.GetFieldValue(recordWalker, "city") + " " +

Dir.GetFieldValue(recordWalker, "number") + "<br><br>";

}

}

return;

 

Output is empty... Any idea? Thanks Maurizio

 

You can link to the data file from the OnJobStart Callback rule, but any output you are hoping to return needs to be done so elsewhere. So you're OnJobStart Rule should be just this:

Dir = new ExternalDataFile("C:\\Users\\Production Printig\\Documents\\Progetti_PTI\\Testrules\\DBMul tipleRecord.csv", ";");

Then you can create a text rule to return the content. On the subject of returning the content, you need to make sure you actually return the content. As your code stands, it's not currently returning anything. See my modifications in red:

var contaRecord = Dir.recordCount;

for (var recordWalker=1; recordWalker <= contaRecord; recordWalker++)
{
   if (Dir.GetFieldValue(recordWalker, 'ID') == Field("ID")) 
   {
       Final = Final + Dir.GetFieldValue(recordWalker, "name") + " " +
       Dir.GetFieldValue(recordWalker, "city") + " " +
       Dir.GetFieldValue(recordWalker, "number") + "<br><br>";
   }
}
return [color="Red"]Final[/color]; 

[blackbelt60]

Hello Ste,

There is something wrong with the "if" sentence. If I leave it like before, script never enters in it. It looks like the "Field" function is not allowed in that place.

 

See this:

 

var contaRecord = Dir.recordCount;

var Final = "";

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

{

if (Dir.GetFieldValue(recordWalker, "ID") == 2) //Field("ID")) //Dir.GetFieldValue (recordWalker, "ID"))

{

Final = Final + Dir.GetFieldValue(recordWalker, ("name")) + " " +

Dir.GetFieldValue(recordWalker, "city") + " " +

Dir.GetFieldValue(recordWalker, "number") + "<br><br>";

}

}

//return Dir.GetFieldValue(1, "name");

return Final;

 

By writing the script above I can return the 2nd row of my input file.

What I need is to have the first 2 ID rows on the 1st composed page , and for the remaining rows, each one on separate pages.

ID is the key field, in my example.

About the "more complex" Excel file, yes, I will ask to have 1 title line only, but I won't be able to get 1 record 1 row schema, so I have to deal with multiple records. As I am not an expert about javascript, I want start learning by using the simplex example I did above.

Thank you. Maurizio