Jim

It's quite possible the sysadmin user of your company file has not gone to Setup, Settings, Project, Allocation and turned on the option to use the Tab key to get at the Allo columns.

You can also use Ctrl-D or the documented Ctrl+Shift+A keyboard keystrokes. The menu option is always on the menu named for the module you are in. You can be in any column to activate these as long as there is an account and dollar amount entered.

Not sure which module you found the deleted allocations but I just tested 2017.1 in the General Journal and Invoices and found both warned about changing signs. In the General Journal I was allowed to use Ctrl-D to get at the allocation and change the sign but in Invoices, I had to resort to using the mouse to activate the Project Allocation dialog.

If in the General Journal you are just taking a Debit of $1000 and ignoring it, going to the Credit column itself and entering a number, then the allocation did delete. In my test, I went to the debit column and put a negative in front of the $1000 assuming you meant to change it to $1000 credit.

I'm not sure it would be a good idea to automatically change the allocation because when you change any part of a recurring transaction, the part that might need to change is also the allocation, therefore a warning is appropriate. As you have said though, I do not like the allocation completely deleting if you type in a completely different column. Hopefully the workaround I suggested will help if that was your intention.

Hi Richard

Thank you very much for the quick reply. My hope was that it was a configurable option and you've pointed me in the correct direction.

It didn't occur to me to put a negative in front of the value to retain the project information while converting it from a credit to a debit or vise versa. Thanks for pointing that out.

One last vent about the projects feature is the built in project reports are lousy. We use a third party add-in that does a much better job.

In general, it would be nice to be able to have heading information repeated on every row. Perhaps that's another feature that can be toggled? For example Excel's pivot tables have the option to repeat all item labels.

Jim Palmer, CPA, CMA
Accounting Manager
Teen Challenge Canada Inc.

I'm not sure I understand this last issue.

Repeating column header information for each row makes for a very busy report with a lot of duplication. The only option in Excel pivot tables I am aware of is the Repeat row labels on each page and I thought that was for pivot tables that spanned page widths. I've never seen a project income report need that unless you expand the Description column of the Detail report really wide or have increased your fonts larger than the default 8 point. I've just never needed to do that before.

Hi Richard

Perhaps it's better explained with an example. I do agree it would be an ugly report but I'd like the Sage output to generate Excel list view or a table for further analysis.

Here's a sample of an actual report with some names and values changed.


Centre One
4205 Fundraising Receiptable 15,000.00
4210 Fundraising Other Charities 20,000.00
4215 Fundraising Non Receiptable 30,000.00
4500 Goods Auction 2,000.00
5215 Fundraising Banquet 40,000.00
5850 Courier & Delivery 202.40
67,000.00 40,202.40

REVENUE minus EXPENSE 26,797.60

Centre Two
4205 Fundraising Receiptable 20,520.00
4210 Fundraising Other Charities 15,000.00
4215 Fundraising Non Receiptable 3,200.00
5215 Fundraising Banquet 25,000.00
5220 Fundraising Special Events 500.00
5850 Courier & Delivery 300.00
38,720.00 25,800.00

REVENUE minus EXPENSE 12,920.00


I need to have the Centre name on every line to facilitate creating views to show information such as:
List project name, net profit in descending order by net profit.
List fundraising receiptable in descending order by project.

There are many other pivot views such as


Acct Centre One Centre two
4205 15000 20000
4210 20000 20520
4215 ...


Activity Jan Feb Mar ...
Centre One
Income
Expense
Net

Centre Two
Income
Expense
Net

I refer to this as "changing data to information".

Have a good day.

Jim Palmer, CPA, CMA
Accounting Manager
Teen Challenge Canada Inc.

You can do this in Excel.

Dump the report to Excel, then use the GoTo Blanks function.

See this website/video for more information.  The video does not indicate you must hit Ctrl-Enter to save the formula but the written steps does.

Hope this helps

I usually:
- dump the Sage reports to an Excel sheet,
- insert cells at the top as needed to get the first row aligned, then
- set up an AutoFilter, filter a detail column for all the blank lines and subtotals, and delete them
- clear the filter on the detail column
- Set a filter on the first title column, showing only blank lines again
- Put in a formula to make A6 equal to the hidden row A5. Copy it all the way down the sheet (using copy / paste,NOT using flash fill)
- Clear the filter, copy the column, use paste special to turn the formulas into text.
- Repeat as necessary.

If I have to do it more than 10 times or so, and I don't have XLGL, I get out the data dictionary and build a query in MS Access or LibreOffice BASE.

Once I have a 'square' table with a complete set of data for each row, I can use a pivot table.

I can do all of that too and I do have XLGL, which is amazing.

My original comment was the project reports that come directly out of Sage are lousy and I think you've confirmed that. You either need to do many steps in Excel or you need XLGL or some other third party project to convert data to information.

Speaking of XLGL, we use the XDataArray and XDataCondition functions inside the XGLP function to mimic a wildcard.

This function retrieves all expenses for account A20 from dates E8 to D9 for Dept B20 where the project name begins with the characters in D20

=XGLP($A20,E$8,E$9,$B20,,,,,XDataArray("Projects", "Project_Name", XDataCondition("Projects", "Project_Name like '"&$D20&"%'")))

Jim Palmer

Hi Richard

I think you might have forgotten to include a link to the video/webiste or it was removed from the message.

Sorry, it's back now. I don't know why it didn't save.

Don't forget the programmers created the reports for viewing the information for management purposes, not crunching more after viewing. What you are looking for is a data dump and from what I understand, you can get that from XLGL. And as Randy alludes, there is always MSQuery/Excel connections using the ODBC drivers.

Jim_Palmer said:

One last vent about the projects feature is the built in project reports are lousy. We use a third party add-in that does a much better job.

The reports in Sage (or any) system are not all things to all people, but they're usually good enough to start with for most organizations. 

The big advantage to Sage 50 over some other systems we've looked at, is that the ODBC connection both free and automatically set up on login. 

The SDK is also a free download, for reporting the data relationship diagram dev-datarel.pdf is invaluable    I've found it to be a sort of 'Rosetta Stone', without which, the data dictionary is all but indecipherable.