Bulk update specific Accounts with Tracking Codes

#1

Tracking Codes are great. Now we wish to generate a report for a Charity where certain revenue and expense accounts are grouped and presented in a different way to the Profit and Loss report. I can achieve the layout and present the data required with Tracking Codes. (Custom Reports cannot deliver the report needed).

1. My question: Is there a way to “Bulk update” a specific account with a tracking code? At the moment we need to drill down on the account and then update each transaction individually.

2. An idea: Could a tracking code be added to an account within the “Settings” ----> “Chart of Accounts” -----> “Edit” function?

Any help would be appreciated, thank you.

#2

I think you are missing the point of tracking codes. They are meant to distinguish transactions posted to the same account as belonging to one division, job, purpose, or another. If you code every transaction posted to an account with the same tracking code, you haven’t accomplished anything, have you? The account was already (apparently) meant to record transactions applicable to that division, job, or purpose.

Or maybe I’m missing what you are really trying to do.

#3

Hey Tut I know exactly how to use tracking codes and their purpose but here is the thing people on this forum are using some of Manager’s capability in genius ways to achieve an outcome. All I am doing here is pulling a report like anyone else with the use of tracking codes.

Oooh Tut how did this happpen … you did not attempt to answer my question in but told me I am missing the point :sunglasses:

Also I have tested the principle of what the charity wants to achieve and it does work. It is just a fair amount of work / time.

Here is some more detail. The charity has about 5 Revenue streams and each revenue stream has its own set of expenses. These are currently beautifully presented in the P & L report.
Our end result is to have for example an “Analysis of Revenue” where all revenue from all income streams are reported by account under a common headings for Revenue

Revenue

Revenue - Donations, Fundraising and other similar
2003 - Conference, Camps Seminars
2007 - Home Missions

Fees Subscriptions and Other revenue
3550 - World Vision
3905 - Membership Fees

#4

OK, here is the answer to your question: No. You cannot bulk update a specific account with a tracking code.

But if you were willing to explain what you are trying to do, there might be a way to get what you are interested in. As it is, it appears you are trying to add a distinguishing factor to transactions that would already be distinguished by being posted to the account involved.

#5

Yes OK I will … Right on entering more tracking codes and trying this there appears to be more work to be done to make presentable. I am beginning to think it is not currently possible in Manager. Maybe Excel / LibreOffice?

Below is a test and all seemed pretty positive until I moved away from the 20xx series set of accounts to the 35xx series set.

Revenue1
Revenue1.png633×558 45 KB

So if you can picture a group of specific accounts grouped under say a common name “Revenue” and Totalled.
The plan is on the same report to do the same for “Expenses”. Essentially the guys want one report with revenue and expenses from all streams consolidated.

#6

OK using tracking codes in custom reports goes a long way to generate the sought after report. This may be acceptable if we could suppress the expanded account detail under each “Account Name”. so now

See below

Revenue4

OK so here is another question can this be done in custom reports? By the way thanks for the update on the “Bulk Update” Tut.
At the end of the day the tracking code input / work will still need to be done to pull the desired custom report.

#7

How would this be different from an ordinary P&L report? You can name groups however you want. All of this seems, based on what you’ve have shared so far, to be running around in circles because the organization has somewhat unusual (but not unprecedented) desires for how the chart of accounts is organized. But Manager can organize a chart of accounts just about any way you want.

#8

Yes I am too relaxed it is not the “Guys” it is the manner charities / churches need to report here when presenting their financials, it is a bit different from a FPO. How is it different well the P & L report separates for example all the revenue and expense streams based on the chart of accounts which in this case define about 6 different revenue streams. The chart of accounts is perfect so no renaming and group changes required. The chart is organised to show revenue stream 1 followed by the expenses relating to the revenue stream 1 then revenue stream 2 is followed by the expenses relating to that revenue stream 2 and so on. So far all looks good but is the P & L showing the various business units in the charity - Good and perfect.
However part of the reporting the charity must provide is a consolidated report of certain components of that revenue / income and expense streams in a summary and it is not ordered in the same way the Chart of accounts are because it is a summary. Are we able to pull a report something like the last example with account group and totals only? This offers a holistic view of the operation / organisation.

Now using tracking codes on the needed accounts allows me to get the data and present it from what looks like any revenue stream I may choose which is great.

How can I suppress / not display the content (the red boxes) of the Account Groups? We need only the account group and the related total for each account group, so if you image the red boxes gone in the last example that would be great.

This is way off topic now and more a custom report manipulation thing.

#9

Sounds like having custom fields on the chart of accounts is what you really want. Which has been suggested in other threads:


#10

I prefer the custom reports capability to locate and filter data to be presented in a report as opposed to making changes to Chart of Accounts. There are currently limitations in custom reporting but once the custom report tool is mature the function and capability will be there I hope.
If custom fields could be added somewhere in the chart of accounts as suggested under ideas that can potentially eliminate updating every transaction using that account with the appropriate tracking code or custom field. Then to deliver the custom report we could filter on that custom field in chart of accounts and deliver a report without making changes to the chart of accounts.

#11

