Number in household?

Discussions around using and interfacing with the Church MLS program.
fishyjim
New Member
Posts: 2
Joined: Sat Oct 15, 2011 11:04 pm

Number in household?

Postby fishyjim » Sat Oct 15, 2011 11:09 pm

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: 6526
Joined: Sun Mar 16, 2008 11:30 pm
Location: USA, TX

Postby jdlessley » Sun Oct 16, 2011 8:36 am

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 LDS.org Help Center page or the LDSTech wiki?

jonesrk
Church Employee
Church Employee
Posts: 1454
Joined: Tue Jun 30, 2009 7:12 am
Location: Farmington, UT, USA

Postby jonesrk » Sun Oct 16, 2011 3:00 pm

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.
Ryan Jones
CDOL Developer
Stake Technology Specialist - Software / Stake Assistant Clerk
Former Ward Clerk

kisaac
Community Moderators
Posts: 1170
Joined: Sun Oct 21, 2007 5:04 am
Location: Utah, united states

Postby kisaac » Mon Oct 17, 2011 3:12 am

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: 24
Joined: Mon Feb 07, 2011 10:20 am
Location: Palm Springs, CA, USA

Postby ckellsworth » Mon Oct 17, 2011 11:21 am

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 79 times

kisaac
Community Moderators
Posts: 1170
Joined: Sun Oct 21, 2007 5:04 am
Location: Utah, united states

Postby kisaac » Mon Oct 17, 2011 1:04 pm

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: Sat Oct 15, 2011 11:04 pm

Postby fishyjim » Wed Oct 26, 2011 6:33 pm

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!


Return to “MLS Support, Help, and Feedback”

Who is online

Users browsing this forum: No registered users and 1 guest