I am building a CRM with 2 tables: customer table (name, email, total purchase) and order table (order id, customer name, customer email, purchase value)
I want to implement an automation which triggers when new record on order table comes in and does action on customer table:
If (customer exists in customer table): find that customer on customer ledger via email and update their total purchase as per the current order by adding to their total purchase
If (customer does not exist in customer table): create new customer record saving customer name, email and their current order’s purchase value as total purchase.
I tried building this in Airtable automation but it doesn’t allow if-else conditional in formulas
How can I build this functionality? Please advise.