Some URLs in reports do not show up as clickable hyperlinks

[JMM]

• TNG v14.0.4
• Legacy Family Tree Deluxe v9.0.0.456

Hello, folks,

When adding/modifying individuals in Legacy, there are 2 fields at the bottom of the window, that we are able to populate if the individual has a record on FamilySearch and/or Find-a-Grave:

When exporting from Legacy, the GEDCOM contains the following information:

1 _FSFTID LKSL-6QY
1 _FSLINK https://www.familysearch.org/tree/person/details/LKSL-6QY
1 _FGRAVE 113220854
1 _FGLINK https://www.findagrave.com/memorial/113220854

TNG imports the above successfully:

Note that in TNG, I renamed _FSFTID, _FSLINK, _FGRAVE and _FGLINK above , but the point is that TNG is importing the links correctly, and in TNG on the getperson page those 2 links above are clickable.

Now, I recently created a couple of reports, one that lists all individuals in my TNG that have a FamilySearch ID/link, and the other that lists all individuals in my TNG that have a Find-a-Grave ID/link.  However, in those reports, the URLs are just text, and not a clickable hyperlink.  As an example HERE, note that none of the URLs are clickable hyperlinks.

I don't know if the problem is with Legacy, or with TNG.  I assume that the problem is not with Legacy, as it is exporting those links that TNG is displaying correctly on some pages (such as on getperson).  So would it be safe to assume that the problem is within showreport.php ?

I am not a coder, but if the problem is indeed in showreport.php , would anyone be able to figure out what I can do (change) in that file so that those links all show up as clickable hyperlinks?

Thanks in advance & have yourselves a great day.

Regards,
John

[ADC]

Hi John,

Your problem isn't really within TNG, it is with the SQL that generates the report. When, for example, the underlying SQL query produces the data for your report, none of the data is in a hyperlink form. TNG recognises certain fields, and adds hyperlinks, for example in the Person ID field. (This is a bit of a simplification but I'm sure you get the point). The principle here is that SQL produces the data and the presentation layer (TNG in this case) formats it. TNG does not know that you want the Links you have in your data presented as hyperlinks.

It is possible to send the data from the SQL query formatted as a hyperlink. I can either explain how to do this, or you can post your report SQL code here (or message it to me) and I will make the changes for you. You basically have to alter your SELECT statement and surround the link fields with HTML href statements. I have tested this on a simple report and it works fine.

For example,

SELECT personID, concat('<a href="',FSLINK,'"></a>')

Where FSLINK is the sql field that stores your FamilySearch link.

I hope this makes sense, I'm happy to help further of course.

Adrian

[JMM]

10 minutes ago, ADC said:

or you can post your report SQL code here (or message it to me) and I will make the changes for you.

I'll post it here instead messaging it to you, in case the code with your solution is of help to others:

SELECT tng_people.living, tng_people.private, lnprefix, prefix, suffix, title, tng_people.branch, birthdate, birthdatetr, altbirthdate, altbirthdatetr, deathdate, deathdatetr,firstname, lastname,birthdate,altbirthdate,if(sex='M',families1.marrdate,families2.marrdate),deathdate,burialdate,e130.info as info130,e131.info as info131,e138.info as info138,e139.info as info139,(if(sex='M',families1.wife,families2.husband)) as spouse, tng_people.personID, tng_people.gedcom, nameorder FROM (tng_people ) LEFT JOIN tng_families AS families1 ON (tng_people.gedcom = families1.gedcom AND tng_people.personID = families1.husband ) LEFT JOIN tng_families AS families2 ON (tng_people.gedcom = families2.gedcom AND tng_people.personID = families2.wife ) LEFT JOIN tng_events e130 ON tng_people.personID = e130.persfamID AND tng_people.gedcom = e130.gedcom AND e130.eventtypeID = "130" LEFT JOIN tng_events e131 ON tng_people.personID = e131.persfamID AND tng_people.gedcom = e131.gedcom AND e131.eventtypeID = "131" LEFT JOIN tng_events e138 ON tng_people.personID = e138.persfamID AND tng_people.gedcom = e138.gedcom AND e138.eventtypeID = "138" LEFT JOIN tng_events e139 ON tng_people.personID = e139.persfamID AND tng_people.gedcom = e139.gedcom AND e139.eventtypeID = "139" WHERE (( e130.info != "" OR e131.info != "" ) AND ( e138.info != "" OR e139.info != "" )) AND tng_people.gedcom = "MotyerJM" ORDER BY lastname,firstname,CAST(SUBSTRING(tng_people.personID,2) as UNSIGNED)

In the above:

e130 = _FSFTID = FamilySearch ID
e131 = _FSLINK = FamilySearch Link
e138 = _FGRAVE = Find-a-Grave ID
e139 = _FGLINK = Find-a-Grave Link