I believe an easier and simpler solution to this problem to implement could be to use the existing Batch Update feature to Bulk update transactions in Receipts & Payments. I routinely use Batch Update to bulk add the BankClearDate by copying and pasting the Date field in excel. This saves me having to individually open every transaction that I have imported from the bank and used a Bank Rule to allocate.

However, what is really frustrating is that the Tracking Code field is not part of the Batch Update fields available. It includes every other field imaginable and even includes my custom fields such as Notes, but the Tracking Code field is not there, even though it is a built in part of the Receipts and Payments data. I understand that the Tracking Code field appears per line in the detail which complicates exporting for multiple line transactions that have detailed allocation, but the system must be able to extract the Tracking Code from the first line as the Bank Rule function can allocate an Account and Tracking Code to the first line on imported transactions from the bank.

Once the Tracking Code field appears in the Receipts & Payments Batch Update fields you can paste the transactions into a spreadsheet to manipulate. I usually insert a sort column at the beginning then fill down numerically starting at 1 to whatever to return data to original order after multiple sorting and manipulation. Then sort the transactions appropriately to get the ones you want, using description or date or reference, then delete the transaction you don’t need to change. You can then paste the Tracking Code you want on the spreadsheet and copy paste the data back into Manager for the Batch Update as it uses the unique Key to match the transactions being updated. Using this method, you would still have to manually update transactions with detailed allocations.

I use Tracking Code on every Receipt and Payment for profit centre reporting. Occasionally a large number of transactions will get imported without Tracking Codes because there is no rule in place which means a lot of work to update every transaction. The only other option at present is to restore the backup, create lots of bank rules and redo the import. It would be easier to use Batch Update.

Can you add the Tracking Code field to the Batch Update?

#12

Two points, @wereshop :

  1. Tracking codes are already included in Batch Update under the heading Lines.TrackingCode.

  2. There is no need to re-order transactions after manipulation in your spreadsheet before pasting back into Manager. The unique Key makes sure the correct record is updated.

1 Like
#13

Like compuit, I have similar issues with consolidating reports for charities, which is volunteer work so I don’t get paid for the hours of work I spend on this. The thing is a typical income account like Sale of Tickets needs to be split up between different Events for P&L reporting. We don’t want to have endless sub accounts sitting in the P&L, just use the one set of accounts and then use Tracking Code to extract data in reports for the different events. So we don’t want to recode every transaction within a particular Account but we would like to be able to Bulk Update the ones that we do want to recode to add or change a Tracking Code in the same way that you can Bulk Update the Account Code.

Please :pray: can you add the Tracking Code option to the Find and Recode function so you can change Account and Tracking Code. This enables adding the Tracking Code after bank import if it wasn’t added by a bank rule, as well as enables easy allocating/reallocating Tracking Code to line items in bulk which would save hours of work .

This should be very easy to enable given that the Bank Rule function already addresses it and that the format is already set up in Find and Recode.

#14

When you do Bulk Update, you do not have to Update all the transactions

In the spreadsheet delete the ones you do not need to update and then change the rest and update those

1 Like
#15

Many thanks Tut :heart_eyes:

As it has been a year since I last did batch updates, I forgot that the Tracking Code shows up as an ASCII string in the spreadsheet instead of simple text, so it was hard to identify which item was my Tracking Code. I have now found the Lines.TrackingCode column and copied down the ASCII string from an historical transaction so it is done. Hooray.

Ever since the removal of the Export button I have been faced with a blank box at step 1 in the Batch Update process where previously it would preload the headers and data in the box for me to copy. Now with the blank box, I hit Copy to Clipboard and paste into a spreadsheet to see what I get because it does not preload the information in the viewing box. Is it meant to be blank because I note in other users posts that their screenshots show information in the box?
This change is also the source of the problem regarding pasting large numbers of transactions because previously, when the data preloaded in the box at Step 1 it was possible to highlight the rows you wanted to copy but obviously I cannot now do that as their is no visible information so it just copies my entire database.

#16

Yes, it is. Because the change is fairly recent, you will probably find many forum posts showing the old approach.

#17

Thanks Joe91, I have been doing that. It is still time consuming when you want to update say the last 50 transactions but the copy to clipboard auto copies the entire database, which is a lot of unnecessary deleting. :cry:

#18

ok, at least I know I am meant to be staring at a blank box now, although why escapes me. I have read all the posts about Export button vs Copy to Clipboard button. To be honest, the preload of the data in the viewing box was far superior due to the ability to select part of the database instead of all of it.

#19

The empty box is there so you have a place to paste back the manipulated spreadsheet.

Many users did not realize you could selectively copy. And finding the desired transactions in a large data file could also be quite difficult, because you had to scroll in a small window through a long list for which you could not even see all the columns.

#20

Most of my charities have maybe 5 transactions per month, but one occasionally has 300 or more. I never found scrolling and selecting a problem because I would be working with a specific date range and the date column is at the start.

I am sure there are lots of features in Manager that many users don’t know how to use efficiently but if you use that as a reason to remove highly useful functionality you might finish up with no program at all. It makes absolutely no sense to take away the option to be able to select what part of the data you want because some users don’t know how to do it.

It is needlessly time consuming to continuously have to delete thousands of lines from a spreadsheet every time I paste something to update say 50 transactions. which is why I suggested updating the Find and Recode function in my other comment. :wink: