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

Sales Order Report mapped to transport portal import templae

Report design issues and solutions.
gazmoz17
User
User
Posts: 108
Joined: 19 Sep 2018, 13:58
Sage Version: v2018 24 UK/Europe/Africa

Sales Order Report mapped to transport portal import templae

Post by gazmoz17 » 07 May 2021, 08:27

Hi Bruce, hope your well.

It's taking us too long to key transport jobs on our transport portal plus its just a boring repetition keying sales order details again!

Been given the transport import csv template and attempted to make a sage sales order report to mirror (by adjusting current report) but having a few probs.

Probs I think are I'm clueless with sections and interrelationship between them...think sage literature is poor for this :(

Having probs:
  • Removing a gap in my report Excel cells V:AC dont want a gap for first line of each new sales order/transport job. If that sales order has haz items then first line should contain first haz details item line. If non haz job (none on excel e.g.) then V:AC blank then very next row down is next sales order/transport job
My report export eg.xlsx
Sage field mapping to eg template.xlsx
clarkes import 2.report
  • Also want the report to run only on sales orders highlighted in sales order module. Aware there's a current filter on this report which runs sales order not complete. But when took this filter out so filters blank it runs for all historical sales order. Took sage advice changing criteria but still runs for all historical. Also tried to mirror a sales invoice report which only runs highlighted but no diff.
Any help much appreciated.

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

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

Re: Sales Order Report mapped to transport portal import templae

Post by brucedenney » 07 May 2021, 10:24

You need to put it all in one section, looks like you need one section for each web category 1 (Custom1)
so move the fields in the header section into the web category section you should then get the header info repeating on the left and every line will be full.

I had a go but, I don't have suitable data to test, you can try it, but I do not have any guarantees.
clarkes import 2 BD.report
The other way to do this would be to have an Excel sheet linked to sage through odbc which you refresh then save as a csv file for the other system, the benefit of that is that you could have it run on a schedule using scheduled tasks, if the other system can be scheduled to import then you could have it run in background and update itself every so often.
Login to access the files attached to this post.
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.

gazmoz17
User
User
Posts: 108
Joined: 19 Sep 2018, 13:58
Sage Version: v2018 24 UK/Europe/Africa

Re: Sales Order Report mapped to transport portal import templae

Post by gazmoz17 » 07 May 2021, 11:41

Hi,

Thanks for the quick response Bruce :D .

Had a "misunderstanding" with transport company yesterday. I just semi mapped the csv file manually and sent it them expecting upload in their portal within 15mins per there promise. But then was told an hour later of a "misunderstanding" and requires development time there end!

....so keen to get the file done today and sent off, so its starting there development time.

Once its sent and they confirm whatever they need to do there end.....I definitely want to explore the odbc option, also for an invoice profit report if could be used for that.

Thanks for the attached layout but it needs to be 1 line per sales order if doesn't have haz items. If has haz items the 1st haz item needs to be on line 1 with with the main sales orders details A:U. Then haz items 2 onwards (if has more haz items) on seperate lines (V:AC) with only col G "Order Ref" needed as main sales order details (before Col V).

Thanks
Gareth

gazmoz17
User
User
Posts: 108
Joined: 19 Sep 2018, 13:58
Sage Version: v2018 24 UK/Europe/Africa

Re: Sales Order Report mapped to transport portal import templae

Post by gazmoz17 » 07 May 2021, 11:42

Sorry forgot to add the file
Mapping.xlsx
Many Thanks
Login to access the files attached to this post.

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

Re: Sales Order Report mapped to transport portal import templae

Post by brucedenney » 07 May 2021, 13:11

What happens when there are multiple different UN Codes on an order, do you want to consolidate the whole order onto one line?
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.

gazmoz17
User
User
Posts: 108
Joined: 19 Sep 2018, 13:58
Sage Version: v2018 24 UK/Europe/Africa

Re: Sales Order Report mapped to transport portal import templae

Post by gazmoz17 » 07 May 2021, 15:34