I see that the items are based on event numbers, such as e130 instead of _FSFTID... I'm in the process of slowly sorting my events in TNG, so I will need to modify my reports accordingly (which shouldn't be a problem for me).

With your code with the solution to the above that I provide you here, I should be able to easily figure out how to get my other 2 similar reports to work.

27 minutes ago, ADC said:

Your problem isn't really within TNG, it is with the SQL that generates the report. When, for example, the underlying SQL query produces the data for your report, none of the data is in a hyperlink form. TNG recognises certain fields, and adds hyperlinks, for example in the Person ID field. (This is a bit of a simplification but I'm sure you get the point).

Yes, I get the point & understand what you're saying.

28 minutes ago, ADC said:

For example,

SELECT personID, concat('<a href="',FSLINK,'"></a>')

Where FSLINK is the sql field that stores your FamilySearch link.

I hope this makes sense, I'm happy to help further of course.

Yes, it actually makes complete sense to me... I'm surprised.  I have absolutely no knowledge whatsoever about SQL... and it's too late in life to learn now .

Thanks for your help with this, Adrian, it is greatly appreciated.

Regards,
John

 

[ADC]

Hi John,

Well there's a lot more JOINS in that SQL than I was expecting 😁, so we might have to a couple of goes to sort it out.

I won't go into loads of detail, but you can see that in your SQL you have the following SELECT statement that collects data from your table e131 (the Family Search link) (I've snipped a lot off):

SELECT ... e131.info as info131 ...

This takes the data from the field "info" in table "e131" and calls the result "info131".

What we want to do is format that data so it is a hyperlink. That just means adding a bit of HTML to either side of it. So we can format it like this:

SELECT ... concat('<a href="',e131.info,'"></a>') as info131 ...

This still pulls the data from e131.info, but adds a bit of code either side, and still stores the result in info131.

We also need to do the same to the e139.info field which contains the Find A Grave link.

This assumes that the fields e131.info and e139.info contain the complete links required, for example, "https://www.findagrave.com/memorial/113220854"

So ... give the following code a try, where I've just made the replacements explained above to e131.info and e139.info.

SELECT tng_people.living, tng_people.private, lnprefix, prefix, suffix, title, tng_people.branch, birthdate, birthdatetr, altbirthdate, altbirthdatetr, deathdate, deathdatetr,firstname, lastname,birthdate,altbirthdate,if(sex='M',families1.marrdate,families2.marrdate),deathdate,burialdate,e130.info as info130,concat('<a href="',e131.info,'"></a>') as info131,e138.info as info138,concat('<a href="',e139.info,'"></a>') as info139,(if(sex='M',families1.wife,families2.husband)) as spouse, tng_people.personID, tng_people.gedcom, nameorder FROM (tng_people ) LEFT JOIN tng_families AS families1 ON (tng_people.gedcom = families1.gedcom AND tng_people.personID = families1.husband ) LEFT JOIN tng_families AS families2 ON (tng_people.gedcom = families2.gedcom AND tng_people.personID = families2.wife ) LEFT JOIN tng_events e130 ON tng_people.personID = e130.persfamID AND tng_people.gedcom = e130.gedcom AND e130.eventtypeID = "130" LEFT JOIN tng_events e131 ON tng_people.personID = e131.persfamID AND tng_people.gedcom = e131.gedcom AND e131.eventtypeID = "131" LEFT JOIN tng_events e138 ON tng_people.personID = e138.persfamID AND tng_people.gedcom = e138.gedcom AND e138.eventtypeID = "138" LEFT JOIN tng_events e139 ON tng_people.personID = e139.persfamID AND tng_people.gedcom = e139.gedcom AND e139.eventtypeID = "139" WHERE (( e130.info != "" OR e131.info != "" ) AND ( e138.info != "" OR e139.info != "" )) AND tng_people.gedcom = "MotyerJM" ORDER BY lastname,firstname,CAST(SUBSTRING(tng_people.personID,2) as UNSIGNED)

There is some testing for blank fields later in the code, which I'm hoping will still work, but as I said, we might have to do a few trials.

Perhaps set this up as a new temporary report until we get it working!

Cheers, Adrian

PS: If anyone reading wants to correct anything I've said that appears incorrect, please do so ... I'm OK with SQL but this is uncharted waters 😁

 

[JMM]

Well, I copied & pasted your code into a new (temporary) report, and you can see the results HERE.

Maybe this is too complicated to do.  I don't want you to waste your day experimenting with this, as it's not that important to me.  But if you want to treat this as a challenge... I won't stop you .

Thanks for your help, Adrian.

Regards,
John

[ADC]

Oh dear 😳

That wasn't what I was expecting!

