Number in household?

Discussions around using and interfacing with the Church MLS program.
Locked
fishyjim
New Member
Posts: 2
Joined: Sun Oct 16, 2011 12:04 am

Number in household?

#1

Post by fishyjim »

I just spent 4 hours valuing the user defined field with the number of people in the household for our disaster response lists. Does anyone know a better way to accomplish this? MLS knows the individuals in each household, but I could not find a field that would count them for me... Any ideas would be appreciated.
jdlessley
Community Moderators
Posts: 9833
Joined: Mon Mar 17, 2008 12:30 am
Location: USA, TX

#2

Post by jdlessley »

There are a lot of calculations that could be derived from MLS data that are not done. This would just be one. If the built-in reports and the custom reports do not provide satisfactory results, you must be creative. Perhaps if you could explain why having the number of household members is needed then there may be another approach to getting the data you need.
JD Lessley
Have you tried finding your answer on the ChurchofJesusChrist.org Help Center or Tech Wiki?
jonesrk
Church Employee
Church Employee
Posts: 2361
Joined: Tue Jun 30, 2009 8:12 am
Location: South Jordan, UT, USA

#3

Post by jonesrk »

On thing I did to find the number of children was to run the Abbreviated Directory of Members and select show children of each household. Then I copied the data to Excel and on the children lines I used

(cell e48) =IF(C48="","",LEN(C48)-LEN(SUBSTITUTE(C48,",",""))+1)

basically that counts the commas and adds one. To get the whole family count you would need to check the parents for & to count it as one or two.

I actually put all of the family on one line with these two formulas
(cell b48) =IF(LEFT(A48,6)=" ","",A48) grabs the parents only
(cell c48) =IF(LEFT(A49,6)=" ",TRIM(A49),"") grabs the children from below the parents
I had just then names column from the report in column A.
kisaac
Community Moderators
Posts: 1184
Joined: Sun Oct 21, 2007 6:04 am
Location: Utah, united states

#4

Post by kisaac »

fishyjim wrote:I just spent 4 hours valuing the user defined field...
Welcome to the forum! I applaud your efforts, but question if it will be a sustainable effort after a few months of move-ins, or when you get released. I'm not saying "give up," I'm suggesting the more complex you make your system, the harder it will be to keep it going month after month, and year after year. If the house count is vital to you, and I can see the value of it for emergency uses, then perhaps JonesRK's spreadsheet calculations would help, but you still have HOURS of typing them back in to MLS.

Yes, MLS does know the individuals, and almost counts them in a field called HH Order (Household Order) when you do a membership export...almost!

We do our fast offerings routes using user defined fields. Works great, just takes time EVERY month to update the move ins, or it quickly becomes out-dated, and unusable. The advantage we have is we know when the fast offering day is, so I know when to update my routes. You won't know...
ckellsworth
New Member
Posts: 30
Joined: Mon Feb 07, 2011 10:20 am
Location: Palm Springs, CA, USA

#5

Post by ckellsworth »

here is another option of generating the # of people in a household.

Get a report that is in the following format:
<head of household>, <household member>

You then can use Pivot tables in Excel to built a report that is:
<head of household>, <count of head of household>

attached is a quick proof of concept of how the input data should look, setup of the pivot table and how the output would look. The actual steps to do this will depend on what spreadsheet program your using. this screenshot was done using Neooffice, though i did test it in Excel.
Screen Shot 2011-10-17 at 10.49.38 .jpg
Screen Shot 2011-10-17 at 10.49.38 .jpg (23.34 KiB) Viewed 531 times
kisaac
Community Moderators
Posts: 1184
Joined: Sun Oct 21, 2007 6:04 am
Location: Utah, united states

#6

Post by kisaac »

JonesRK wrote:On thing I did...
ckellsworth wrote:here is another option...
I will say a huge "thank you" to these posters and the many others here...this probably represents some real time and effort to research this question, and may be beneficial to many. I know I would never have thought of similar methods. These posters, and all others who spend time here helping others, is the true benefit of a forum like this.
fishyjim
New Member
Posts: 2
Joined: Sun Oct 16, 2011 12:04 am

#7

Post by fishyjim »

Thank you for these ideas - I think the pivot table is probably what I will look into next (I agree that the solution is most likely outside of MLS)... To answer the question earlier, our Stake is asking us to report number of individuals accounted for, so our block captains can simply note which households they visited, and we can let the spreadsheet do the counting/adding for us... Thanks again!
Locked

Return to “MLS Support, Help, and Feedback”