Coda — database alternatief — UpSert
Dit artikel is voor gevorderden en legt uit hoe je zonder UpSert te gebruiken een beter alternatief kan bouwen binnen Coda. Beter omdat je niet afhankelijk wordt van een externe berekening, bijvoorbeeld tussen Coda & Zapier.
This article is also — but in a shorter version — available in ENG
De UpSert Functie van Coda via bijvoorbeeld Zapier maakt het mogelijk een een rij bij te werken vanaf dat er een overeenkomst is met één bestaande waarde in een rij, zoals bijvoorbeeld een email adres. Echter mijn ambitie was om een match op te bouwen op basis van een unieke eigenschap, één die losstaat van de gebruiker. Ik koos voor de RowID van Coda.
De Coda Row ID
Elke rij binnen Coda krijgt een RowId toegekend en deze maak je zichtbaar via onderstaande functie:
RowId(thisRow)Vervolgens stuur ik deze ID mee vanaf dat een gebruiker eigen gegevens wil bijwerken via een formulier (ik gebruik PaperForm). Hoe ik dat doe, lees je hier.
Hoewel de UpSert geruime tijd werkte met de RowId zoals ik wenste, stopte dit op 8 oktober 2020. De Upsert met de Email bleef goed functioneren. Echter ik wil matchen op RowId. Gebruikers hebben immers het recht om al hun gegevens te verwijderen.
Ruim twee weken later vond ik een belangrijk deel van de oplossing gevonden. Het andere deel weer twee weken later. Het voordeel van mijn alternatief is:
- Er wordt altijd een nieuwe rij aangemaakt bij een update. Zodoende krijgt de gebruiker elke keer een email en daarmee de mogelijkheid tot aanpassing. Deze logica is ook van toepassing bij een update van een bestelling met dat een bestelling een unieke ID heeft.
- We kunnen verschillende emails sturen, één voor nieuwe inschrijvingen, één voor updates. Beiden met hun eigen look & feel (andere templates)
- Je bent niet afhankelijk van ingewikkelde processen in een andere applicatie zoals Zapier
- We kunnen — met dat het aanmaken van een nieuwe rij eenvoudig is — ook andere integratie software gebruiken. Tot op heden richt ik me uitsluitend op Zapier, ook vanwege de excellente support, echter er bestaan waardevolle alternatieven die — eens je geen support meer nodig hebt — ook goedkoper zijn.
Toevoegen van een rij
Vanaf dat we voor elke verandering een nieuwe rij toevoegen krijgen we uitdijende database. Het is zaak om alleen gegevens te bewaren die actueel zijn en dat veronderstelt dat we rijen verwijderen vanaf dat er een nieuwe rij is me daarin bijgewerkte informatie. Met dat we bij een update een RowId meesturen weten we of we al of niet te maken hebben met een bijgewerkte rij. Voor mijn klanten werk ik overigens ook met de Paperform ID, deze is langer en onregelmatig zodat creatieve gebruikers via een manipulatie van de URL een database niet kunnen leegschrijven.
Thuis in 8 stappen
We werken met onderstaand voorbeeld om de 8 stappen te doorlopen. Je merkt dat deze tabel ook verwijst naar rijen die niet langer bestaan, de eerste 16 rijen zijn weg. Het kan immers voorkomen dat je al rijen hebt verwijderd en dat er voor die rijen toch updates binnenkomen. Ik had dit pas door nadat ik het eerste deel van mijn oplossing had gebouwd om te beseffen dat ik toen nog maar halverwege was.
Stap 1 van 8— de laatste update
We beginnen met het vinden van de laatste update van een rij die een update ontvangen heeft. In het bovenstaande is dat rij 28 waar een verwijzing staat naar rij 32 in staat. Dat doen in de kolom ‘last value’ me de volgende formule:
thisTable.Filter([Retour ID].Contains(thisRow.[Row ID])).[Row ID].FormulaMap(CurrentValue).Last()stap 2 van 8 — vertaal naar checkbox
Nu we weten per rij wat de laatste waarde, is het zaak de meest recente rij — die dus een update ontvangen heeft — aan te vinken. Daarvoor gebruiken we onderstaande. De IsNotBlank() hebben we nodig om te zeggen dat het over ‘iets’ gaat.
thisTable.Filter([laatste waarde bestaande rij] = thisRow.[Row ID]).IsNotBlank()Stap 3 van 8
We kijken nu naar de rijen die geen update bevatten en die daardoor standaard geen Retour ID hebben. Echter geen Retour ID wil niet zeggen dat er later toch geen update voorkomt. Vandaar dat we per rij van de laatste waarde kijken of ze een Retour ID bevat.
En de formule die hierbij hoort is dan deze:
thisRow.[laatste waarde bestaande rij].Contains(thisRow.[Retour ID])Pas op de plaats
We hebben met voorgaande stappen de basis om voor bestaande rijen een slimme filter te bouwen. En dat zou volstaan mocht je maar één keer rijen verwijderen, de overblijvende gegevens exporteren naar een andere tabel en originele tabel verwijderen. Je brengt dan check 01 en check 02 samen via onderstaande en bent rond.
thisRow.[check 01] OR thisRow.[check 02]Echter we willen ook nog een werkende oplossing eens we rijen hebben verwijderd.
Hoe we filteren op verdwenen rijen?
Je kunt niet filteren op iets dat je niet hebt zoals verdwenen rijen. Ons vertrekpunt is dus eerst te werken met wat we wel hebben. Hieronder 3 screenshots om het filter proces duidelijk te maken.
stap 4 van 8 — step A
Het tweede screenshot toont onderstaande formule. Eerst filter ik op rijen die een update hebben gehad. Deze lijst verklein ik door te kijken naar rijen die een getal bevatten dat niet voorkomt in de RowID’s — via Contains.Not() . Dat zijn voorbeeld rijen met getallen kleiner dan 24 en vervolgens rijen die verwijzen naar hoger gelegen doch verwijderde rijen (we hebben het zo complex als mogelijk gemaakt).
En dan het stukje na AND. Dit streepje code had ik eerst niet voorzien. Echter nadat ik dacht klaar te zijn, bleek aan het einde dat de laatste rij die een update had gehad aangevinkt bleef staan vanwege de functie Last() terwijl we alleen de rij willen tonen met de update en niet de rij aanvinken die verouderde info bevat. We hadden dus twee actieve rijen terwijl we er maar één wilden.
Dat bracht me er toe om het stukje na AND toe te voegen waarmee ik Retour waarden kleiner dan de RowId uit de verzameling duw. Updates van gewone rijen handelen we immers af in voorgaande stappen. Hier gaat het echt om het verband met verwijderde rijen.
(thisTable.Filter([Retour ID].Contains([Row ID])).[Retour ID]).Contains(thisRow.[Retour ID]).Not() AND thisRow.[Retour ID] < thisRow.[Row ID]Stap 5 van 8
Nu we weten welke bestaande rijen geen update hebben gehad, wordt het tijd om na te gaan welke updates horen bij rijen die verwijzen naar verdwenen rijen. Dat doen we via onderstaande:
thisTable.Filter([Step A]=true and [Retour ID] =thisRow.[Retour ID]).Last()
Step 6 van 8
om de waarden uit Step B om te zetten naar een checkbox gebruiken we:
thisRow.[Step B].Contains(thisRow)Step 7 van 8
we hebben nu met allerhande tussenstappen drie checks uitgevoerd:
- op rijen met updates
- op rijen zonder updates
- op rijen die verwijzen naar verdwenen rijen
en we brengen deze dan samen in de rij oplossing
thisRow.[check 01] OR thisRow.[check 02] OR thisRow.[check 03]
Stap 8 van 8
En deze oplossing sturen we aan via een button die we vragen om de rijen die niet aangevinkt staan te verwijderen. Het resultaat zie je dan rechts en de werking van de button hieronder:
Of geschreven als functie:
DeleteRows(Filter(Voorbeeld, Oplossing=False()))
Is dit de perfecte oplossing?
Vast niet. Er zijn waarschijnlijk ook andere oplossingen te bedenken. Misschien ook met minder tussenstappen die eleganter zijn. Echter dit doet wat het moet doen: een tabel bijwerken met een button.
Tot slot
Je kunt dit verder verfraaien door het bouwen van een automation die elke dag of elke week draait. De basis is nu helder. Het uitwerken van deze schijnbaar eenvoudige oplossing was moeilijker dan ik bij aanvang dacht. Veel moeilijker zelfs. Werken met deelverzamelingen vraagt focus en creativiteit. De acht stappen lijken — eenmaal gepresenteerd — de eenvoud zelve, echter dat zijn ze niet. Het was een ingewikkelde puzzel.
Mijn naam is Christiaan Huizer en ik ben eigenaar van Huizer Automatisatie. In mijn rol als bedrijfsadviseur ben ik actief in het midden- en kleinbedrijf in Wallonië, Brussel, Vlaanderen en Nederland. Mijn klanten vragen me vooral om hun bedrijf voordelig te vereenvoudigen en versnellen. Ik maak daarbij gebruik van tools als AirTable, Coda, Zapier, MailJet en PaperForm.