I have spotted one thing that probably stops the actual field from being output, so perhaps try this when you have a minute please.

SELECT tng_people.living, tng_people.private, lnprefix, prefix, suffix, title, tng_people.branch, birthdate, birthdatetr, altbirthdate, altbirthdatetr, deathdate, deathdatetr,firstname, lastname,birthdate,altbirthdate,if(sex='M',families1.marrdate,families2.marrdate),deathdate,burialdate,e130.info as info130,concat('<a href="', e131.info , '">' , e131.info , '</a>') as info131,e138.info as info138,concat('<a href="', e139.info , '">' , e139.info , '</a>') as info139,(if(sex='M',families1.wife,families2.husband)) as spouse, tng_people.personID, tng_people.gedcom, nameorder FROM (tng_people ) LEFT JOIN tng_families AS families1 ON (tng_people.gedcom = families1.gedcom AND tng_people.personID = families1.husband ) LEFT JOIN tng_families AS families2 ON (tng_people.gedcom = families2.gedcom AND tng_people.personID = families2.wife ) LEFT JOIN tng_events e130 ON tng_people.personID = e130.persfamID AND tng_people.gedcom = e130.gedcom AND e130.eventtypeID = "130" LEFT JOIN tng_events e131 ON tng_people.personID = e131.persfamID AND tng_people.gedcom = e131.gedcom AND e131.eventtypeID = "131" LEFT JOIN tng_events e138 ON tng_people.personID = e138.persfamID AND tng_people.gedcom = e138.gedcom AND e138.eventtypeID = "138" LEFT JOIN tng_events e139 ON tng_people.personID = e139.persfamID AND tng_people.gedcom = e139.gedcom AND e139.eventtypeID = "139" WHERE (( e130.info != "" OR e131.info != "" ) AND ( e138.info != "" OR e139.info != "" )) AND tng_people.gedcom = "MotyerJM" ORDER BY lastname,firstname,CAST(SUBSTRING(tng_people.personID,2) as UNSIGNED)

Hopefully that should populate those fields as least. Because I don't have those tables I can't test the output unfortunately.

I have to say that I'm not sure what is formatting your original table, so the mess that appeared last time may still be a mess, although I do have one thing up my sleeve if it does look the same. The headings, for example, are not being defined in the SQL so I'm not sure where they are coming from. Is this a report you wrote from scratch or picked up from somewhere else?

Cheers, Adrian

 

[JMM]

Bingo.  The hyperlinks are showing up now, as you can see HERE in that same temporary test report .

17 minutes ago, ADC said:

Is this a report you wrote from scratch or picked up from somewhere else?

It's a report that I created myself, but by using TNG's report writer... you know, where I choose the fields to the display, then the criteria, and finally the sort fields.

Regards,
John

[JMM]

24 minutes ago, ADC said:

Because I don't have those tables I can't test the output unfortunately.

That makes sense, as you wouldn't have those tables unless they were imported into TNG, like mine were from Legacy:

[JMM]

Hey, I just noticed something... this is weird.

When I used TNG's report writer, with the screenshots from a couple of replies ago, THIS is what TNG displays, which is correct.

But, when I am in TNG Administration, in the Reports section, and I click on the green Test checkmark, the attached page is displayed (see attached).

But when I paste that same (copy & paste) into a new test report, only at the bottom in the OR Leave Display, Criteria and Sort fields blank and enter direct SQL SELECT statement here:, with nothing else selected above, then THIS is what that report looks like... just like your report with all those extra columns.

You would think that if a report was created using TNG's report writer, and if that report displays perfectly, that the SQL code displayed by TNG for that report should be correct, and could be used as-is in a new rerport.  But that is not the case.

[ADC]

3 minutes ago, JMM said:

Hey, I just noticed something... this is weird.

You just beat me to it 😁

Well, we have had some success ... at least we now have the fields formatted as hyperlinks and they are clickable 👍

So what is happening  (I have just discovered) is that when you create a report by using the click-and-select writer, it doesn't creates a record in the tng_reports table which does really have the SQL code in it, but a list of the things you have just defined - which fields to display, what criteria, what sort order, and so on. When you run the report, the PHP code retrieves that information from the tng_reports table and does clever stuff with it - for example, it does not show the fields you have not asked for, but which the query is using for one reason or another (testing for living, for example).

However, when you just paste some SQL into the bottom box, although it is stored in the tng_table it doesn't do anything clever with it, it just runs the SQL and displays the results - the messy stuff you can see.

There are probably a couple of ways around this. I will have a think about which is the simplest and get back to you later as I have to pop out now. It's definitely solvable and won't be too complicated, and we have definitely made progress!

Cheers, Adrian

[theKiwi]

You could pass the GEDCOM file through a text editor before uploading it to achieve exactly what you're looking for.

