@QuentinLeblondwrote:
... a solution that would save me from categorizing 10 times the line 'Airtable' as 'Online Services' , or even better ...
Indeed there is. A script process that leverages GP3 to perform keyword and entity extraction automatically.
Using regex in a script, you could write a map key:value object that trigger specific categorisation upon matches.
This is something I’ve been wanting to do for sometime now, I’ll see what I can come up with.
With a transactions table, and a Categories table - you can have a script match a Transaction Description against a Category String Term, and then update that transaction with that category upon having a match.
I wrote this with this mornings coffee, it seems to work - you can see the automation filling in the matched Categories;
The match term is found within the Categories Table;
The automation triggers upon the Transaction Table being updated - it could probably be further refined;
The tricky bit is the scripting, where you'll need a solid understanding with arrays, objects, and what data the Airtable API expects. There are many ways to do this task.
Unfortunately I have to paste my script as an image due to a bug in the new Airtable forums - typing it out is good practice.
Hopefully this helps! I plan to implement this into my own systems now I've figured it out.
>>> The tricky bit is the scripting ...
Indeed, there are a lot of nuances that need to be considered. But this is a pretty elegant approach and should get the job done.
When faced with these types of challenges, I always worry about the maintenance required by the end users. In this case, it seems pretty straightforward; if you maintain the match terms and keep them ahead of the actual terms flowing into the transactions, it will work well.
There is a way to eliminate a sizeable chunk of this solution, namely the Categories Table and any code that needs to work with it. As I mentioned earlier in this thread GPT-3 could be used to extract keywords from the transactions and apply the discovered tags. This, of course, would have the added benefit of never needing to maintain any separate table or try to predict the terms and matches required for proper classifications. I make this suggestion only for the benefit of other readers.
As Musk often says - the best part in a machine is no part at all. 😉 But there is a replacement part involved - GPT-3; it does require an API call, and it is a non-zero cost (about $0.0002 per transaction). A thousand transactions per month would be about $2.00, so it seems like a net positive tradeoff if you want a deeply elegant and effortless system.
With a transactions table, and a Categories table - you can have a script match a Transaction Description against a Category String Term, and then update that transaction with that category upon having a match.
I wrote this with this mornings coffee, it seems to work - you can see the automation filling in the matched Categories;
The match term is found within the Categories Table;
The automation triggers upon the Transaction Table being updated - it could probably be further refined;
The tricky bit is the scripting, where you'll need a solid understanding with arrays, objects, and what data the Airtable API expects. There are many ways to do this task.
Unfortunately I have to paste my script as an image due to a bug in the new Airtable forums - typing it out is good practice.
Hopefully this helps! I plan to implement this into my own systems now I've figured it out.
Ok many thanks, very useful one.
I am no coder at all though, so I'll try to see if i can implement myself this; if there is any chance you can send me the code line, i'd really appreciate ! or even share the tables you just did, that I would adapt 🙂
Thanks a lot though, Im a first airtable-forum user, and very positively surprised by responsiveness and kindness.
Thanks a lot again !
>>> The tricky bit is the scripting ...
Indeed, there are a lot of nuances that need to be considered. But this is a pretty elegant approach and should get the job done.
When faced with these types of challenges, I always worry about the maintenance required by the end users. In this case, it seems pretty straightforward; if you maintain the match terms and keep them ahead of the actual terms flowing into the transactions, it will work well.
There is a way to eliminate a sizeable chunk of this solution, namely the Categories Table and any code that needs to work with it. As I mentioned earlier in this thread GPT-3 could be used to extract keywords from the transactions and apply the discovered tags. This, of course, would have the added benefit of never needing to maintain any separate table or try to predict the terms and matches required for proper classifications. I make this suggestion only for the benefit of other readers.
As Musk often says - the best part in a machine is no part at all. 😉 But there is a replacement part involved - GPT-3; it does require an API call, and it is a non-zero cost (about $0.0002 per transaction). A thousand transactions per month would be about $2.00, so it seems like a net positive tradeoff if you want a deeply elegant and effortless system.
Thanks a lot Bill
I'll see if i can have a look into GP3, but as said previously, im not a coder, so not sure im able to play with API either - despite ill probably have to try to directly plug my bank account's API to Airtable so i dont have to download/copy/paste transactions each month...
Thanks a lot for your time and kind reply though !
When faced with this issue I ended up creating a script that would check whether the text included any specific words, and I output stuff from there
For example, if the text contains any of "wix", "squarespace", "airtable", it would output the word "Subscriptions" for me
You'd have to manually categorize everything but eventually you get to a point where you've covered most of them anyway and / or get too lazy to keep it up (which is what happened to me heh)
Ok many thanks, very useful one.
I am no coder at all though, so I'll try to see if i can implement myself this; if there is any chance you can send me the code line, i'd really appreciate ! or even share the tables you just did, that I would adapt 🙂
Thanks a lot though, Im a first airtable-forum user, and very positively surprised by responsiveness and kindness.
Thanks a lot again !
Hopefully this posts OK - the recent community forum updates introduced some bugs that have been making it difficult to post.
let {transactionRecordId} = input.config();
let transactionsTable = base.getTable("Transactions");
let myRecord = await transactionsTable.selectRecordAsync(transactionRecordId,
{fields: ["Description", "Category"]})
let categoriesTable = base.getTable("Categories");
let myCategories = await categoriesTable.selectRecordsAsync( {fields: ["Category Name", "Match Term"]})
let categoryBank = myCategories.records.map( record => (
{
id : record.id,
name : record.name,
category : record.getCellValueAsString("Match Term").toLowerCase()
}
));
let noMatchrecord = categoryBank.filter((record) =>
record.name.includes("No Match")
);
let matchedObject = categoryBank.filter((record) =>
record.category.includes(myRecord.name.toLowerCase())
);
let myReturn = [];
if (matchedObject.length === 0) {
myReturn = noMatchrecord;
} else {
myReturn = matchedObject.map( record => ({
id :record.id,
name : record.name
}));
}
console.log(myReturn);
await transactionsTable.updateRecordAsync(transactionRecordId, {
"Category" : myReturn
})
Ok many thanks, very useful one.
I am no coder at all though, so I'll try to see if i can implement myself this; if there is any chance you can send me the code line, i'd really appreciate ! or even share the tables you just did, that I would adapt 🙂
Thanks a lot though, Im a first airtable-forum user, and very positively surprised by responsiveness and kindness.
Thanks a lot again !
The irony that a platform noted for its deep commitment to #no-code, which requires so much code, does not escape me. My advice, find a student at a local community college who codes and become close friends. The student needs these types of experiences and you may learn some coding along the way.
The irony that a platform noted for its deep commitment to #no-code, which requires so much code, does not escape me. My advice, find a student at a local community college who codes and become close friends. The student needs these types of experiences and you may learn some coding along the way.
Working with No-code platforms (I’m looking at you MS PowerAutomate Flow…) can be like a visit to the dentist… I really like Airtables approach - but that’s not to say there’s still plenty of room for improvement. Which reminds me, I have a backlog of feature requests that I need to post to the new Product Ideas forums section.
https://community.airtable.com/t5/product-ideas/idb-p/productideas
@QuentinLeblond I'm going to share a free and no code at all solution with you.. although not Airtable related!
Wave Accounting is completely free to use and not only auto-categorizes based upon your expense accounts, it also integrates with most banks and credit card providers.
I'm not affiliated in any way, just a user who has had great results. Hope that helps solve your issue.
Working with No-code platforms (I’m looking at you MS PowerAutomate Flow…) can be like a visit to the dentist… I really like Airtables approach - but that’s not to say there’s still plenty of room for improvement. Which reminds me, I have a backlog of feature requests that I need to post to the new Product Ideas forums section.
https://community.airtable.com/t5/product-ideas/idb-p/productideas
Hello Karlstens
This discussion came back up in my mails, so having a look at it again 😉
Fact 1 : no proper solution so far. I have sorted out a very heavy automation but not satisfying
Fact 2 : i tested your code, and for a reason I dont get, it only partially works as 1/ I cant get the 'No Match' printed in the required field when there is no match, and 2/ some times a word gets several answers despite not being in any single category (see attached with the word 'Good')
On top of this, I might need to ask for exact (or quasi exact) match to be valid, otherwise the risk is definitely to match wrong things together.
Happy to chat if ever you have any brilliant idea, given you're way better than me on these matters 😂
Many thanks !!
@QuentinLeblond I'm going to share a free and no code at all solution with you.. although not Airtable related!
Wave Accounting is completely free to use and not only auto-categorizes based upon your expense accounts, it also integrates with most banks and credit card providers.
I'm not affiliated in any way, just a user who has had great results. Hope that helps solve your issue.
Thanks a lot,$
Very kind, but I already have too many tools in my dashboards, so complicated for me to add one just for this...
Appreciated though, many thanks !