Hi, no as is on my report please exactly (as that mirrors there template they've asked for) the only diff being the first haz item line is on line 1 of that sales order so there's no gaps (orange highlighted)
Remove gap.JPG
Once that gap is removed some how it mirrors there template exactly.

So when multiple haz items on one order it works correctly shows them as indiv lines with the order ref showing in column G all other cols blank then (....other than indiv listed haz items and ref number in col G).

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

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

Re: Sales Order Report mapped to transport portal import templae

Post by brucedenney » 10 May 2021, 11:36

Please can you post the link from the other party that defines the file format. I think they have miscommunicated something, the norm is to repeat header lines in CSV format, I would expect it to work with everything on one line as I did on my sample.

If they really want them supressing it could be done, but it is extra work and it don't think they really do need them supressing.
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.

gazmoz17
User
User
Posts: 108
Joined: 19 Sep 2018, 13:58
Sage Version: v2018 24 UK/Europe/Africa

Re: Sales Order Report mapped to transport portal import templae

Post by gazmoz17 » 18 May 2021, 09:59

Good morning Bruce,

Double apology!
1. For not making time getting back to you last week (was off yesterday), been a bit made with price increases lately.
2. The report you sent me was very close I just think I maybe saved it down and ran the wrong report or with incorrect sample data.

Heres the requested orig template:
FileSpecFeb20Email.xls
ExampleFeb20Email.csv
But I only want the report to run on highlighted sales orders in the sales order module irrespective of their despatch status.

I have an invoice report which only runs highlighted invoices but I cant seem to edit this report to do the same?

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

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

Re: Sales Order Report mapped to transport portal import templae

Post by brucedenney » 18 May 2021, 10:12

So the report is good. The only change you need to make is to have the implicit selection of orders when run?
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.

gazmoz17
User
User
Posts: 108
Joined: 19 Sep 2018, 13:58
Sage Version: v2018 24 UK/Europe/Africa

Re: Sales Order Report mapped to transport portal import templae

Post by gazmoz17 » 18 May 2021, 11:13

Hi,

Well I'm not sure if Ive misread there template and example Ive attached.

But how Ive read it if the order contains haz items than these need to be below the first line (1st line containing core info to that order address details etc)

So I think this is now correct
Clarkes now.report
other than 2 things:

1. How to only run from whats highlighted in sales order module...implicit selection.
2. Getting rid of the gap on first line if its haz instead of dropping to the next.

I know what your saying on your earlier reply about csv lines usually repeat themselves but Ive read there template that the entire lines don't repeat if there haz. But I'll check with them now.

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

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

Re: Sales Order Report mapped to transport portal import templae

Post by brucedenney » 18 May 2021, 12:01

What is the Web Category Header all about?

You seem to have skipped a lot of the fields in your layout there are 29 fields in the spec there are 42 have you missed some?

I think you want one just line per order.

That line is going to need to be an order footer, because we will need to add up the weights of all the items on the order we are also going to need to inspect each item to see if it is "Hazardous" if the order has "Hazardous" items then some of the values need to change.

So you move it all into an order footer.

In the details section create a field called "Haz" if the item we are looking at id hazardous set the value to 1 if it is not set it to 0
In the footer section add up all the Haz if it adds up to 0 then there ar eno hazardous items, if it is more than 0 then it has hazardous items, use this logic to set the value sin the hazardous fields.

----------------------------------------------------------

For the implicit selection of items highlighted you need to go to Report>Criteria and Edit the OrderNumber Criteria
Screenshot How to autoselect items.png
Change the Criterion Name to ORDER_NUMBER and it will work.

If you need to do this in future and it is a different module or you forget, just grab a report that does work, edit is and look at the value for the primary Key STOCK_CODE,ACCOUNT_REF etc depending on the module and that will give you the name you need to use.
Login to access the files attached to this post.
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.

gazmoz17
User
User
Posts: 108
Joined: 19 Sep 2018, 13:58
Sage Version: v2018 24 UK/Europe/Africa

Re: Sales Order Report mapped to transport portal import templae

Post by gazmoz17 » 18 May 2021, 13:35

Hi,

Thanks for the Implicit bit works a treat now for highlighted selection...just couldnt work it out from my inv report!

Not all fields present?

Correct I'm not too sure if Sage can pull through some of the fields think might be too complicated to do.

Green fields I think I can return. 6. Hazardous with your recommendation below returning a total of more than 0 flag as Haz)

Orange fields dont know if can return correctly as not just weight that constitutes diff pallet sizes. Plus we sometime fudge the deliveries putting them on as number of items pro-rated at item rate instead of jumping up to quarter pallet rate.

So some of the orange fields we might just have to key once exported as csv amend and then forward to transport company. But export overall would still be a huge time saver.

The reds I need a bit more info from transport company.

*32.No of items will definitely have to key as not weight related just what random ancills we can fit in what type of box etc.
Fields.jpg
Thanks
Login to access the files attached to this post.

gazmoz17
User
User
Posts: 108
Joined: 19 Sep 2018, 13:58
Sage Version: v2018 24 UK/Europe/Africa

Re: Sales Order Report mapped to transport portal import templae

Post by gazmoz17 » 18 May 2021, 13:37

What is the Web Category Header all about?

I'm useless adding sections (need to revisit the help literature) think the sage material isnt great so just sort of fudge it to get as close to the transport template as I could!

gazmoz17
User
User
Posts: 108
Joined: 19 Sep 2018, 13:58
Sage Version: v2018 24 UK/Europe/Africa

Re: Sales Order Report mapped to transport portal import templae

Post by gazmoz17 » 18 May 2021, 13:44

" I think you want one just line per order."

Waiting for them to clarify, but per what theyve sent Ive read it as single line per order is fine...only if none haz.

If haz require indiv haz lines with repetition of order number only.

Thanks

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

Re: Sales Order Report mapped to transport portal import templae

Post by brucedenney » 19 May 2021, 11:30

So where an order has hazardous items they want it listing twice, three times, once for the non hazardous and then multiple times for each of the hazardous items?

My expectation is they want each order listed once.
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.

Post Reply

Who is online

Users browsing this forum: brucedenney and 9 guests