Be warned: genealogy is a terrible tearjerker.

First we had Newsnight rottweiler Jeremy Paxman blubbing like a bairn at the revelation that his widowed Glaswegian great-great-grandmother was refused poor relief for her nine kids because one of them was born on the wrong side of the sheets. “You shouldn’t go into this family history business. It’s just upsetting,” he croaked between sobs.

Some people never listen. So now we face the prospect of JK Rowling in a forthcoming edition of the BBC’s Who Do You Think You Are? emptying the Kleenex box after it turns out her French great-grandad had to settle for the Croix de Guerre during the First World War, instead of the posher Legion d’honneur, as previously reported. “The Croix de Guerre is much better than the Legion d’honneur for me,” she splutters, as if to make the best of a bad job.

That’s the problem, isn’t it? Fictional characters can be headstrong, but the author retains at least some control over their fate. With rellies, you have to take what you’re given and there’s a dearth of happy endings. Anyone digging around in their past should be braced for the occasional outbreak of club root.

Rowling has my sympathy. I admit to a brief burst of pride when an amateur military historian told me my great-great-great-great-grandfather ended the Napoleonic Wars weighed down with gongs for gallantry. Alas, all those medals went to another Benjamin Johnstone. Mine served three years in the North British Militia and never saw a shot fired in anger. The army pension he boasted of on his census returns was probably a bar-room bluff (unlike his 10 children, including apparently, two born to different women simultaneously).

Even so, when I stumbled on his gravestone in the old kirkyard in Moffat, it brought an unexpected lump to my throat. My other half had the same experience last year when we planted spring bulbs around the graves of his great-great-grandparents, who both died in 1832, beside the abandoned church at Ardclach, in a beautiful bend in the River Findhorn. Like most Scottish ancestors, they were agricultural labourers but these close encounters with our past have the power to move us deeply, not just because they remind us of our own mortality but because we are moulded from these people, this simple clay.

In a sense we are all family. Who knows how many third cousins twice removed we brush elbows with on station platforms? Apparently, the six billion people alive today are all descended from the 60,000 or so who populated the planet a few thousand generations ago. Don’t they say that a third of all men are related to Attila the Hun? Explains a lot.

Genealogy generates tears of frustration too: those elusive missing pieces in the family puzzle: the boxes of brown curling photographs of crusty old characters and wan girls in lace dresses, without a single caption; the indecipherable parish records; the much-reproduced family trees, all with the same mistakes; the charlatans who churn out assembly line family histories with fake coats of arms.

Writing other people’s family history sometimes calls for tact and diplomacy. How do you depict a much loved relly who turned out to have been an incorrigible drunk or womaniser? Never trust family stories, especially the ones claiming royal or famous ancestors. They are vulnerable to the Chinese whispers syndrome that turns “Send reinforcements, we’re going to advance” to “Send three and sixpence, we’re going to a dance”.

Yet, for all its frustrations, uncovering your family history can be both oddly compulsive and surprisingly moving. A couple of years ago my father and I casually punched the name of his Uncle Arthur into a computer in the Imperial War Museum. Seconds later his regiment popped up (the Royal Scots Fusiliers) and the date of his death in 1917 on the Western Front. Our only photograph of Arthur shows a lean, bespectacled, young solicitor from Alloway. No gallantry medal on Arthur’s chest, just a big hole. In a small voice Dad sniffed: “Do you happen to have a spare tissue?”