* Custom Query Count

[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

[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.

[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")

[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!?!?