How can I get this DB right??

I have been trying to find good ways to setup DB’s for various information in DT (in a database like setup) and it seams that I fail every time. It always feels there is no good way to do it; I desperately seeking some advice!

Let’s go with a prime example that is in front of me now… I want to setup a DB that specifically stores all documentation pertaining to several bank accounts. I want to store:

  1. Statements
  2. Check Images
  3. Deposit Images

Seems simple, right? Couldn’t be more wrong… Let me walk through how I tried to setup and demonstrate how it fails and where the variables are, and HOPEFULLY someone can help me…

I setup group structure like so (for each given bank account):

Chase (1071)
—>Check Images
—>Deposit Images

ECT… for each bank account. This system fails completely because now I have multiple groups with the exact same name (all the years) which makes it impossible to correctly tag something. Also, I need to keep track of 5 bank accounts, so that would leave me with a truly MASSIVE amount of duplicate groups.

Now, I could just throw the documents in each given folder, and then “tag” each document with the years being “tags”, but this is also unhelpful. That is because I want to be able to see these groups at a glance and that setup would destroy this! Here is why:

  1. I don’t want to have to drill down every time using the Tags view
  2. yes I could create smart folders under each group, but this simply takes FOREVER in DT, and I have other databases where this is the exact same problem where I would have to create HUNDREDS of smart folders to accomplish this… that time investment completely defeats the point of using DT in the first place.

So how should I go about making this DB (or any other)?? I would really love to:

  1. have no groups with duplicated names
  2. still be able to quickly sort any document correctly by applying tags (either “groups” or literal “tags”, or both, I don’t care)
  3. be able to use the AI to auto classify things correctly as well
  4. have a birds eye view so I can quickly navigate the structure (ie go to Chase > Statements > 2010) without having to run a search.

I also realize I could make unique group names that include all relevant parents in EACH group name, but this also seems to defeat the point of nesting anything, and then the group names become ludicrously long and run off the screen. So there MUST be a way to accomplish what I am looking for with as powerful as DT is… please help! Any advice is useful here! Thanks!


Not enough information here to really understand what data is important to you, but I’d be putting ALL this information in the document title:


depending on what is the most common sort order for the view you are used to. You should be able to filter the document with searches or smart groups, and leave fixed groups and tags for other information, like “house,” “car,” “business expense,” etc.

I use a format of YYYYMMDD which is pretty easy to pick apart with Regex when you need to.

Try a few and see what works for you.

HTH, Charles

This is a dense question, but why not use Quicken, or Moneywell, or any of a number of purpose-built financial apps? Not every problem can be solved in DT.

Thanks for the advice, which introduces yet another aspect I didn’t even want to get into: what should I be titling these documents?? Nice to hear that’s how you do it because I was considering the same thing! BTW, I cannot see your pictures cturner :frowning: They just show up as a linkless filename with no domain prepended.

To simplify my original question, it really comes down to a trade-off between what information do I want to indicate with tags, and what information do I want to indicate with groups, (and as cturner introduces) what to indicate with document titles. What is going to give me the best trade-off between working with AI and also being able to quickly classify things with tags (if one can be achieved at all)? I would like to use both groups an tags with max efficacy.

When going with groups you run into the duplicate group names which hinders tagging, but when you introduce tagging you lose that drill down structure ability you have with pure groups. THEN with searching you can sometimes lose the context of a document if it’s entirely dependent on its hierarchical position or tags. There must be some zen-like compromise for all of this. (and yes I have read the taking control book and it really didn’t help).

@korm, I realize it’s dense, sorry for the long post :frowning: I am not asking for a solution for this specific DB, but rather using it as an example to highlight the problem I comes across when trying to structure any DB in DT.

I do use finance software, but I like to have concrete copies of these documents just in case.

The density was in my brain, not your question. :slight_smile:

Well, I use this DTPO thing all day long for hours, and I never use the AI. Why? Because machines just don’t put my stuff in the right places and how the AI makes decisions is totally opaque to me. That’s heretical in DT land, but so it goes.

