Multiple database relationship issues (conditional value lists).
I am creating personal financial management database and got stuck because of either faulty setup of relationships, or simply lack of experience.
1st database CATEGORIES (parent) contains 3 levels of categories, and ID field. so: id_cat, cat_1, cat_2, cat_3.
how it looks like:
cat001 | income | planned | wages
cat004 | expenses | bank | mortgage and so on...
I linked this database to INVOICES (child) like this: categories::id_cat = invoices::id_cat.
I also have joined table LINES in between of INVOICES and ACCOUNTS and that whole setup works pretty fine.
Problem occurs when I go to INVOICES layout. i WANT it to have conditional value list functionality - that is, first of all, I would have to choose either it's INCOME or EXPENSES, then, depending from the choice, it would give me 2nd level of category choice, and then - conditional 3rd level category choice. I defined two more relationships to achieve that:
1) CATEGORIES::cat_1 = CATEGORIES_cat_2::cat_1
2) CATEGORIES::cat_1 = CATEGORIES_cat_3::cat_1 and CATEGORIES::cat_2 = CATEGORIES_cat_3::cat_2
Also, I created 3 value lists and all that conditional value list thing displayed correct conditional values. But. As I used cat_1, cat_2, cat_3 fields from CATEGORIES table in INVOICES layout, it mixed categories, because relationship again is through id_cat.
I tried 3 options:
1) add cat_1, cat_2, and cat_3 fields into INVOICES table and set them as a calculations had formula =CATEGORIES::cat_1 (and others respectively) and set field control style to drop down list. It didn't work, as calculation fields are not editable.
2) after first option didn't work, I tried to leave those cat_1, cat_2, cat_3 as regular fields and set field control style to drop down list. For 1st level it worked, because it was simple use all values from field value list. As for other 2 levels - it didn't.
3) I tried to leave CATEGORIES::cat_1, cat_2, and cat_3 on INVOICES layout and use script trigger when I modify 3rd level category, it would go to CATEGORIES layout, find particular category, set it's unique id_cat as a variable, return back to INVOICES layout and set id_cat field there. Script worked, too bad that whenever I switched throughout the different categories, it messed up parent fields in CATEGORIES db, because, obviously, I was using them directly.
Finally, I came up to halfway solution: added another value list, which USES id_cat values, but DISPLAYS all values in one row (from calculation field, which combined all 3 categories in this format), for example:
income -> planned -> wages
It at least doesn't mess up categories anymore, still, the problems with this setup are several:
- as list contains of 27 categories, it's hard not pleasant to scroll throughout all it searching the only category you need at the moment
- in other tab i have operations, that only contain either expenses or income, so having all values showing up wouldn't be great.
I believe Phil or other gurus' could give me a way out of this. Thanks in advance.