My first post here… what a great community - I have read dozens of other threads in the process of searching for answers. It’s been super helpful!
I’m a bit stuck with a seemingly simple problem:
I set up a database to do my accounting. I’m importing records from three bank accounts all into one table called “Transactions”. Every now and then there are transfers between accounts. I would like to connect them so that I can either filter them out or follow where they are going. What is the best way to do this?
I try to avoid portals for the simple reason that I’d like to be able to edit transfers from a list-based layout where all transactions appear. My solution so far is a one-to-one relationship that uses the key-field of "Transactions" called __id_TransactionID:
- I created a new field in the table “Transactions” called “_fk_XferID”.
- I made another table-occurrence of “Transactions” called “Transactions_Xfers” and set up the relationship to match “_fk_XferID” with the key-field “__id_TransactionID”.
- Finally, I made a value list based on the field “Transactions_Xfers::__id_TransactionID”, put the field “_fk_XferID” on my layout and attached the value list as a pop-up list. I have values displayed from another field (a calculation that displays date, amount, and name) to make it humanly possible to find the matching transaction.
While that somehow works, it’s not ideal. It connects the transfers only in one direction. And as you can imagine it also creates a very long value list to chose from.
My question: is there a better solution to the whole thing? If this is basically a valid approach, is there a way to limit the value list to not show every transaction in the table? Or maybe, is there another thread you can point me to I have not found ? I’m sure a lot of people had a similar problem.
Many thanks in advance, and a happy new year!