1

When filling a Word template through SQL on Exact Online the following error occurs:

Publication failed.

Context:

value-of expression

16Hjjhhhasdhfjhasjhfjha;jsfhsahfdahskj;dhkhsdkjhskjhkKashdhasdjhjsahdjhjsadJashdkaskjdjsakdkjhDocumentnrKlantnrOffertedatum<invantive:value-of expression="$F{qtk.quotationnumber}" ***/><invantive:foreach> <invantive:value-of expression="$F{qtk.orderaccountcode}" /></invantive:foreach><invantive:foreach> <invantive:value-of expression="$F{qtk.quotationdate}

The location of the error is indicated by the marker '***'.
Evaluation of expression "$F{qtk.quotationnumber}" failed.
Cannot find field with the name 'quotationnumber'. Check that you have inserted a surrounding repeating block and that the field exists in that block.

The underlying SQL of the block in Composition for Word is:

select qtk.quotationnumber
,      qtk.versionnumber
,      qtk.quotationdate
,      qtk.orderaccountcode
,      acc.name
,      acc.addressline1
,      acc.postcode
,      acc.city
,      acc.countryname
,      acc.phone
,      acc.fax
,      acc.vatnumber
,      itm.code
,      qtl.itemdescription
,      qtl.notes
,      qtl.quantity
,      qtl.netprice
,      qtl.amountdc
,      qtl.vatpercentage*100
from   exactonlinerest..Quotations Qtk
left 
outer 
join   exactonlinerest..Accounts Acc
on     acc.id = qtk.orderaccount
left 
outer 
join   exactonlinerest..QuotationLines Qtl
on     qtl.quotationid = qtk.quotationid
and    qtl.quotationnumber = $P{P_OFFERTE}
and    qtl.versionnumber = $P{P_VERSIE}
left 
outer 
join   exactonlinerest..Items Itm
on     itm.id = qtl.item
where  qtk.status = 40
and    qtk.quotationnumber = $P{P_OFFERTE} 
and    qtk.versionnumber = $P{P_VERSIE}

The SQL returns a list of quotations and their lines and items (articles) from Exact Online.

The content of the document is: Word template contents

I've tried various options, but the error keeps appearing. What am I doing wrong?

Guido Leenders
  • 4,232
  • 1
  • 23
  • 43
H Jansen
  • 319
  • 1
  • 8

1 Answers1

1

I guess you want to create a Word document with quotation header information, plus some quotation lines (just like traditional Order -> Order Lines master - detail table).

In this case I would first recommend splitting the query in two pieces:

  • One outer Composition block with query that retrieves the quotation information.
  • An inner Composition block with query that handles the quotation lines from Exact Online, including article information.

But that is not your current. In your Word template you have no repeating block specified. This is the area that should be repeated for each and every occurrence of a row in a block. You can easily insert a repeating block by clicking on Building Block in the Modeler menu. And then choosing the right block and then "Create repeating block".

As an alternative, you can also put

<invantive:foreach block="BLOCK-CODE">

before the text and other tags that you want to repeat in the resulting Word document including layout and pictures.

And put

</invantive:foreach>

after it.

The <invantive:foreach> that you already are not containing all tags. The <invantive:value-of expression="$F{qtk.quotationnumber}"/> is not contained. Also, the other foreaches are not at usable position. Better is to put the repeater outside the table.

Guido Leenders
  • 4,232
  • 1
  • 23
  • 43