I have a field FamilySearch ID in Reunion that is exported as for example

1 _FSID LJQ3-G5G

I open the GEDCOM file in BBEdit, and run the following Find and Replace on it with GREP

Which results in this in the GEDCOM file now showing this

Quote

1 _FSID <a href="https://www.familysearch.org/tree/person/details/LJQ3-G5G" target="_blank" title="Click to see person on Family Search">LJQ3-G5G</a>

which then shows up in TNG as this

 

You can see that on this page for my Dad

https://lisaandroger.com/genealogy/getperson.php?personID=I2&tree=Roger

You could do the same for your various fields like FSID and FindAGrave. (I have FindAGrave handled differently by the FindAGrave Links Mod, so I enter all my FindAGrave number as required by that Mod as CRid=235925580 in Reunion so that they show up throughout TNG as

That mod also does the same thing for FindAGrave cemetery numbers.

Roger

[JMM]

Thanks, Roger,

But my GEDCOMs exported from Legacy, when they're imported into TNG, TNG already shows the hyperlinks on the getperson page, and always has, as you can see for THIS individual near the bottom, at the beginning of the Other Personal Events section.

My question pertains to TNG not displaying those same hyperlinks in Reports.

But I do like how you have them displayed on your site in the Burial row... that's another project for me to do on my site .  But again, separate from my reports issue.

Thanks anyway, and have a great weekend.

Regards,
John

[ADC]

Roger is correct in saying that changing the data at source, i.e., in the GEDCOM file before it is imported, will make things work. It is currently working in the getperson screen because TNG recognises it as a hyperlink and formats it accordingly, but it doesn't do this in the report output in the same way. If the data in the table already has the HTML in it, it should work in the getperson page and in the Reports. This is one of the methods I was considering, but it appears the data you have are stored in non-standard tng tables and I am not sure how they get there, so it's not something I want to mess with.

I think I can write the SQL which will output the same format as the original report, give or take. I will have a go later today and probably post the result tomorrow,

Cheers, Adrian

 

[JMM]

Adrian,

I thought it might be a simple coding change in showreport.php , but it has turned into something much too difficult, and quite possibly impossible.  So let's forget about trying anything else.  I do sincerely thank you for your efforts until now.

I do have 1 question though...

I came across this in phpMyAdmin, for what fields to display for 1 of those reports:

And I wondered what would happen if I were to wrap 2 of those fields in that URL stuff, so I tried this, knowing that it wouldn't work because I don't know the proper syntax:

Would you happen to know what that proper format would be for those 2 fields?

Aside from that, I understand what Roger & you are saying, but for me to search my GEDCOM (with more than 6,700 people in it) prior to each time importing into TNG... it's not worth it to me .

It was worth a try.  Thanks again for your help today, and have a great week.

Regards,
John

[theKiwi]

36 minutes ago, JMM said:

Aside from that, I understand what Roger & you are saying, but for me to search my GEDCOM (with more than 6,700 people in it) prior to each time importing into TNG... it's not worth it to me .

 

This takes literally seconds - my GEDCOM file has over 18,000 people in it. I have that Find and Replace saved, and once the GEDCOM file is open, I press Command-F to open that dialog, choose the script from the button that looks like a g

 

then click the Replace all button and it's done in barely 2 seconds - the entire 384,000 lines of the file are scanned for that Find string, and where found replaced with the Replace string

Save the file and upload it to your site by FTP.

Could hardly be simpler.

Roger

[JMM]

3 minutes ago, theKiwi said:

Could hardly be simpler.

At my age, I have only about 2 remaining brain cells... and they spend most of the day too busy looking for each other .

I will Google later today how to save/run scripts in Notepad++.  I use Find/Replace in Notepad++ all the time... I just have to find out how to save it.

Thanks for the suggestion, and will let you know how I make out.

Regards,
John

[ADC]

1 hour ago, JMM said:

Would you happen to know what that proper format would be for those 2 fields?

I'm afraid that won't work as you want it to, John, I have already tried it.

I appreciate you're probably fed up by now, but I will get you one final SQL to try tomorrow and I'm sure we will have some success.

Cheers, Adrian

[JMM]

Just now, ADC said:

I appreciate you're probably fed up by now,

Hell, no!  Quite the opposite .

I'm just tweaking some columns to display, and then will reply to Roger.  His trick works perfectly!

[JMM]

Roger,

Your method works perfectly, Sir, as can be seen HERE (in my combined report).

For now, I am using a Windows program called grepWin, which I don't believe is able to save anything.  But now that I surprisingly know what I'm doing, I'll be looking for another similar program like this, but one that allows me to save the search/replace commands.

Thanks again to you & Adrian for your help today, and have yourselves a great evening.

Regards,
John