Haal verwijderde records terug in een oogwenk

Haal verwijderde records terug in een oogwenk

Om te beginnen…

Ik wil het hier hebben over hoe we omgaan met data die overbodig is geworden. Het artikel is FileMaker-specifiek en bevat daarom niet noodzakelijk richtlijnen die overeenkomen met algemeen database beheer. Het gaat hier ook niet over backups en backup-strategieën. We bespreken wel technieken die we zelf actief gebruiken in productie-applicaties. Wat betreft het gebruik van sleutelvelden (primary keys): we veronderstellen dat elke tabel een numeriek veld ‘__id’ bevat met als validatie: ‘Uniek’ en ‘Niet leeg’.

Waarom records verwijderen?

Waarom zou je je druk maken over overbodige data in je database? Hier zijn een paar voorbeelden van wat men vaak veronderstelt:

Geheugenruimte besparen

Met de huidige lage prijzen van opslagmedia zou dit één van de laatste bezwaren mogen zijn. Als je ooit geheugenruimte tekort komt omwille van de groei van je database dan is dat eerder een teken van onderschatte hardware-vereisten. Los het op door je hardware te upgraden en niet door je database te downgraden.

Zoekopdrachten versnellen

Je kan je hierover terecht bezorgd maken. Maar een tabel die op de juiste manier is geïndexeerd, kan gemakkelijk miljoenen records bevatten vooraleer je een snelheidsverlies merkt in zoekopdrachten. In onze ondervinding zijn de meeste applicaties niet van die aard dat het aantal records bepalend is voor de snelheid van het systeem. Tenminste als je je werk goed doet en ervoor zorgt dat zoekvelden geïndexeerd zijn, dat je met grote found sets niet sorteert en evenmin niet-opgeslagen berekeningen toepast. We gaan hier niet in op de diverse technieken om met grote datasets om te gaan.

Nu we dit hebben uitgeklaard blijft er nog één belangrijke reden over: hoe verbergen we overbodig geworden records voor de gebruiker? Hoe zorgen we er bijvoorbeeld voor dat een artikel dat niet langer beschikbaar is ook niet meer kan worden gebruikt in bestellingen en facturen?

Technieken om overbodige records te verbergen

Ik zet een aantal technieken op een rijtje, de minst praktische eerst, om uiteindelijk te komen tot wat me de beste oplossing lijkt en wat ook de reden is van dit blog-artikel.

Verwijder de records

Om kort te zijn: VERWIJDER NOOIT IETS uit een database. Doe je het toch, dan ontdek je vroeg of laat dat er altijd wel iets ontbreekt: externe sleutelvelden (foreign keys) verwijzen niet langer naar een geldig record en wat dan? Het record is weg en de enige manier om het terug te halen is het gaan zoeken in één of andere backup. Er zijn natuurlijk uitzonderingen: log tabellen bevatten vaak data die snel veroudert en die geen groot verlies inhouden als ze weg zijn.

Verplaats de records naar een archief tabel

In de meeste database-systemen is dit een algemene en eenvoudige techniek. FileMaker zorgt hier helaas voor twee hindernissen:

  1. FileMaker kent in het relatiediagram niet zoiets als het SQL ‘UNION’ statement. Daarmee kan je in een SQL-omgeving de inhoud van twee tabellen samenvoegen als was het één tabel. Dit is ideaal als je data wil scheiden in een actieve en een archief tabel. Via ‘Union’ kan je zonder veel moeite iets terugzoeken in zowel actieve als archief tabel indien nodig. (Je kan in FileMaker wel degelijk ‘Union’ gebruiken in een SQL statement via ‘ExecuteSQL’ maar het resultaat is geen tabel. Om dat te bekomen moet je al een virtual list inschakelen en dan zijn we ver van het standaard-FileMaker verhaal)
  2. Doordat er niet zoiets als ‘Union’ bestaat in FileMaker wil dat zeggen dat je geen automatisatie kan toepassen op actieve en niet-actieve records tegelijk. Je doet dubbel werk en elke wijziging in de logica op de actieve tabel moet worden toegepast op de archief-tabel.

Markeer overbodige records zonder ze te verwijderen

Dit is een wijd verspreide techniek. Voeg een aankruisvakje toe aan je tabel, bijvoorbeeld ‘IsActief’ en ga ervan uit dat records waar dat veld als ‘Onwaar’ evalueert, overbodig zijn. Dit kan je eenvoudig inbouwen in een toepassing en het werkt ook erg snel. Maar het zadelt je wel op met extra automatisatie: elke gevonden reeks moet worden gemanipuleerd om ervoor te zorgen dat de gebruiker geen overbodige records te zien krijgt. Denk ook aan portals, scripts, keuzelijsten, … Nog een nadeel is dat de FileMaker navigatiebalk steeds het totale aantal records in een tabel toont (actieve en inactieve). Dat kan verwarrend zijn voor gebruikers en je zou moeten overwegen om die navigatiebalk dan maar te verbergen en te herbouwen…

