Transmit sales data to home office

[Dale Harris]

OK, who wants a programming project.

For those of you who do not program, read on because I will be asking you for comments on what information the program should transmit.

In today's Chicago Tribune business section there was an article about the demise of Archibald Candy Company, owners of the Fannie May and Fannie Farmer candy brands, and while there were many causes for their liquidation, one mentioned was lack of a POS system. In fact, they were using electric (not electronic) cash registers that were 30 years old and only tabulated the sales amount with no merchandise information.

This meant that all ordering for restocking had to be done by hand and that there were no computer files of past sales histories or current trends to project needed production of product.

In an amazing coincidence, in my real life I am a locksmith and one of my former associates was a area manager for Fannie May. She asked me to rekey several Fannie May stores and while I was doing so I noticed the outdated cash registers that they were using. I mentioned my POS software and gave her a demo on a laptop I had in the car. I told her that it would work on all PC type computers no matter how old and that Fannie May could probably pick up computers that would run the software for practically nothing. In fact, since the software was free, probably the largest expense for each store would be buying the receipt printers and cash drawers. I gave her a copy of the program on a floppy and she said that she would show her boss.

I never heard back from her and now her company is history.

So why am I telling you this? On thing my former associate asked was, "Will your program transmit its information to the home office?" I had to admit that it would not but that you could either print out the informaion or copy the informaion to a disk and mail either to the home office, which would be a whole lot better than the system they were currently using which was nothing.

While Fannie May was a business that was much larger than the small and medium businesses for which I am targeting this program, I have heard from several POS users that do own two or more stores. It would help them very much if the sales information from their individual stores could be transmitted to a central location.

While it would be fairly simple for me to have the POS program make a file of sales and inventory information that could be transmitted to a central location over phone lines, a program that would actually send or receive the file is completely beyond my capabilities. For this, I need help. Actually forget the "help" part, I need someone to do it.

Here is the methodoligy. When the POS program is closed it will call another program that will create the file to be transmitted. The file name will begin with the store's ID number and end with the extension .TMT. So if the store's ID number is 2138 then the file name will be 2138.TMT. Once the program creates the file it will run your program using the QB command RUN "TRANSMIT"

Your sending program will look for any file that ends with the .TMT extension and close down if it does not find one with a warning message. If it does find the file it will use a dial up modem to dial the phone number that is the first line in the file or maybe an internet connection and use Telnet to transmit to the URL in the first line of the file. It will connect with your second program at the other end and transmit the file. When your program at the other end sends conformation that it has received the file and stored it on the disk, the transmitting program will disconnect and erase the .TMT file. If for some reason the file transfer cannot be made the program will advise the user on the screen and ask if it should try again. If the answer is "no" the program will close and not erase the file.

Your receiving program will of course receive the files as they come in. It will open the file and look for lines that say DATEDATE. The program will create a folder (if it has not already been created) with folder name that follows DATEDATE. It will then save the file line by line until the end of the file or it finds another DATEDATE line. If another DATEDATE line is found that will constitute another separate file and must be saved in the proper folder. After all the files have been saved the receiving program will tell the sending program that the file has been received and hang up to wait for another incoming file.

The reason for splitting the file at the DATEDATE line is that if the transmitting program is not able to transmit the file, and then will not erase the file, then the next day the POS program will append the next day's data on the existing file instead of creating a new file. Eventually the file will be transmitted and the receiving program will have to be able to split it up into the proper day's folders.
<pre>
FILE NAME 2138.TMT

7735552565 PHONE NUMBER OR URL
DATEDATE 20040225 ----------------------------
FGRHTRH R
YRYTUY ERUNU
TYHBREYERUEU
TYBRTYERU5E6UE SAVED IN FOLDER 20040225
TYREUEUEUYEU
TYBYERUEUTEYU AS FILENAME 2138.TMT
TRYBY365UNUKTI
YUINO8,R6YU456
W457NM47U6778M
57N76I 56U7M7I UT
65MNR67INE6UIM7I
6UNR67IU EUYMN ----------------------------
DATEDATE 20040226----------------------------
R86OM6DRTUE56U
R5UNB47IU 56UY6
WEUBE56U36UN568
66UN67EINEN67I SAVED IN FOLDER 20040226
5UYBE57U6RUN58
RTUNBR67I YGR68N57 AS FILENAME 2138.TMT
W5UBNE76ITJ WTUN
EUNBE5UJWRUN
DRTGJUNETWRU
EUNBE56UW5Y
EUNE56U5N86 ----------------------------
</pre>
.
.
Now for all the non-programmers out there.

Here is where you get to comment on what data should be transmitted in the file. First of all I will assume that everything on the closing receipt should go. This will give the home office the information to do all the financial stuff including paying taxes.

Next should go the pieces and dollar amount sold for each item that has been sold plus the current inventory and model stock for that item. This assumes that either some of that item has been sold, or that the model stock for that item is greater than the inventory. If nothing has been sold (or returned) or you do not need to order more of that item then there is no reason to transmit data about that item. This will allow the home office to determine sales trends and ship merchandise to replenish the store's inventory.

How about payroll? Andrew has a payroll program that several of you are using. Should we ask him to find a way to have the payroll information from his program be included in the file to be transmitted?

What other information should be sent from the shops to the home office?

And what information should be sent from the home office to the shops? How about price change files? How about email or memos? What else?

Now that the information is in the home office what to do with it? The information in the files will be in text format with commas separating informaion in the same line. In addition the file will have the beginning of each data type labeled, for example the line "MERCHANDISE DATA" will be in the file just before the merchandise data is listed. This will allow any programmer to write a program that will allow his program to access the information and do anything the programer wants to do.

