Pip308
Member
Registered: 25th Oct 07
Location: Basingstoke Drives: Audi A4 Avant, Mk1 Caddy
User status: Offline
|
At my work we have a delivery diary - its a A4 piece of paper with the date going down the side and the deliveries next to it in pencil...
I'd like to bring us up to date with some sort of electronic delivery diary, but I don't have a clue where to start.
I need the date, customer, what their load (delivery) is, their address and possibly a cell for if they've paid or not.
Is all this possible and easy, would like it as clean as possible.
We can do a maximum of two deliveries a day as its just one driver and one lorry so if I could make it so I can easily put a post code and it say "cant do that" due to time limits/distance. if you know what I mean?
Phil
|
A2H GO
Member
Registered: 14th Sep 04
Location: Stoke
User status: Offline
|
I'm guessing this is not as simple as:
|
Pip308
Member
Registered: 25th Oct 07
Location: Basingstoke Drives: Audi A4 Avant, Mk1 Caddy
User status: Offline
|
not really, thanks tho, i've searched google etc but cant find what i'm looking for
|
Budgie
Member
Registered: 2nd Dec 09
Location: Basingstoke
User status: Offline
|
Are the deliveries on a pdf or separate file? If so I'd hyperlink it instead of filling out a cell.
Can you not use google maps and just fill a cell out with the time of how long it will take to get there and back?
Are they repeat deliveries btw?
edit:
something like this?
[Edited on 21-02-2013 by Budgie]
|
Dom
Member
Registered: 13th Sep 03
User status: Offline
|
The postcode bit sounds overly messy for Excel and unless you want to do the tracking manually (essentially it'd be a digital paper copy of what you do now) then you'd be better off getting in some decent logistics software.
|
evilrob
Premium Member
Registered: 16th Mar 12
Location: Your mum's house
User status: Offline
|
Postcode Anywhere have a ready-built solution for distances/directions, but you will get charged up to 16.7p per check depending on how much credit you buy upfront:
http://www.postcodeanywhere.co.uk/route-planner-services/how-to-use/
Or you could naughtily use Google Maps API via a macro to get the distances between postcodes.
|
Pip308
Member
Registered: 25th Oct 07
Location: Basingstoke Drives: Audi A4 Avant, Mk1 Caddy
User status: Offline
|
The problem with postcode/direction/times on google maps or other is that lorries take longer and driver must take a break after certian amount of time
|
evilrob
Premium Member
Registered: 16th Mar 12
Location: Your mum's house
User status: Offline
|
How do you currently estimate how long a delivery will take?
Is it as simple as:
GoogleMapsTime + n% lorry goes a bit slower factor
+
If (GoogleMapsTime + n% lorry goes a bit slower factor) is greater than x minutes then add y minutes break
|
Dom
Member
Registered: 13th Sep 03
User status: Offline
|
quote:
Originally posted by Pip308
The problem with postcode/direction/times on google maps or other is that lorries take longer and driver must take a break after certian amount of time
Postcode Anywhere does allow you to create vehicle profiles (sure it did the last time i looked at the API) that it can reference when getting you distances/time/directions etc But even then it's far from a walk in a park to 'throw' something together.
Either way, it sounds like you want a logistic package and it's not something you're going to knock up in Excel over a lunch-break. Better of trialling a few packages, get some prices and attempt to get your MD to sign off on it.
|
evilrob
Premium Member
Registered: 16th Mar 12
Location: Your mum's house
User status: Offline
|
As I see it you have the following variables:
DP = Depot Postcode
D1P = Delivery 1 Postcode
D2P = Delivery 2 Postcode
LF = Lorry factor (e.g. 150% - i.e. you're making the assumption it will take 50% longer again than a regular car journey)
RTMIL = Round trip miles
RTMIN = Round trip minutes
MMIL = Maximum number of miles a driver can travel before they are required to take a break
BRMIL = Break required when maximum miles reached (e.g. 30 minutes)
MMIN = Maximum number of minutes a driver can drive for before they are required to take a break
BRMIN = Break required when maximum minutes reached (e.g. 15 minutes)
From this you would calculate your round trip of DP -> D1P -> D2P -> DP by generating a Google Maps URL and parsing the returned XML.
e.g. RTMIL = 120 miles, RTMIN = 240 minutes
However, let's say MMIL = 50, so we would calculate how many whole MMIL's go into RTMIL; in this case it would be two, so we would add 2 x BRMIL = 60 minutes
And MMIN = 100, so we need to apply 2 x BRMIN as well = 30 minutes
So your total estimated time would be:
RTMIN * LF +
(ROUND(RTMIL/MMIL,0) x BRMIL) +
(ROUND(RTMIN/MMIN,0) x BRMIN)
240 * 1.5 + 60 + 30 = 450 minutes
If you're happy with that logic, I can upload something in about half an hour.
[Edited on 21-02-2013 by evilrob]
|
evilrob
Premium Member
Registered: 16th Mar 12
Location: Your mum's house
User status: Offline
|
Your next issue will be that your estimated mileage won't take into account having to take a different route because of low bridges or weight restricted road etc.
I'd be inclined to agree with Dom on getting a logistics package, but it's an awful lot of expense for 2 deliveries a day!
I have some experience in fleet management - and it aint cheap!
|
Dom
Member
Registered: 13th Sep 03
User status: Offline
|
quote:
Originally posted by evilrob
Your next issue will be that your estimated mileage won't take into account having to take a different route because of low bridges or weight restricted road etc.
As said, Postcode Anywhere could sort that as the API (just had a look now; function 'DistancesAndDirections/Interactive/FreightDirectionsAndLines') takes into account weight/height/length/width of the vehicle. They also do route optimisation which is something a lot of the bigger logistic packages will do.
Still not something you're going to knock up unless you're handy in a programming language (still going to need to know VB/VBA to some degree to get it going in Excel).
|
evilrob
Premium Member
Registered: 16th Mar 12
Location: Your mum's house
User status: Offline
|
quote:
Originally posted by Dom
quote:
Originally posted by evilrob
Your next issue will be that your estimated mileage won't take into account having to take a different route because of low bridges or weight restricted road etc.
As said, Postcode Anywhere could sort that as the API (just had a look now; function 'DistancesAndDirections/Interactive/FreightDirectionsAndLines') takes into account weight/height/length/width of the vehicle. They also do route optimisation which is something a lot of the bigger logistic packages will do.
Still not something you're going to knock up unless you're handy in a programming language (still going to need to know VB/VBA to some degree to get it going in Excel).
Yes, I know that, Dom - it was me that suggested Postcode Anywhere in the first place. 
I'm offering to build a solution based on Google Maps on the understanding it can't do freight routes but won't cost 16p per query.
|
Dom
Member
Registered: 13th Sep 03
User status: Offline
|
quote:
Originally posted by evilrob
quote:
Originally posted by Dom
quote:
Originally posted by evilrob
Your next issue will be that your estimated mileage won't take into account having to take a different route because of low bridges or weight restricted road etc.
As said, Postcode Anywhere could sort that as the API (just had a look now; function 'DistancesAndDirections/Interactive/FreightDirectionsAndLines') takes into account weight/height/length/width of the vehicle. They also do route optimisation which is something a lot of the bigger logistic packages will do.
Still not something you're going to knock up unless you're handy in a programming language (still going to need to know VB/VBA to some degree to get it going in Excel).
Yes, I know that, Dom - it was me that suggested Postcode Anywhere in the first place.
I'm offering to build a solution based on Google Maps on the understanding it can't do freight routes but won't cost 16p per query.
Was more of an informative reply to Pip rather than you.
Although im not sure how you'd implement Google Maps API into Excel as it requires the map to be shown (also part of their licensing that you must show the map if using the 'free' version).
|
Ian
Site Administrator
Registered: 28th Aug 99
Location: Liverpool
User status: Offline
|
Wouldn't even use the API, just open another window and plan a route, then put the distance in to the spreadsheet.
Automating it is a massive job and not all that good an approach anyway as highlighted, the driver will probably not stick to the route you come up with, so your measurements are rough anyway.
Also that figure of two deliveries may be subject to change, I'd read up on the tacho rules and work backwards from there. The combined distances, driving time and speed of the truck are what dictates the number of deliveries you can do, not the other way around.
|
evilrob
Premium Member
Registered: 16th Mar 12
Location: Your mum's house
User status: Offline
|
quote:
Originally posted by Dom
im not sure how you'd implement Google Maps API into Excel as it requires the map to be shown (also part of their licensing that you must show the map if using the 'free' version).
quote:
Originally posted by evilrob
Or you could naughtily use Google Maps API via a macro to get the distances between postcodes.
By blatantly breaking the license terms... or by showing a map in a web browser control. For the volumes the OP is doing, Google won't give a flying fuck either way.
|
evilrob
Premium Member
Registered: 16th Mar 12
Location: Your mum's house
User status: Offline
|
quote:
Originally posted by Ian
Wouldn't even use the API, just open another window and plan a route, then put the distance in to the spreadsheet.
Automating it is a massive job and not all that good an approach anyway as highlighted, the driver will probably not stick to the route you come up with, so your measurements are rough anyway.
Also that figure of two deliveries may be subject to change, I'd read up on the tacho rules and work backwards from there. The combined distances, driving time and speed of the truck are what dictates the number of deliveries you can do, not the other way around.
As per discussion the other day, you know I can automate IE from Excel; Google isn't going to know whether or not that IE instance is visible.
All I was trying to do was help out with the OP's requirements; I agree it's not a good way of doing it, but it does seem like it's a 'back of a fag packet' setup anyway at the moment - was just offering to mash up a spreadsheet equivalent of the existing paper system, with known limitations, but free.
There are any number of better, paid-for, solutions - but as you say they're complex, and they're also expensive to buy or implement.
On the basis that their process is exactly as described, a wonky spreadsheet will for the most part 'do the job' for up to two deliveries a day.
[Edited on 21-02-2013 by evilrob]
|
evilrob
Premium Member
Registered: 16th Mar 12
Location: Your mum's house
User status: Offline
|
I once wrote some software for the PHS Group Clinical Waste division with similar requirements (though more of a 'travelling salesman' problem) - they had about 80 depots across the UK, a number of wagons (I forget how many) and thousands of lifts a month.
Back then, before we had Google Maps, we used MapPoint. 
It doesn't matter what route you give a driver; they're going to do whatever they want anyway.
[Edited on 21-02-2013 by evilrob]
|
Ian
Site Administrator
Registered: 28th Aug 99
Location: Liverpool
User status: Offline
|
The API does optimise the way points, I've had a bit of a play for some other software I was thinking about.
In fact, I might run that by you at some point, I got nowhere with it because alongside the Google API and another one that integrates in to it, I couldn't get something resembling a good UI.
|
evilrob
Premium Member
Registered: 16th Mar 12
Location: Your mum's house
User status: Offline
|
Had a spare moment this evening so whipped this up:
https://www.youtube.com/watch?v=tH7hjPgsjuY
In the video I am manually doing things in Google Maps alongside the spreadsheet only to verify/demonstrate it is working.
In this example I have 4 deliveries that need to be made and I want to find out which two I can do in a day.
There is a parameters worksheet where you can specify the 'slow lorry factor', maximum number of minutes a driver can do in a day, the depot postcode, and set criteria for when breaks should be taken and for how long.
To find out the round trip for one delivery (i.e. depot -> delivery address -> return to depot), you set column G to TRUE for the appropriate row. Column J will return the miles from Google Maps, Column K will return an adjusted number of minutes including 'slow lorry factor' and any required breaks.
If you want to see the miles / minutes for a second delivery on the round, you put the delivery ID of the second delivery in column H.
Column M will return TRUE or FALSE depending on whether the total minutes required to do the round is less than the maximum number of minutes a driver can do as set in the Parameters worksheet. 
edit: edited due to incorrect column specified above.
[Edited on 21-02-2013 by evilrob]
|
dannymccann
Member
Registered: 9th Aug 06
Location: Doddington, Lincolnshire
User status: Offline
|
I have nothing to add to this discussion apart from to ask Rob, can you send me that spreadsheet so I can see how you've done it, I'm a bit of a geek when it comes to spreadsheets but am still a novice
|
evilrob
Premium Member
Registered: 16th Mar 12
Location: Your mum's house
User status: Offline
|
No probs - what operating system / version of Office are you using? (so I can send you an appropriate version that doesn't have any wanky conversion bollocks when you open it)
|
Dom
Member
Registered: 13th Sep 03
User status: Offline
|
Rob, you clearly love Excel
Is that scraping Google Maps for the mileage then or are you directly linking to the API?
|
evilrob
Premium Member
Registered: 16th Mar 12
Location: Your mum's house
User status: Offline
|
It comes from years of working for companies who want stuff automated but don't want to pay for the appropriate software or infrastructure to do it properly.
"We've got Excel - can't you do something with that?"
This one is calling the API, but I could just as easily scrape it. Path of least resistance for proof of concept was to use the API cos then you get lovely XML back.
If this does what the OP wants and they're worried the Google feds are going to come knocking for breaking the rules, I can scrape the results instead.
|
Ian
Site Administrator
Registered: 28th Aug 99
Location: Liverpool
User status: Offline
|
I should install 7 at some point, just can't get a liking for that task bar.
|