So, if I were doing what you’re doing I wouldn’t use the same group name twice. I would use some meaningful pattern in the group names. Like strings that stand for bank account, type of data (check, statement, deposit) and date. (I suspect that the missing links from Charles might portray the same idea.) Now that you have all those 4-digit groups, it’s only a small one-time chore to modify their names by selecting them and using the various techniques in Scripts > Rename (prefix, suffix, etc.).

What I do personally is not use DT to orgzanize the receipt and document data. I use a strict naming convention, then put everything into folders that Hazel keeps straight, and then I index them into DTPO.

This is very useful and also funny what I am hearing. You power users seem to forgo the grouping/tagging conventions altogether and use structured filenames instead to “tag” your data haha.

So I am interested to hear then if you use tags or grouping at all? I am intrigued by your idea of integrating hazel into the process as well, @korm. I was thinking of using the Tags app in conjunction with hazel rules to auto sweep stuff into its correct place as soon as I tag it in finder seeing as DT now will honor the openmeta tags :slight_smile: Any thoughts on whether that is a stupid idea? And also, the best way of methodically coming up with naming the files if I am to go with your method.

@ jpburt:

I’ve got a financial database, but with two major differences from the one I think you described.

  1. I don’t have duplictive group names. I follow a different naming convention, like this:

– Chase Accounts 2007
----Checking statements 2007
----IRA Statements 2007
----Investments 2007
----Credit Card Statements 2007
------Credit Card Payments 2007
------Credit Card Issues 2007
----Tax Forms 2007 - Interest, Dividends, IRA Minimum Distributions

Groups for other investment accounts 2007. by manager name and including monthly statements, transactions, notes and correspondence and tax reporting forms

Charitable Donations 2007 including scans of checks, clippings of credit card transactions, acknowledgement letters, etc.

Business Income 2007
–Billable Invoices 2007
–Payments Received 2007
–Expenses 2007
----Equipment and Supplies 2007
----Travel 2007
----Other Business Expenses 2007
----Taxes 2007

Home Maintenance and Renovations 2007 - in which I track construction contracts, payments, progress and costs.

  1. Note that this isn’t anything like an Intuit or accounting database. It’s a document management database in which I keep track of electronic and scanned statements, correspondence, receipts, invoices, etc. I don’t bother to scan each and every check, but I do include scans of checks related to a project expense or tax matter.

I don’t bother to track all expenses, but only significant ones such as a home repair project and, of course, business expenses.

Even so, there’s a lot of content in my financial database for each year, and having my documents and notes organized and searchable is much better than stuffing paper into shoeboxes.

I help out my tax accountant by creating rich text notes that list the items, such as deductable expenses with a link to each related document such as receipts, and then link that list to an Excel sheet that totals the category for the year, and is included in the database. I’m thinking about using Sheets for that purpose.

Bill’s structure is a useful approach.

Should work. Perhaps someday Hazel will actually support OpenMeta. I’m not a fan of autotagging anything. In my mental schema tags are adjectives (file and group names are adverbs), and I want to look at something before I decide if and what to tag.

For finance data I use when + who + what == “yyyymmdd source - doc purpose” == “20100304 Tiffany & Co - Tiara”. This is only partially machine-parsable, but good enough for at-a-glance comprehension.

Hi- Well, I do that for my main work which is historical in nature. My DTPO $$ database is just a dump for all sorts of receipts, and aside from grouping by year, I rely of searches to find what I want.

No links there, just used a PNG file suffix.

Also, your names could be even longer, but I’m sure you get the idea. Check out Templates for “bulk adding” repetitive groups structures.

HTH, Charles

Where might I find these templates? I’m still unaware of a place I can find a boatload of 3rd party templates & scripts other than poking around on the forum/google but this is something I would love to check out!

See, for example, Data > New with Templates > Project, or the ‘Registers’ templates.