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!

[Travitron]

Hi @natalie-grist

As @Bilbo also requested, do you happen to have an updated link to this example? I’m having a similar issue and am curious how things worked here…

Thank you,
Travis

[natalie-grist]

So sorry @Bilbo, I completely missed your request! @Travitron - I’ve updated the example here: Community #1059 - Categorizing Transactions - Grist

There are 3 tables; a table of categories, a table of transactions and then a table that categorizes each transaction.

This third table has a reference column to each table, linking transactions to categories.

In the Transactions table, you’ll see the Expense-Tag column. This is a formula column I added to create a nice label to use in the Transaction Categorization table, combining Date, Business and Amount information for the related transaction. This is also a reference column. We use this to link our two widgets so when we select a Transaction, we only see related Categorizations.

When you initially select a transaction that has not been categorized, the Transaction Categorization table will not show anything.

Select a category from the dropdown. Amount will automatically populate with the total amount of the transaction.

This value is editable. So if you need to categorize a portion of this, you can update this value. Then, when you add the next category, it will return the amount minus the already-categorized portion.

This is done via a trigger formula in the Amount column.

The formula here is;

total_categorized = PEEK(SUM(Transaction_Categorization.lookupRecords(Transaction=$Transaction).Amount))
$Transaction.Amount - total_categorized

We use lookupRecords() to find all categorizations for this transaction. We use dot notation to pull the Amount value for each of those records. We use SUM() to summarize all of those amounts. We use PEEK() to avoid the #CircularReference error.

We set this to apply on new records so it will find the remaining uncategorized Amount when a record is created but then can be edited as needed.

I then added a Fully Categorized formula column. This returns true (or toggled on) if the value in the Amount column for the transaction matches the sum of the Amounts in the categorized table.

SUM(Transaction_Categorization.lookupRecords(Transaction=$id).Amount) == $Amount

If you want to clean up the view, the Expense-Tag column can be hidden from the Transactions table since this is a formula column, working in the background. The Transaction column in the Transaction Categorization table can also be hidden. Linking between the two widgets will continue to work with this hidden.

Please let me know if you have any follow up questions!

Thanks,
Natalie

[Travitron]

Thank you! I took a look at this and learned some things. It is not quite addressing the issue I’m having, so I’ll probably be posting that on the community sometime soon. I am preparing an example document that I can share to explain my issue.

[Travitron]

UPDATE: I’ve created a new community post describing my use case:

Possible to Automatically Insert Rows into Another Table without using a widget?

Based on all of my research and testing so far, it does not seem possible to automatically add/update/delete records based on some action/trigger, such as when a new record is added to a table. I’m hoping this is in-fact possible somehow. If not, I would like to nominate this as a feature request which would unlock a lot of great functionality! I have created shared a document to explain what I’m trying to achieve: https://docs.getgrist.com/wdC9yriF7nkA/Attribute-Auto-Add-Testing-SHARED/m/for…