Simplify Journal report?

Make comments, ask questions, or just complain about the software on this site. Or comment on any educational software.
Please note that by clicking on links that may appear in these posts that you may be leaving the Dale Harris Educational Software website and that the content of those sites is the sole resposibility of the authors of those sites.

Moderators:daleadmin, Dale Harris, Alan, Andrew

arcovine
Forum Regular
Posts:38
Joined:Tue Oct 21, 2008 11:04 am
Location:Pontianak, Indonesia
Simplify Journal report?

Post by arcovine » Mon Feb 13, 2017 3:58 am

So I just tested the Journal feature the other day. And after I tried some 40-50 transactions, the journal file is unbelievably long (including opening & closing report).

This is the example:
https://drive.google.com/file/d/0B9Ftio ... sp=sharing

Is there any possibility to shorten the transactions to one line per transaction? For example:

Tran No | Tran Time |Description | Quantity | Price/Unit | Tax | Transaction Amount

0001 | 2017-01-25 10:30 | Chocolate waffle | 2 | 10000 | 0 | 20000
0001 | 2017-01-25 10:30 | Chocolate drink | 2 | 8000 | 0 | 16000
0002 | 2017-01-25 10:40 | Orange juice | 3 | 8000 | 0 | 24000
0002 | 2017-01-25 10:40 | Cheese waffle | 3 | 10000 | 0 | 30000
etc

That's what I had in mind after I saw some examples of other reports.
Please let me know if this is possible. Thank you!

User avatar
daleadmin
Forum Owner
Posts:1279
Joined:Tue Dec 30, 2003 9:47 pm

Re: Simplify Journal report?

Post by daleadmin » Mon Feb 13, 2017 1:08 pm

Arcovine,

Unfortunately in many locations there are legal requirements for the journal in that it has to report EVERYTHING. At least that is what their local tax buddies tell them.

Off the top of my head I cannot think of a way to get the type of report you want but with so many features in DHPOS I may come up with something.

Until then you could always try to reconfigure the deflector dish, that always works on Star Trek.

Dale

arcovine
Forum Regular
Posts:38
Joined:Tue Oct 21, 2008 11:04 am
Location:Pontianak, Indonesia

Re: Simplify Journal report?

Post by arcovine » Wed Feb 15, 2017 10:53 am

Lol. Thanks for the response, Dale.

User avatar
daleadmin
Forum Owner
Posts:1279
Joined:Tue Dec 30, 2003 9:47 pm

Re: Simplify Journal report?

Post by daleadmin » Thu Feb 16, 2017 12:29 am

arcovine,

Well I have given this some thought and got nowhere. As a last resort I thought of the REC2CSV.EXE program that is described here http://www.dhpos.com/postip11.htm#rec2csv

This program will take the info in the record of past sales and send the transactions you select to a .CSV file that can then be loaded into a spread sheet program. From there you can make any report that you want.

Unfortunately it does save all the data from each sale to the .CSV file. You can manually copy and paste just the line you want from each sale to another spreadsheet and then get what you want but that is labor intensive and I am sure that is not what you want.

Below is the data for just one sale. However the BBS will not preserve the columns that the data is held in the spread sheet.

BEGIN SALE TRANSACTION # 1782 2/15/2017 23:11
1. DALE
STOCK NUMBER DESCRIPTION TAX RATE PIECES UNIT PRICE PRICE
60004 COLOR KEY TWO SIDED 1 1 2.79 2.79
1
SUB TOTAL 2.79
TAX1 at 10.25% 0.29

SALES TAX 0.29
TOTAL 3.08
CASH TENDERED 3.08

arcovine
Forum Regular
Posts:38
Joined:Tue Oct 21, 2008 11:04 am
Location:Pontianak, Indonesia

Re: Simplify Journal report?

Post by arcovine » Sat Feb 18, 2017 10:01 am

Thanks, Dale. Will try it out.

User avatar
brucef2112
Forum Regular
Posts:336
Joined:Mon Mar 06, 2006 11:19 pm
Location:Broward County, Floriduhh
Contact:

Re: Simplify Journal report?

Post by brucef2112 » Sun Mar 05, 2017 10:17 pm

Long, long ago (2011) I created a helper app (in Excel 2002) to work with the output file created by Dale's REC2CSV.EXE utility.
It was intended so sort out the method of payments plus some other stuff. It was helpful to 1's of people.

However aside from the summary data, I think i remember that the flattened data is saved as a separate worksheet in workbook.
It may give you the answer you need.

For the following reasons, I cannot verify this:
1. I currently don't have a PC (pre-windows 7) to run DHPOS or REC2CSV.EXE .
Therefore I don't have a REC2CSV file to see if my Excel app produces what you need.
AND
2. I have long forgot the password I used to lock access to the code modules. :(
So I can't view the code to see if it does flatten a CSV to the format you are looking for.

Both of these are surmountable. Just not now. ('cause i now have a hobby)

The Original Forum post is here: http://www.dhpos.com/discuss/phpbb3/vie ... a95#p11629
The entire post has lots of details but what makes me think it will produce what you need is "Item 2. Worksheet with the flat REC data."
If it creates what you need you can just save that worksheet out to another name and BAM! you got what you need!!

Read the thread. Then go back and read the whole thread. In it is a link to download my Excel helper app.
The version of Excel this was written in is 2002. However some later versions did run it just fine.
I have no idea how far along the version trail it will go. I'll guess if it runs on WinXP, then that version of Excel should be fine to run my App.

You will have to give it a try and see what results it produces.
Read the forum post.
Run Dale's REC2CSV.EXE utility to get your CSV file.
Run my Excel helper. WHEN YOU OPEN MY HELPER FILE FOLLOW THE INSTRUCTIONS EXACTLY AS OUTLINED IN IT and see what it returns.

Write back and tell us of your success!
Later,
Bruce

They that can give up essential liberty to obtain a little temporary safety deserve neither liberty nor safety."
Benjamin Franklin - Historical Review of Pennsylvania, 1759

User avatar
brucef2112
Forum Regular
Posts:336
Joined:Mon Mar 06, 2006 11:19 pm
Location:Broward County, Floriduhh
Contact:

Re: Simplify Journal report?

Post by brucef2112 » Mon Mar 06, 2017 1:21 am

Well, I found the password to the code module.
"Ohh joy!" I said. I'll just browse my code and see if my code flattening the CSV file will meet what the OP wanted.

Only to find out that back in the day of development I decided the code module should not only be password protected, BUT the code should be obfuscated before I posted it online. :(
I don't remember how i did it but I succeeded. The code is not easily readable. I'd guess some 2 or 3 hours to hammer it out into 'VBA English' again.

Well of course the projects original working copy is backed-up on a CD. Its just that it's in a Big Box with ALL my file backups (far back as 1995). Yes I backup files and backup often. I'm weird like that.

So to save my sanity we are back to the OP using their data and my tool to test if it works for them.
If the OP reports back with something good or bad I may find a moment to search 'the box' to update for their need.
Hopefully though the original App as is, gives them what they need.
I then have made this project useful for 2's of people over the last 6 years.
Later,
Bruce

They that can give up essential liberty to obtain a little temporary safety deserve neither liberty nor safety."
Benjamin Franklin - Historical Review of Pennsylvania, 1759

User avatar
daleadmin
Forum Owner
Posts:1279
Joined:Tue Dec 30, 2003 9:47 pm

Re: Simplify Journal report?

Post by daleadmin » Mon Mar 06, 2017 1:55 am

Hi Bruce,

It is good to see that this forum is beginning to come alive again.

Dale

User avatar
brucef2112
Forum Regular
Posts:336
Joined:Mon Mar 06, 2006 11:19 pm
Location:Broward County, Floriduhh
Contact:

Re: Simplify Journal report?

Post by brucef2112 » Wed Mar 08, 2017 11:50 pm

Hey Dale,
Good to see ya too.
Later,
Bruce

They that can give up essential liberty to obtain a little temporary safety deserve neither liberty nor safety."
Benjamin Franklin - Historical Review of Pennsylvania, 1759

User avatar
brucef2112
Forum Regular
Posts:336
Joined:Mon Mar 06, 2006 11:19 pm
Location:Broward County, Floriduhh
Contact:

Re: Simplify Journal report. Confirm it works

Post by brucef2112 » Tue Apr 11, 2017 10:07 pm

I now have a XP pc. I've tested and verified my program works. I half guarantee this to work with 2002 up to Excel 2007.
My RecCSV_Parse.xls program does flatten the journal csv to show each item sold on a single line.
You must have Excel set to allow macros to run. TOOLS > MACROS > SECURITY Select either Medium or Low.

1. When you open RecCSV_Parse.xls read the instructions. then click the 'Click To Start' button.
Enter the info for the location of your POS and the location of the CSV file already created by Dale's program.

2. On the main form leave the check box checked for "Flatten Just the stock table data in my CSV......"
Un-check all the boxes in the 'Choose to Include' section. Doing so will minimize the time to flatten the journal data.
3. Click the OK button.

Once finished, Each line contains the following fields for each Stock Item sold in the journal.

Code: Select all

STOCK NUMBER
DESCRIPTION
Tax Rate ID
Pieces
Unit Price
Price
PayBy Method(s) UNKNOWN Cash Check CredCrd Debit	
Trans#
Trans Date	
Vendor#
Vendor Name
Employee Name
Total Pcs Sold
With the data flattened like this it is very easy to do a ton of analysis of your sales.

Refer to the links in this thread to get my helper app and other information needed to use Dale's program.
Last edited by brucef2112 on Tue Apr 11, 2017 10:18 pm, edited 1 time in total.
Later,
Bruce

They that can give up essential liberty to obtain a little temporary safety deserve neither liberty nor safety."
Benjamin Franklin - Historical Review of Pennsylvania, 1759

User avatar
daleadmin
Forum Owner
Posts:1279
Joined:Tue Dec 30, 2003 9:47 pm

Re: Simplify Journal report?

Post by daleadmin » Tue Apr 11, 2017 10:15 pm

Thanks Bruce

User avatar
biglog
Occasional Poster
Posts:12
Joined:Sun Apr 16, 2017 5:21 am

Re: Simplify Journal report?

Post by biglog » Mon Apr 17, 2017 3:56 am

Hi Bruce,

How does your report treat items sold on with a sale price?

thanks

Log

User avatar
daleadmin
Forum Owner
Posts:1279
Joined:Tue Dec 30, 2003 9:47 pm

Re: Simplify Journal report?

Post by daleadmin » Mon Apr 17, 2017 10:43 am

Biglog,

Bruce's program take the information from the file of past transactions. What you see there will be what is in the .CSV file. To view a past transaction press "5. Voids" from the main DHPOS menu.

Dale

User avatar
biglog
Occasional Poster
Posts:12
Joined:Sun Apr 16, 2017 5:21 am

Re: Simplify Journal report?

Post by biglog » Tue Apr 18, 2017 3:37 am

So the "unit price" column is Bruce's spreadsheet is the "R price" from the stock table not the actual price the item was sold for, if a discount was applied?

Log

User avatar
daleadmin
Forum Owner
Posts:1279
Joined:Tue Dec 30, 2003 9:47 pm

Re: Simplify Journal report?

Post by daleadmin » Tue Apr 18, 2017 7:50 pm

Big log,

I just ran a sale here with a sale price and the sale price ended up in the file of past sales and therefore can be viewed when using 5. Voids.

I then ran the REC2CSV.EXE program and the sale price (and the customer info) ended up in the .CSV file.

So now it is up to Bruce (since I do not know how to allow macros in Excel) to determine if the sale price ends up in his file (I think it would have to since it is the price in the .REC and .CSV files) and if the customer info also ends up in Bruce's file.

Dale

Post Reply

Who is online

Users browsing this forum: No registered users and 46 guests