* Burial Locations - Unique

[Gary Carson]

I wish to create a query that list all unique burial locations.

My format for burial Place is Cemetery, City, County, State, Country with the number of links.

I already have a query that list all burials by individual.

Please advise.

[tatewise]

Unfortunately, you cannot achieve that with a Query.

The best you can achieve is to sort the list of burials you have into Place order so that all the duplicates are adjacent.

The only ways to get a unique list are:
1) Export the Result Set to a spreadsheet and get it to filter down to unique rows.
2) Write a Plugin to create the Result Set of unique burial Place names.

[DavidNewton]

I don't know if this is suitable for what you want. Create an Individual query with a Column set of %INDI.BURI[1].PLAC>% (keep or delete the %INDI% column as you like); a Row condition of
Exclude if %INDI.BURI[1].PLAC>% is null
then the result set will be a list of the burial place records (with duplicates) now select the whole list and using the Edit menu turn it into a named list and I believe this will give you the list without duplicates.

David

[tatewise]

That is brilliantly ingenious David.
Yes, select the entire Place name column, and use Query Menu > Add Selected Cell Records to Named List.

[Gary Carson]

I did create a plug in that seems to work. It gets the list I need,

--[[
@Title: Cemetery Summary Report
@Author: Gary Carson
@LastUpdated: April 2016
@Description:
Counts and Lists All Burial Places in the File
]]

tblCemeteries = {} -- Define array for Burial Places
pi = fhNewItemPtr() -- declare pointer
pi:MoveToFirstRecord("INDI") -- point to the first Individual record
while not pi:IsNull() do
-- For each Person Add the Burial Place to the list
strCemetery = fhGetItemText(pi,'INDI.BURI[1].PLAC>')
if (tblCemeteries[strCemetery] == nil) then
tblCemeteries[strCemetery] = 1
else
tblCemeteries[strCemetery] = tblCemeteries[strCemetery] + 1
end
pi:MoveNext()
end
-- Build Tables for the result set columns for Burial Place and Qty
tblSurname = {}
tblcount = {}
ii = 0 -- line counter for result tables
for strCemetery, iQty in pairs(tblCemeteries) do
ii = ii + 1
tblSurname[ii] = strCemetery
tblcount[ii] = iQty
end
fhOutputResultSetTitles("Cemetery Summary Report",
"Cemetery Summary Report")
fhOutputResultSetColumn('Burial Place', 'text',tblSurname,ii,260,'align_left',1,true)
fhOutputResultSetColumn('Count', 'integer',tblcount,ii,30,'align_right',1,false)

[Gary Carson]

I did the query in the manner suggested and it also worked fine.

[tatewise]

Just in case you are interested, here are a few tips for refining the Plugin script.

Instead of while not pi:IsNull() do you can use while pi:IsNotNull() do

To update a dictionary of counts like tblCemeteries you can replace entire if then else end with:
tblCemeteries[strCemetery] = ( tblCemeteries[strCemetery] or 0 ) + 1
This works because if tblCemeteries[strCemetery] is empty/nil the or 0 option is used.

To populate the result list tables you do not need the ii index, just use:
table.insert( tblSurname, strCemetery )
table.insert( tblcount, iQty )

Then in fhOutputResultSetColumn use #tblSurname instead of ii as size of table.

[Gary Carson]

I made the changes to the following and also changed tblSurname to tblCemetery. That was a naming error.

Now how do I eliminate the null result and null count in the final Result Set?

--[[
@Title: Cemetery Summary Report
@Author: Gary Carson
@LastUpdated: April 2016
@Description:
Counts and Lists All Burial Places in the File
]]

tblCemeteries = {} -- Define array for Burial Places
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
pi:MoveNext()
end
-- Build Tables for the result set columns for Burial Place and Qty
tblCemetery = {}
tblcount = {}

for strCemetery, iQty in pairs(tblCemeteries) do
table.insert( tblCemetery, strCemetery )
table.insert( tblcount, iQty )
end
fhOutputResultSetTitles("Cemetery Summary Report",
"Cemetery Summary Report")
fhOutputResultSetColumn('Burial Place', 'text',tblCemetery,#tblCemetery,260,'align_left',1,true)
fhOutputResultSetColumn('Count', 'integer',tblcount,#tblCemetery,30,'align_right',1,false)

[tatewise]

The easiest way is to skip non-existent Burial Place names in the initial search.
fhGetItemText() returns an empty string if either the Burial Event or its Place name do not exist.

if strCemetery ~= "" then
tblCemeteries[strCemetery] = ( tblCemeteries[strCemetery] or 0 ) + 1
end

An equally valid test is to check the size/length of the string using the # operator:

if #strCemetery > 0 then
tblCemeteries[strCemetery] = ( tblCemeteries[strCemetery] or 0 ) + 1
end

[Gary Carson]

I would like to modify the following plug-in so the output columns would be Cemetery Name, City, County, State
My format for burial Place is Cemetery, City, County, State, Country with the number of links.


--[[
@Title: Cemetery Summary Report
@Author: Gary Carson
@LastUpdated: April 2016
@Description:
Counts and Lists All Burial Places in the File
]]

tblCemeteries = {} -- Define array for Burial Places
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
pi:MoveNext()
end
-- Build Tables for the result set columns for Burial Place and Qty
tbCemetery = {}
tblcount = {}

for strCemetery, iQty in pairs(tblCemeteries) do
table.insert( tbCemetery, 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('Count', 'integer',tblcount,#tbCemetery,30,'align_right',1,false)

[tatewise]

Use the Lua string.split() method to split Place text on commas into a table.
e.g.
tblPlace = strCemetery:split(",")
table.insert(tblName,tblPlace[1])
table.insert(tblCity,tblPlace[2])
etc...
See example in plugins:code_snippets:split_a_line_using_a_separator|> Split a Line using a Separator (code snippet).