Defect Ratios - Part #2

In part 1 - you will notice that we use TEXT and not NUMBERS for the part number --that helps you keep LEADING ZERO's in part #'s - a tip that is handy

We found it easier to get TOTAL SALES from a 6-3 report and not add up every invoice here ...you use a similar method - but use your Ethernet line if you have one and go thru Acconnex --WAY FASTER ( 20 to 50 times faster )

The 6-3 report is a GREAT REPORT that we do every month to see where we are on select accounts (salesmen accounts and key other accounts )

I am sure I have written on how to take TWO DIFFERENT spreadsheets and take data from one into the other using something we thing is way better than VLOOKUP or HLOOKUP ( =INDEX( range , MATCH ( part #, range of part #'s, 0 ), x is the column you bring back ) --this is trickey at first - but very very handy

Use sub totals from your 6-9 report and get the values for the warranty returns by customer #.

NOW - great tip we found -- how do you get the SUB RTOTAL ONLY data .... use EDIT ... GO TO .... SPECIAL ..... VISIBLE CELLS ONLY ..... and copy the data. The PASTE SPECIAL for VALUES ONLY into a new spreadsheet. From here - import into your 6-3 report (or a clone of it ) and calculate defect ratio's

So - what steps might I have skipped - one is the reverse the negative sign ( older Excel programs like I use do not have it - so get the www.asap-utilities.com add on ( come on--make this for the MAC !! - so far only PC only )

There you have it --data that you can USE -- but with care & caution -- since habits take time to improve - and there might be valid reasons for specific warranty ratio's

Better data makes better decisions

how to calculate defect ratio's for customers

This is a bit complex - and assumes you have some good excel skills.

Master these skills -
1) get a laptop ( or regular PC ) and multiview PC software. Set up the 'capture screen data' option ( mentioned previously in this blog. You have to use a SERIAL line ( not the ethernet Acconnex fast line ) - so it will be slow for capture of the 6-9 data

Set up the laptop/PC as a PRINTER (call J-Con help desk) and have at the > prompt -=-do NOT sign into menu or the port will think it is BUSY. From another PC --send the 6-9 dump to the Laptop/PC ( which you are capturing the data).

If you get more than 500 pages you will need to open into a word doc and split into 500 page sections ( maybe you can go to 600 or 700 pages before you exceed the excel limit --since there is so much crap - extra lines and headers )

Import into Excel - click on the image to see the template setting to cut at what position ( found thru trial & error )

Now you have EVERY INVOIVE you have done in that time frame ---

Transaction code types
DR - Dept Return
FR - Fleet Return
IAP - Web-Link
IR - store return
IS - Store Use
RET - cust return
SR - Shop Return
SU - Store Use
SVC - ???
WHR - Warehouse Return
WR - Warranty Customer

You can sort and have fun ....... end of part # 1 --- see part # 2 on defect rations coming soon
WVR - Warehouse Warranty

Ont stores with enviro fees

Some J-Cons' didn't have an upgrade to the 8-15 control panel --and as such were not charging taxes on the new Ontario envior fees - this has now been cured for our stores -- how is yours?

List Price - more than you wanted to know

How to set LIST PRICE ...gee ...on the onset that would seem simple enough. Thanks to Adrian from CARQUEST Woodstock ON for the tips

First --look at 4-11 screen field #72 for the specific customer info

Then look at the 8-4 settings ... the Min setting is field #50 and the max setting is field #49. This 8-4 setting trumps the 4-11 setting. If you are multi-store --like we are -- check to see if you have all the stores set the same ( hint )

Hope this helps - if not - send feedback

multi store connection problems

Most multi stores connect thru very old technology using dial up or dedicated phone lines and multiplexers ( to join and re-split the various lines to terminals & printers.

Newer technology uses the Internet and boxes called jetstreams to do the job of multiplexers. We have several problems with jetstreams and to make matters worse - Activant doesn't want to stock a replacement in Canada - delaying our repairs by a day and a half when it clears customs.

We have recently found out that the routers used set up a VPN tunnel from the branch store to the main store - this has been a problem at times as well - and takes a systematic process to shut down and re-start to see if it solves the problem

Further -- we have found out that we can TELNET from the multi-view PC emulations (forget this if you use dumb green terminals) --and we are also looking at testing the linking of a serial printer to a host PC

This is all very brief-- it only applies to multi-stores with internet connections -- stay tuned ...

How to LINK an X line to a regular line

Have some sku's in rotors you buy from outside CARQUEST ?? Want to make sure your staff and more importantly your customers can see them when they look up parts on WEB-LINK ....

Make sure your regular part has a special DESCRIPTION .... ise '@XAB 1234 there the line is XAB or what ever and a 'space' and the part number.

See the images -- this is how it works
. Got a problem - if you have a line code with TWO digits ... you need to have TWO spaces then the part #

This ALSO links the parts in the counterman's e-cat screen in J-Con as well .... and at POS when you go to invoice.... a handy trick if you didn't know this.

Note --if you are OUT of the X line --it will NOIT show the link .... only if you have stock of the X line linked part

Merge data in excel

Check our sister blog

We have posted a first ( in a series ? ) of tips on how to take data from one excel sheet and match the data and import into the 2nd sheet ---say you wanted to know what your sales are in line ABC for your 4 stores -- you can use this to build a consolidated sheet ( there are other ways on the J-Con data as well ).

We use this to match USA jobber net cost to compare to Cdn Jobber net cost and the parts that match --we compare.

Click here to go to the Exploris Blog

Misc tips

From time to time I would write down a tip I thought worthwhile -- now - where did I put that piece of paper I wrote that on ...? TOO bad we don;t have a searchable data base for good idea's .... since I am an absolute FAN on WIKI's you know I have repeatedly asked the IT dept all the way to Joe Z for a WIKI on Exploris ...

anyways -- here are some tips I had written down over the years .... send a comment if you see something I have missed...

>VERIFY that End of Month ran well

We had an End of Month have a problem that we don't see until te next day. The resulting action of running the end of month during business hours SIGNIFICANTLY slowed down POS - to a point that many thought their terminal or printer was frozen - but it was just that CPU intensive

Do we want to start End Of Month early and see if it ran well?
-if so -- log in after an hour ( guess ) and look at 9-6 --- do for ALL stores separately ( if you are multi store ) ---if you get a status of 1 (one) it has yet to start that store --you want to see a status of 87 to know all is well ( do NOT change any status .... hint then you can check 8.3 and see if all are complete - this is the product and customer file rolls
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>

> killterm kbxx to kill a frozen terminal
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
** Break points for movement codes
35 25 15 10 6 4 3 2 = 100 for all 8 codes
used in 3-13 mvt code build
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
> force printing for more labels
8-15-2-9 option # 3 and make sure you go all the way thru to print

> clear Corupted Part Chain -- 8-15 3-2 ( this also removes duplicate parts - Re-Hash - run monthly ?? along with 3-14 re-sort -- needs to be run after hours but in a pinch on our fast system we can usually get it in live time

Note --we have a complete check list for end of month --send me an e-mail and I can forward copy in .xls or .pdf
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
>To change the time in the J-Con simply:
Exit menu
TYPE - asktime
ENTER - correct time in military style( i.e. 4:30PM = 1630) no dots or spaces should be entered
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
> Stock Level move-ups
2.19 - see what levels are set at by sub-line
3.9 change any stock levels required
11.2 (or 8.15 3* 21 for right away ) for tomorrow add missing sku's
3.5 forcast
2.7 report of changes
3.7 set changes ( or 3.3 or 3.18 manual set changes by part )
2.13 manual return ( this is not automatic on end of day
7.1 do order ( or wait for end of day
NOTE -- Greg's way with Excel is SO MUCH easier ( drop e-mail for more info )
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
>to check how full hard drive is
> sys/d
read the du0 line
>sys/d
Current disk status
Mount Dir Filesystem blocks used free %used
/ /dev/root 1000000 522848 477152 53%
/stand /dev/boot 40000 23068 16932 58%
/sds /dev/sds 1000000 108626 891374 11%
/du0 /dev/du0 32224684 5677832 26546852 18%
/cd0 /dev/cd0 322936 322936 0 100%
>
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>

> how to FIX a broken connection to Warehouse

if bad router daemon ( shown at top of screen )
shell out ( exit J-Con )
> restart_comm (cr)
wait a minute for '>' to come back
type reg then (cr) - do 5 times ( to be sure - you are logging back in to aconnextx server )

Now you should be good to go and can order from the warehouse or superstore ... and your FAST-LINK customers can now order again
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>

J-Con Tricks #2
> how to FORCE a update to warehouse

8-15 #10 - # 8 to send request file NOW
#4 for CCInet
submit a file for transfer ( 1st option )
poll a node
DSID # -- is TOR001 for our warehouse
Type: is POLL

hit F-10 to send file
hit F-9 to back out

Reprint more price labels in J-Con
say you want to re-do price labels for a line ...
8-15 menu
- 2 ( inventory)
- 9 price labels
- 3 label utilities for shelf labels (do not use 1,2 or 4)
- enter defaults ( enter = default ) until you get to the name of the line you want
- enter one or more lines
- enter = screen
then end when you are done
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
DISPLAY LOCATION
999 = price labels

100 = price sensitive items we need to review ( test 4-07 )
we set special items that are very price sensetive at 100 so we can sort in Excel and review
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
Function keys - J-Con
got your won terminal emulator program you want to try ( good luck ) - here are the function key formulas
F1 esc OP
F2 esc OQ
F3 esc OR
F4 esc OS
F5 esc OT
F6 esc OU
F7 esc OV
F8 esc OW
F9 esc OX
F10 esc OY
F11 esc OZ
F12 [24~

getting it from J-Con to Excel

OK -- so you got Multi-View installed and working on a PC
-- and you have the red and black record buttons showing
-- and you have set the column width to 132 ... lets go get some data

Try an easy report to record ? Say a 2-19 or a 6-4 report? Anything as a test.

Save the report on your PC where you will remember to find it,

Open Excel -- hey -- you can't find it ... change excel setting to open ANY file type --and now you file shows up. See Image#1

Excel says --what do I do with this ?? You have two options -- open with some de-limiters ( can be a comma or a tab or ? ) or open as fixed width and you will cut into columns. One way we handle new reports is to open as delimited - using the default tab as delimiter ... and the J-Con will open as the report with no action ( as you have no embedded tabs in the report) See Image #2

How does your report look? Image #3 shows the report in a 'normal' font - like Arial -- but it looks sloppy - nothing lines up

Change your font to a 'non-proportional' font like COURIER and all of a sudden the report lines up. Image #4

Remember we didn't chop up the report when we first opened it -- now we can using "DATA" then "Text to columns" in Excel See Image #5

Now it is in Excel - next post will give us some more tips on what you can do

Version 65

A tidbit of info - most of us are on some version of v60. There is a v65 that has several nice features. There where also some big problems --although they are probably handled -as a couple of CARQUEST jobbers in Quebec are running v65.

I have some doc's on what v65 does -- will trade for beer

ASAP utilities

One side trip we need to take is get a copy of ASAP utilities

asap-utilities.com

This is a free (for now anyways ) and invaluable help !! If they charge --PAY --it will be worth the money. If you are using an older version of Excel like many of us are - you have to do some digging on their web site to find the older version to match.

WHY is it worth it's weight in gold?
1) it will take negative numbers that show on the wrong side of the number and correct ... do it manually once and you will understand
2) a more advanced use - on reports like 6-4 sales by line --it will copy the line code to the lines below ( neat!! )

Another point -- we are guessing that you have some Excel training - if you are weak in Excel-- you will have to work at understanding the power Excel has in helping you run your business ...go make some sales charts or anything that will get you to track your business results in excel so you see where you are going.

step 2

The hard part is over -- getting Multi-view ( or finding where you put your copy ) and getting it installed on the PC's -- getting a serial line and a serial adaptor -- getting the right pin in the right slot -- and any changes on the serial side -- are you at 9,600 or 19,200 baud rate - the whole concept of setting up the defaults is tough --find an older J-Con person on the help desk and treasure that knowledge ..... so it works ( or on the 2 or 5th time ) .... And with the local GPI computer guys so busy with new Exploris installs -it isn't like they have much time for us... :-(

Now - lets take a report and save and open in Excel ( there are other potions like www.openoffice.org and google with FREE equivelent items to excel - we use the Older version 97 in excel - so you don't need anything brand new )

You have played with the screen buffer ( so you can copy small patches of text and paste directly ) - but lets copy a report ...

Steps .... 1) the normal width for J-Con is 80 characters wide and for a report is 132 wide ...see a looming problem ... the last thing you want is the report 'wrapped' into a 2nd line ... so ... you will learn to change the settings ( or be like me and leave it at 132 ) -- do this - under configure - terminal - terminal type > configure -- set the normal to 132 wide
2) enter a report to run - pick a small one to start - like a 2-19 for FRI - before you get to the press ENTER to start - hit the red record button - then run the report --and hit the black stop record button - and give you test report a name
3) open excel - remember where you saved the test report - and try to open it -- you won't see it ...damm ... now change FILE TYPE to ALL FILES and your log file will show
4) excel gives you two options - de-limited and fixed width - you will usually used fixed width - but for this one - pick delimited -- nothing will really happen --as J-Con doesn't embed any de-limiter characters ---so what you will see is a full virgin copy of what you copied ...but what do we do with it?
5) if your report opens and it looks ugly -- and it will look ugly -- you need to understand non-proportional fonts - change to COURIER font and suddenly the report shuffles into focus
6) how do you cut the report into strips -- you can use the DATA - text to columns option in Excel to cut into strips - play with it and once you start using a report on a regular basis - you can REMEMBER where to cut on a cheat sheet

Life in the old horse yet?

One of the KEY things you can do to get more left from J-Con is to learn to dump data from J-Con and into Excel

Steps -- every PC can run a terminal emulation program to 'pretend' it is a dumb green screen. Activant uses some hard to copy function keys - so we seem to have only a couple of options -the most popular is Multi-Term available thru J-Con support.

There are many options to make Multi-View more powerful
- turn on the CAPTURE buttons - see the image -the red button circled will start recording everything that Multi-View sees on the screen and the dimmed square BLACK button will allow you to select the place and name the file. These are saved as .LOG files than can be opened by Excel (more detail in a further post)
- the scroll back option is also handy for grabbing something short - in the image we show the setting at 499 lines and you will know you have this option on - as a set of 'elevator controls' will show on the right hand side of the terminal emulator -allowing you to go back ( up ) or forward (down ). You can drag and copy like most other windows programs

Getting Multi-View - if you don;t already one a copy is the SINGLE MOST important part of many future tips to be shown here = are you ready for the journey?

first post

Welcome to J-Con Tips & Tricks. There is still life in the OLD HORSE. While we are awaiting the change to EXPLORIS -- we can learn from one another on tips and tricks we have gained to help us do our jobs better.

If you have a tip or trick to share-- e-mail to info@NO_SPAMdtauto.ca ( of course - remove the NO_SPAM part ) and we will give you more than ample credit for your kind act of sharing info.

If you have interest and would like to have posting privledges -- e-mail me at above and tell me about yourself.