Multiple Line Import on an Invoice Report
Posted: 20 Feb 2020, 15:56
Good afternoon,
Hopefully, this is the last query I have concerning a report that Bruce kindly helped me with a week or so ago.
The report is for a csv file to import to a courier app for International customs information. The part I am having a problem with is Expr17 on the attached copy of the layout.
Customs require what they cal PRD information individually for each item on the invoice
It must contain:
Standard Identifier = PRD
Product Code
Commodity Code
Unit Weight
Parcel No (ie which parcel the product is in, if multiples are being despatched - I have set the default to 1 as we never send more than 1 parcel overseas)
Product Description
Item of Origin
Qty
Unit Value
They also need it to be delimited
Expr17 currently looks like this - it works, but only creates it for the last product line on the invoice
INVOICE.NOTES_2 in ("Y","y")? "PRD" +"|"+ INVOICE_ITEM.STOCK_CODE +"|"+STOCK.INTRASTAT_COMM_CODE +"|"+STOCK.UNIT_WEIGHT +"|"+"1" +"|"+INVOICE_ITEM.DESCRIPTION +"|"+STOCK.COUNTRY_CODE_OF_ORIGIN +"|"+INVOICE_ITEM.QUANTITY +"|"+INVOICE_ITEM.NET_AMOUNT : ""
It works: Produces this in the csv
PRD|CAL-CERT|90259000|0|1|CAL CERT AT|GB|1|35
The problem is it only creates details for the last product line on the invoice
So my questions are- how do I get it to do it for all products on the invoice and is the above expression a load of tosh when it comes to making something delimited.
Your help is so very much appreciated.
Ann
Hopefully, this is the last query I have concerning a report that Bruce kindly helped me with a week or so ago.
The report is for a csv file to import to a courier app for International customs information. The part I am having a problem with is Expr17 on the attached copy of the layout.
Customs require what they cal PRD information individually for each item on the invoice
It must contain:
Standard Identifier = PRD
Product Code
Commodity Code
Unit Weight
Parcel No (ie which parcel the product is in, if multiples are being despatched - I have set the default to 1 as we never send more than 1 parcel overseas)
Product Description
Item of Origin
Qty
Unit Value
They also need it to be delimited
Expr17 currently looks like this - it works, but only creates it for the last product line on the invoice
INVOICE.NOTES_2 in ("Y","y")? "PRD" +"|"+ INVOICE_ITEM.STOCK_CODE +"|"+STOCK.INTRASTAT_COMM_CODE +"|"+STOCK.UNIT_WEIGHT +"|"+"1" +"|"+INVOICE_ITEM.DESCRIPTION +"|"+STOCK.COUNTRY_CODE_OF_ORIGIN +"|"+INVOICE_ITEM.QUANTITY +"|"+INVOICE_ITEM.NET_AMOUNT : ""
It works: Produces this in the csv
PRD|CAL-CERT|90259000|0|1|CAL CERT AT|GB|1|35
The problem is it only creates details for the last product line on the invoice
So my questions are- how do I get it to do it for all products on the invoice and is the above expression a load of tosh when it comes to making something delimited.
Your help is so very much appreciated.
Ann