Best way to conveniently create record in junction table

[GreetingsEarthling]

I am playing around with financial transaction tracking and am working on categorization.

I have a table of transactions that I imported from my bank, and a table of valid categorizations (my “Targets”, such as gas or groceries). Some transactions may need to be split between multiple categorizations, so conceptually I’d like to use a junction table of “Transaction Categorizations” to link transactions to categories.

I am wondering if there is an easy way to smooth the creation of the records in the junction table. What I’d hoped for is a page with 3 widgets:

• A table of transactions
• A table of categories
• A card of junction items

… and be able to select a transaction, select a category, and click “+” on the card to create a new junction item that is automatically linked to both. But it doesn’t look like it works that way.

Before I go too deep into trying other things, wondered if I am making sense and if so if you have suggestions for how data entry setup might work best in this scenario? Thanks!

[natalie-grist]

Hi there!

You could add a Category column to your existing table of transactions that would be a Choice List column.

I created a short example here for you:

https://public.getgrist.com/gbYUB9HFHvpu/Community-1059-Categorizing-Transactions

In the Category column, multiple categories are selected for most transactions. You can see all the different Choices available under the Column Configuration panel on the right hand side. You can add any categories you need.

I added the Month and Year columns as formula columns so we can break down spending within each month and year.

On the Summary of Spending page, there is a summary table of the Expenses table grouped by Category. To create this summary table, click the green ‘Add New’ button > Add Widget to Page > Select Widget = Table > Select Data = Expenses > Click Green Summation Icon > Group By = Category.

Now you’ll have a table of Categories and how much was spent in each category. If a transaction is assigned two categories, it will count towards both.

The table in the upper right is another view of the Expenses table but set up to Select By our summary table, Expenses [By Category].

When you select a category in the first table, the second table will update to show transactions assigned to that category. Again, if they were assigned to multiple categories, they will show up in both. For example, the Amazon expense on 5-30-22 is assigned to both the ‘Office Supplies’ and ‘Business Related Expense’ categories so you see it when you select each category.

I used the Month and Year formula columns in the Expense table to summarize spending on this page as well. You could also summarize your spending across each category by month and year, which is shown on the Summary by Month and Year page

The Credit Card Activity Template is a great example of this as well, with more realistic data.

Hope this helps! Let me know if you have any other questions.

-Natalie

[GreetingsEarthling]

Thanks for your response, Natalie.

What I mean when I say that a transaction may need to be split, is that, for example, for a transaction of $400, $300 might be categorized as Groceries and $100 as Personal Spending (Discretionary). I need a way to indicate how much of the transaction is related to each of the categories. That’s why in my mind I thought a separate table, with related records, was most appropriate, rather than a Category column with multiple choices.

Is there any way, either in a “Reference List” column, or a “Select By” statement for 2 widgets, to easily create a new “Transaction Categorization” record that has the related record prefilled?

Here’s an example with a little dummy data of what i am working in now.

https://docs.getgrist.com/igMK7ZEc1ay3/Personal-Finance-copy/m/fork/p/7

“AppleTransactions” has transactions from the bank, “Targets” has the categories, and the “Transaction Categorizations” table (only visible in Raw Data) is the junction table. The actual “Transaction Categorizations” widget was where I was trying to smooth the process of creating new Transaction Categorization records so that each AppleTransaction record has “true” in the “Fully Categorized” column. Does that make sense? Let me know if I’m not communicating real well.

[natalie-grist]

Sorry for the delay - had to get some help on this one Dmitry made a tweek to your document. Check it out at the link below:

https://docs.getgrist.com/doc/igMK7ZEc1ay3~d5Yaz1XSgmKKtYKPkEGZDT~26/p/7

To enter a categorization, select a transaction then select a category in the Target ID dropdown of the empty row in the bottom widget. The amount is auto-populated with the total uncategorized amount but you can change the amount to anything.

Then, the next row will be auto populated with the balance remaining after selecting a category.

Let us know what you think!

[GreetingsEarthling]

Just realized, I forgot to reply - I think this is helpful, thank you!

Down the road, I think it would be helpful to be able to create linked records with some kind of a button in the reference or reference list field. A little bit like how in a choice list field, if the entry you want isn’t there, a “plus” appears at the bottom to add your new choice.

[Bilbo]

Hello. The link does not work any more but I am interested in the solution. Could you re-post? Thanks!