* Query Help Request: List of Cemeteries with count of times used

[LeslieP]

I feel like I'm kind of close to getting this query built, but my brain is just not taking me that final step.

What I want:
List of cemeteries with a count of times they are used, so I can add the "popular" cemeteries manually to my TNG website. I do not use the Address records hack, all place content is in the Place record.

First Step
Create a named list ("cemeteries") of all places that are used in burial facts.
Fact query
- column: "Label" =FactLabel(%FACT%)
- column: "Place" %FACT.PLAC>%
- rows: Add if =FactLabel(%FACT%) matches 'Burial'
- rows: Exclude if %FACT.PLAC>% is null
Run query, select everything in the Place column, add selected records to named list "Cemeteries"

Second Step
Count the number of times the entries in the Cemeteries List have been used as the place on burial facts.

This is where I am stuck.

The number of times those places are used will include all the times they're used in all types of facts, not just burial facts.

I know it's going to be some combination of LinksTo, LinksFrom, IsInList and maybe some others, but my brain just can't get me there on what would be in the columns and what the row filters need to be. I'm not even sure if it should be a place type query or a fact type query.

Once I see it, I'm sure I'll say "yes, this is exactly how to filter this information!", but my brain only works right on troubleshooting existing code and filters and stuff, I'm just no good at all at coming up with it from the get go.

All help greatly appreciated!

[tatewise]

You cannot determine those counts with a Query.
The LinksTo and LinksFrom functions only give record-to-record counts and not to/from specific facts.

The best you can do with a Query is to run your First Step query and sort the Place column.
Then manually count the rows that contain large numbers of the same 'popular' Cemeteries.

To count things like that automatically requires a custom Plugin.

[LeslieP]

Bummer. Thanks for letting me know. I guess I'll export to csv then open in LibreOffice Calc and summarize/count that way. At least FH can get me halfway there!

Appreciate the help.

[Gary Carson]

Below is a plugin that tatewise helped me with

Code: Select all

--[[
@Title: Cemetery Summary Report
@Author: Gary Carson
@LastUpdated: July 2017
@Description: 
Counts and Lists All Burial Places in the File
]]
 
tblCemeteries = {}     -- Define array for Burial Places
tblLatLongNum = {} -- Define array for Lat/Longitude
tblLatNum = {} -- Define array for Latitude
tblLongNum = {} -- Define array for Longitude

pi = fhNewItemPtr()  -- declare pointer
pi:MoveToFirstRecord("INDI")    -- point to the first Individual record
while pi:IsNotNull() do
   -- For each Person Add the Burial Place to the list
   strCemetery = fhGetItemText(pi,'INDI.BURI[1].PLAC>')
   tblCemeteries[strCemetery] = ( tblCemeteries[strCemetery] or 0 ) + 1
   strLatLongNum = fhGetItemText(pi,'INDI.BURI[1].PLAC>LATLONG:NUMERIC')
	tblLatLongNum[strCemetery] = strLatLongNum
   strLatNum = fhGetItemText(pi,'INDI.BURI[1].PLAC>LATLONG:LAT_NUMERIC')
	tblLatNum[strCemetery] = strLatNum
   strLongNum = fhGetItemText(pi,'INDI.BURI[1].PLAC>LATLONG:LONG_NUMERIC')
	tblLongNum[strCemetery] = strLongNum

pi:MoveNext()
end
-- Build Tables for the result set columns for Burial Place and Qty
tbCemetery = {}
tbLatLong = {}
tbLat = {}
tbLong = {}
tblcount = {}

for strCemetery, iQty in pairs(tblCemeteries) do
   table.insert( tbCemetery, strCemetery )
	table.insert( tbLatLong, tblLatLongNum[strCemetery] )
	table.insert( tbLat, tblLatNum[strCemetery] )
	table.insert( tbLong, tblLongNum[strCemetery] )
	table.insert( tblcount, iQty )
end
fhOutputResultSetTitles("Cemetery Summary Report",
   "Cemetery Summary Report")
fhOutputResultSetColumn('Burial Place', 'text',tbCemetery,#tbCemetery,260,'align_left',1,true)
fhOutputResultSetColumn('LatLong', 'text',tbLatLong,#tbCemetery,90,'align_right',1,false)
fhOutputResultSetColumn('Long', 'text',tbLong,#tbCemetery,90,'align_right',1,false)
fhOutputResultSetColumn('Lat', 'text',tbLat,#tbCemetery,90,'align_right',1,false)
fhOutputResultSetColumn('Count', 'integer',tblcount,#tbCemetery,30,'align_right',1,false)

[ColeValleyGirl]

Gary, hope you don't mind but I've edited your post to make the plugin display in a code window (by selecting all the code and clicking the </> button.)

[davidf]

I think I have seen similar requests previously

Is there a wish list want for an extension of the functions to include a count function?

=count(what[Individuals/families/notes(free-standing)/Sources/Repositories/Media/Places/Facts], where[specified by query or named list?], orderby, [Distinct|All])

e.g. Count(BurialFacts, where burial place contains "Cemetery" or "graveyard", orderby Place)

e.g. Count(Individuals, where birthplace Contains "Essex, UK", orderby firstpart of birthplace)

or something similar?