There should also be a general purpose program for the non-programmers out there to also access the data and make either printed reports, data files, or text files. What kind of reports should this program produce? Remember that on the disk are reports from each and every shop for each and every day. Each file contains the financial data, merchandise sales data, inventory data, and payroll data for each shop for each day. First I would like to suggest that the financial and merchandise sales data be combined into a separate file for the whole company. You may also want to break it down into files for each region / state. Now you can make reports for only one store or the stores in one region or state or for the whole company.

But what reports? Here are my suggestions. First, all the reports you can make from the "Reports" feature of the POS program. Second the report that comes from the PURCHASE.EXE program to reorder for each shop. Third payroll reports for each shop like the ones Andrew's payroll program produces. What else?

Please rememeber that this was never intended to be an accounting program to do everything for your business, only to provide data that the accounting programs you already have (or will purchase) will need. I do not know the data formats of your accounting programs so I will not be able to format the data generated by my programs to be able to be read into your accounting programs.

Also remember that I AM ONLY ONE GUY, WHO IS NOT GETTING PAID ANYTHING TO DO THIS. Really, truly, there is only one of me (the last time I counted) and since there is NO MONEY generated by this project I have to waste a lot of my time doing something else to earn a living, and ocassionaly I do sleep now and then. The "earning a living" and "sleep" things do seriously cut into my programming time so I can only do so much, sorry. So if you want to post that this program in your home office should either work just like QuickBooks or MS Money or should seamlessly intergrate data with those programs then I will probably either ignore you or heap scorn upon you. Which could actually be fun, so take your chances.

Reasonable suggestions that do not require cloning myself to accomplish them will be considered assuming that you do not catch me in a weird mood.

Scheduling.
Since there is no point in my writing the "reports" program until someone else comes up with the transmiting programs I will be working on the "13,000 items" feature as my next big project. Therefore this project will follow that one unless something else becomes more important like the "Customer accounts" project.

[Andrew]

I like where this is going- although I need time to think about it, and perhaps discuss it in chat some time :-)

I agree Payroll information might be useful for sharing or even receiving from head office.

[dave something]

For me personally, a re-vamped journal file that could be transmitted would be lovely. By re-vamped I mean an utterly flat file, one transaction per line, tab or comma delimited, or fixed width if it's easiest. Things I care about in the file are (in no particular order):

date of sale
time of sale
item #
qty
extended price
tax collected
order total
cashier id
tender type
transaction type (this is for the pesky part of me that wants to know who is ringing up No Sales, doing voids, etc.)

I have to rely on my managers (I have four stores in four cities), and I can't be there to personally supervise each and every variant transaction, but I do like to know what's going on.

From the data items listed above I can load Quickbooks by making my own flat file that it needs for importing data, I can also recreate daily sales by store for the home office, along with requisite summaries, etc.

As for payroll, too many variables for too many jurisdictions. Sounds like a nightmare to me. In any event, if it's offered, it should be its own file, and not related to txn data. (Duh, they said...)

Price changes files...hmmm...sounds like candy being offered. Can we move an updated stock table file back and forth without having store-level intervention for re-loading it? The thought of THAT scenario makes be shake.

Thanks for the software Dale, we remain very happy and will be even more so with a daily sales journal that can move back to HQ.

[Dale Harris]

dave something

The journal has a specific purpose. So that you, or your local tax folks, can look at it, add it up, and see if what was rung on each sale is actually in the total that was reported at the end of the day. As such it must closely resemble the receipt that is printed for each sale.

While you may want the file to be loadable into software the local tax folks want something that they can hold in their hand to inspect and is not dependent on your software to interpret. They are very untrusting people. So I am afraid that the journal has to stay pretty much as it is.

One thing that can be done is to transmit the .REC file to you. This is the file that records each transaction and can be viewed by using the "Void" feature in the POS.EXE program. They would also have to tell you what the first and last transaction number was for the day so you could find them. Unfortunatly this is only good for viewing the transactions on the screen and the data cannot be loaded into any other program.

It is always possible to send a stock table over the internet, either way. However remember that if you send an entire new stock table to a shop that you will change lots more than just the prices. The inventory, pieces sold, value sold, and lots more will also be changed. Remember to send both the .IDX and .TBL files to send a stock table.

As far as just sending the price file, I guess that I can make the POS.EXE program look for a price file with today's date when the program opens for the day and if if finds it, load it. Several people have asked for this.

[smckane]

The ability to send a price batch from office to stores would be brilliant. I think that all your suggestions for what the program should do are pretty much spot on Dale, and the idea of sending memos and emails back to the stores is pretty nifty as well :-)

From a personal point of view, I am more likely to use my limited Excel skills to rip the data out of files on the office computer rather than use a dedicated report program, however the ability for it to generate and differentiate between both individual store and whole group data must be built in (obviously when I say must, I mean I don't have the foggiest idea how or how much effort it really takes, but the idea sounds very useful ).

The only other thing that POS would then need to challenge IBM et al would be a multi-buy discount feature (like Buy One Get One Free/ Half Price/etc), but that can be an idea for a very rainy day

And here sits me not even realising that you could do stuff like this just using QBasic!

Stewart

[Dale Harris]

smckane,

Actually Jon Simpson is doing the actual transmit program and he is doing it in QB

[sysadmin]

Moved the "Deals" stuff here: http://www.home-nets.biz/discuss/phpBB2 ... .php?t=160
Might generate a little more interest that way, and I want to see some opinions.

Jonathan Simpson

[Dylan (JDR)]

Dlae, i didnt read anything on this page, sorry but from i gatehered in the first few lines you want a piece of software to transmit a copy of all sale to a home office,

I can make a batch file to copy everything to a "Home office" folder so that the manager can email it to home.

Would that work??

Email me somebody!
usmilitaryman13@yahoo.com

Later,
Dylan a.k.a. Dil