Gebruik een ‘Actieve Records’ tabel

Eindelijk, de meeste elegante oplossing: maak een nieuwe tabel ‘Actieve Records’ en plaats die voor je eigenlijke data tabel. Bewaar in die nieuwe tabel enkel primary keys en link die met de data tabel via een één-op-één relatie. Een voorbeeld met een tabel ‘artikels’ waarbij je niet-actieve artikels wil verbergen:

Bouw je ‘artikels’ tabel zoals gewoonlijk maar activeer de optie ‘serienummer toekennen’ NIET voor het veld artikel ID (‘__id’). Bouw nu een tweede tabel ‘Artikels~Actief’ en voorzie maar één veld, namelijk ‘__id’ met de serienummer optie AAN. Deze tabel zal één record bevatten voor elk actief artikel. Zo moet het relatiediagram eruitzien:

de relatie is bepaald als volgt:

Zoals je kan zien staat de optie ‘Verwijder gerelateerde records’ wel degelijk AAN maar enkel aan de kant van de ‘ARTICLE~Active’ tabel. Dat is om er zeker van te zijn dat op geen enkele manier de ‘ARTICLE~Active’ tabel records kan bevatten die niet bestaan in de ‘Article’ tabel.

Als je het ‘separation model’ gebruikt (data en interface gescheiden), dan moet die relatie zijn gedefinieerd in beide bestanden van je applicatie. De optie ‘verwijder…’ moet aan staan in de data file, de optie ‘records aanmaken’ moet aan staan in de interface file. Ik leg dadelijk uit waarom die laatste optie moet aan staan aan BEIDE kanten van je relatie.

We zijn nu klaar om de interface te bouwen. Baseer elke layout, keuzelijst en portal op de ‘ARTICLE~Active’ tabel zodat gebruikers enkel werken met artikels die actief zijn. Maakt de gebruiker een nieuw record aan in die tabel dan maakt FileMaker onmiddellijk ook een record aan in de ARTICLE tabel (omdat de optie ‘records aanmaken’ aan staat aan de rechterkant van de relatie). Als een gebruiker een record verwijdert in ARTICLE~Active is het inderdaad niet langer actief maar het record blijft bestaan in ARTICLE. Hiervoor is geen enkele script trigger of script nodig! Aan de andere kant, als je wil linken met Artikels vanuit andere tabellen, doe dat dan steeds rechtstreeks naar de ARTICLE tabel.

Een voorbeeld: om op een bestelling te kiezen uit actieve artikels, maak een picker of een keuzelijst die je baseert op de tabel ARTICLE~Active zodat gebruikers enkel geldige artikelen zien. Maar in de tabel met bestel-lijnen maak je wel een koppeling naar de ARTICLE tabel om je ervan te verzekeren dat die koppeling ook blijft werken als dat artikel niet langer wordt verkocht.

Verwijderde records terughalen in een oogwenk

Verwijderde records terughalen wordt nu kinderspel: maak een layout ‘Archief Artikels’ dat je baseert op de ARTICLE tabel. Zorg ervoor dat gebruikers hier zeker geen records kunnen maken of verwijderen of het is een maat voor niets natuurlijk. Maak nu een ‘Zet terug’ (Restore) knop om records terug actief te maken. Het script daarvoor kan volledig generiek worden opgemaakt zodat hetzelfde script werkt voor gelijk welke tabel als je je maar houdt aan een uniforme naamgeving voor je tabellen en je primary keys:

Set Field By Name [Get ( LayoutTableName ) & “~Active::__id”; Get ( LayoutTableName ) & “::__id”]

Dit werkt omdat we ook de optie ‘records aanmaken’ hebben aangezet aan de linker kant van de relatie.

Zoals je kan zien hebben we hiermee een robuuste structuur gebouwd die volledig is gebaseerd op ‘native’ FileMaker functionaliteit met een minimum aan scripting.

Stap voor stap

Hier is hoe het werkt: we verwijderen een record op de layout ‘Articles’, gebaseerd op de ‘ARTICLE~Active’ tabel.

Het enige dat we zonet verwijderden was het record met het ArticleID in de ARTICLE~Active tabel. Merk op dat de FileMaker werkbalk steeds het correcte aantal actieve records weergeeft. Achter de schermen is de artikel-data veilig bewaard gebleven in de tabel ARTICLES, zoals je kan zien op de layout ‘articles archive’:

Als je op ‘Restore’ klikt, zal FileMaker eenvoudig opnieuw een record aanmaken met het gekozen ArtikelID via de relatie-optie ‘Maak records…’

Download het demo bestand

clickworks_admin