Need more, need help now?
- - - - - - - - - - - - - - - - - - pay-as-you-go support - no contract - tenth of an hour billing - expert help - fast service - no call queues
Need integration?
- - - - - - - - - - - - - - - - - - with your shipping system - website - invoicing system - crm - cms - manufacturing - order import - back to back orders..
Need a report?
- - - - - - - - - - - - - - - - - - Excel reporting that pulls data from Sage - custom layouts - layouts that change adapt to your brands and/or for drop shipping.
Want web hosting?
- - - - - - - - - - - - - - - - - - Your own domain name - email - a shop - wordpress - woo commerce - ticket systems - help desks - forums - portals

Commercial Invoice

Report design issues and solutions.
Post Reply
Phones
User
User
Posts: 1
Joined: 25 Feb 2021, 13:16
Sage Version: Other

Commercial Invoice

Post by Phones » 25 Feb 2021, 13:30

Hi

Over the last 6 weeks I have compiled a list of data necessary to get shipments collected in europe, and brought across the border to the UK now we're post brexit. This has been an exhausting time with shipments being turned away repeatedly with "Commercial Invoice Missing xxxxxxxx" as the resason.

I've customised a normal invoice (picture attached), so that it will contain everything needed to get goods moving. At the moment I'm using a spreadsheet layout to tide me over, but this is time consuming.

I've attached a picture of my report adjustment so far, but just a couple of issues.

1) I need to get the customers email address onto the sheet. Shipping companies refuse to collect pallets without it being present.
2) I need the weight and the commodity code as you can see, which I have achieved, but when I create a text line using the Stock Code of "M" whatever I type is followed by those two lines. I want stock items to be followed by weight and commodity code, but not text lines.

Is there an easy solution?

Thanks.
Login to access the files attached to this post.

User avatar
brucedenney
Site Admin
Site Admin
Posts: 4619
Joined: 28 Feb 2006, 09:56
Sage Version: v28 UK/Europe/Africa

Re: Commercial Invoice

Post by brucedenney » 01 Mar 2021, 11:28

The email address is a field from the SALES_LEDGER table eg SALES_LEDGER.E_MAIL so not sure what the problem is

If you want commodity code and weight to only print on lines where there is a stock record, then you need to add another Invoice Item group section put those lines into that section and then use a filter on the section to supress it when not wanted. I would use a pair of filters, to supress the printing of the section if the commodity code is empty or if the delivery address is in the UK then your UK customers will not get a huge invoice with all the export extras on it.
For just about anything Sage :- Discount subscriptions, pay-as-you-go support, application integration, reports, layouts, linked excel spreadsheets, analysis or any other help making life with sage easier/less time consuming Contact me.

SomeGuy
User
User
Posts: 17
Joined: 20 Jul 2020, 06:14
Sage Version: Other

Re: Commercial Invoice

Post by SomeGuy » 04 Mar 2021, 06:58

An alternative approach for hiding the commodity code and weight would be to use conditional formatting on the expressions to suppress printing on them when the stock code is equal to "M"

User avatar
brucedenney
Site Admin
Site Admin
Posts: 4619
Joined: 28 Feb 2006, 09:56
Sage Version: v28 UK/Europe/Africa

Re: Commercial Invoice

Post by brucedenney » 04 Mar 2021, 10:41

The disadvantage of that approach is that the large whitespace will not be removed.
For just about anything Sage :- Discount subscriptions, pay-as-you-go support, application integration, reports, layouts, linked excel spreadsheets, analysis or any other help making life with sage easier/less time consuming Contact me.

SomeGuy
User
User
Posts: 17
Joined: 20 Jul 2020, 06:14
Sage Version: Other

Re: Commercial Invoice

Post by SomeGuy » 08 Mar 2021, 07:16

brucedenney wrote: 04 Mar 2021, 10:41 The disadvantage of that approach is that the large whitespace will not be removed.
True. You'd need to get creative with conditional formatting to stop that

User avatar
brucedenney
Site Admin
Site Admin
Posts: 4619
Joined: 28 Feb 2006, 09:56
Sage Version: v28 UK/Europe/Africa

Re: Commercial Invoice

Post by brucedenney » 08 Mar 2021, 13:00

Conditional formatting could never get rid of the whitespace.

To remove the whitespace you need a group section which you can filter out.
For just about anything Sage :- Discount subscriptions, pay-as-you-go support, application integration, reports, layouts, linked excel spreadsheets, analysis or any other help making life with sage easier/less time consuming Contact me.

SomeGuy
User
User
Posts: 17
Joined: 20 Jul 2020, 06:14
Sage Version: Other

Re: Commercial Invoice

Post by SomeGuy » 09 Mar 2021, 07:21

Could swear I have done this before but typically can't lay my hands on the layout I was thinking of now that I want it.
From what I recall it was something using conditional formatting to modify the auto grow and padding, and allowing sections to overlap. Don't recall the exact detail, and from a brief test with one of the out-of-the-box layouts, I can't easily recreate it. Probably not worth pursuing now but if I do find the layout I did this on I'll post here for info.

User avatar
brucedenney
Site Admin
Site Admin
Posts: 4619
Joined: 28 Feb 2006, 09:56
Sage Version: v28 UK/Europe/Africa

Re: Commercial Invoice

Post by brucedenney » 09 Mar 2021, 10:41

Auto grow would allow you to expand the section and add extra whitespace to accommodate the fields, it would not enable you to make the whitespace smaller.
For just about anything Sage :- Discount subscriptions, pay-as-you-go support, application integration, reports, layouts, linked excel spreadsheets, analysis or any other help making life with sage easier/less time consuming Contact me.

SomeGuy
User
User
Posts: 17
Joined: 20 Jul 2020, 06:14
Sage Version: Other

Re: Commercial Invoice

Post by SomeGuy » 10 Mar 2021, 06:59

brucedenney wrote: 09 Mar 2021, 10:41 Auto grow would allow you to expand the section and add extra whitespace to accommodate the fields
Exactly. The section was only a single line in height by default but through auto grow I had it expanding as needed. There was no whitespace to be removed. As I say I can't find the report I did this on and I'm not able to replicate it now so I don't think there is any value in pursuing this approach as the additional group approach does work.

Post Reply

Who is online

Users browsing this forum: No registered users and 13 guests