Page 1 of 1
Custom Query Count
Posted: 29 Mar 2015 00:29
by spacemeout
Hi
I'm a new user to FH, coming from the TMG fall out. And so far thing are going well.
I do have a small question about custom queries. I want to find how many individuals are in each FH family group. There's a nice standard query that list all the individuals with the group number, which is great. I've copied to a custom query but I find no way to aggregate (group by) a count on a field?
Thanks
Phil
Re: Custom Query Count
Posted: 29 Mar 2015 09:52
by Jane
It's not possible to aggregate lines with a Query, but there are a couple of options. You can save the results of the Query as a CSV file and open it in Excel (or other spreadsheet) and use a pivot table to quickly sum the rows, or alternately you could investigate writing a simple plug into the do the counts, the Surname summary plugin would be a good place to start.
Re: Custom Query Count
Posted: 29 Mar 2015 10:52
by spacemeout
I came to same conclusion, so wrote my second plugin in the 3 days I've been using FH.
Here it is for anyone to use. There maybe a more efficient way of doing this in lua, but I've only been using for 12 hours. It's a shame that FH doesn't support Python for plugin development.
Code: Select all
-- [[ Written by Phil Chandler 29 March 2015]]
pi = fhNewItemPtr() -- declare pointer
pi:MoveToFirstRecord('INDI') -- set the first to point to the first Individual record
pi2 = fhNewItemPtr()
-- Define Columns
tblSurname = {}
tblResPool = { }
tblResPoolCount = { }
intResPoolCount = 0
while not pi:IsNull() do
-- Do Work Here
if not pi:IsNull() then
-- Get All Names from Name Fields
pi2:MoveTo(pi,"~.NAME") -- Get the Name for the INDI Record.
if ( not pi2:IsNull()) then
-- Use Relation Pool ID as the key for table variables
intResPool = fhCallBuiltInFunction("RelationPool", pi)
if ( tblResPoolCount[intResPool] == nil ) then
tblResPoolCount[ intResPool ] = 1
tblSurname[intResPool] = fhGetItemText(pi2,'~:SURNAME')
tblResPool [ intResPool ] = intResPool
else
tblResPoolCount[ intResPool ] = tblResPoolCount[ intResPool ] + 1
end
end
end
pi:MoveNext() -- next individual record
end
fhOutputResultSetTitles("List an aggregate count of Relation Pool by Relation Pool ID",
"List an aggregate count of Relation Pool by Relation Pool ID",
"Printed: %#x")
fhOutputResultSetColumn("Surname", "text", tblSurname, #tblSurname, 60, "align_left", 1, true)
fhOutputResultSetColumn("Res Pool", "integer", tblResPool, #tblResPool, 160, "align_left")
fhOutputResultSetColumn("Res Pool Count", "integer", tblResPoolCount, #tblResPoolCount, 160, "align_left")
Re: Custom Query Count
Posted: 29 Mar 2015 21:27
by tatewise
You could run the Show Project Statistics Plugin, which lists Pool sizes.
All of them if up to six Pools, or largest and smallest otherwise.
The Plugin may also reveal other things you did not know!?!?