Transcript:
0:00 hello everyone uh my name is Patrick um
0:03 I'm going to talk to you today about the
0:04 Idaho uh Department of Health and
0:06 Welfare specifically their food
0:08 assistance program so um food stamps
0:11 food assistance is available in the US
0:14 um for People based on a v variety of
0:17 criteria so in this particular instance
0:20 the criteria we have here is in order to
0:22 be eligible for food assistance uh you
0:25 have to be either citizen or legal
0:26 immigrant within the US you must be
0:29 resident in the state of vaho your
0:31 household income and actually just going
0:33 to draw attention to that your household
0:35 income not your personal income must be
0:37 less than the program limits for your
0:39 household size which are listed here on
0:41 the right and then with some exceptions
0:44 obviously you must be uh in work or in
0:46 some kind of training um program Etc so
0:50 some of this isn't that complicated for
0:52 example citizen or legal immigrant
0:54 that's basically an options set value um
0:57 you know same for are you in employment
1:00 AR training program
1:02 Etc but here now for the household
1:04 income so it's on the right hand side
1:06 the menu here to display so for a
1:09 household income of one your gross
1:11 income must be less than
1:14 $1,276 for two etc for three for four
1:17 for five so as you can see it list
1:19 values up as far as eight residents and
1:22 then at eight residents it's $
1:24 4,430 is a household income and then for
1:27 every additional person living there 9
1:30 10 11 you add an extra $451 on so just
1:35 going to go on and show you how we have
1:37 that implemented
1:39 here so I food
1:46 assistance so we have
1:48 um obviously a decision table and then
1:51 we're going to have a set of
1:53 calculations and then it uh we actually
1:56 then go back and have a look there's one
1:58 extra requirement that came in lat later
2:00 on so after we detail the the generic
2:03 stuff sorry the more easier stuff we
2:05 come back and go into the the more
2:06 complex version so this is a very quick
2:10 overview of what the decision table is
2:12 like so as you can see immigration
2:14 status here as it's a de or an option
2:17 set what we have here is we have in this
2:20 is actually two options so what we're
2:22 seeing is here for immigration status
2:24 you want to this column to be true for
2:26 one of a better term if they're either a
2:28 legal immigrant or a US
2:32 citizen then we have here same again on
2:35 employment status there must be either
2:37 part-time employed full-time employed or
2:39 un approved training course and again
2:41 these are option set values so this will
2:45 this will evaluate the true if they're
2:46 in either of
2:48 those then we have I'm just going to go
2:50 back and go through calculations very
2:51 quickly so resident Idaho so the the
2:55 model that we used for this was that we
2:57 converted the contact entity into a
3:00 resident just change the name of it
3:02 added a few Fields the same then for the
3:04 account we changed it into a household
3:08 density so over here the formula just
3:12 going to open it up now we can have a
3:13 look in the
3:17 color so it's an in Upper find Value Etc
3:22 basically all that's doing is it's going
3:24 out to the associated primary residence
3:26 which is a lookup field for the accounts
3:29 returning back the address one state or
3:31 Province code um doing an upper which
3:34 basically turns it all into capital
3:36 letters and then we just see this
3:39 function will return true if the state
3:41 or province is either ID as in ID is
3:44 typed in or Idaho and full is typed in
3:47 there the next one we do a find
3:53 sum so basically this is a very simple
3:56 one all we do is we go off and we find
3:58 any contacts where their primary
4:00 residents is listed as the the current
4:04 um resident for one better term so sorry
4:08 put this one up
4:11 here so find some got all the contacts
4:14 that are living in the same property and
4:15 we get their monthly income some it Al
4:18 together it just comes back as a number
4:19 value and this value here it says zero
4:22 just people who might not be familiar
4:24 with North52 that's just uh where you can
4:26 set your own default value nice and
4:28 simple
4:31 and then finally this
4:36 guy we just do a fine count on all of
4:39 the contacts that are currently resident
4:41 in that premise it's all it is so
4:44 anybody is resident there this will tell
4:46 us how many people are there so are
4:48 three calculations if the person's
4:50 resident in Nido will return it true or
4:52 false household income will actually
4:53 give us a numeric value and household
4:55 residents will give us a numeric value
4:57 of the actual people living there so you
5:00 go back to the decision
5:01 table we expand out and we go to
5:05 calculations so as you can see because
5:07 they're listed as calculations they're
5:09 now available here in calculations on
5:10 the left hand side so we have here
5:13 resident Idaho and true or false um
5:18 their household income again we just the
5:21 Valu is returned here and we just said
5:23 if there's currently one person or less
5:26 well in this instance one person there
5:28 has to be maximum hous income allow for
5:30 that property
5:32 1,276 um looks like I actually um here
5:35 we are then for two it's 1,726 Etc but
5:40 now if we go
5:42 back here we can see that for each
5:45 additional member it's
5:48 $451 so we have to add that so we handle
5:52 that right here with this guy it's very
5:54 simple line here so what we say is that
5:58 if the number of tostal residence in the
6:00 property is greater than
6:04 eight then what we do is we take the
6:06 value 4430 because that's the the max
6:10 number of uh the maximum allowed income
6:13 for for eight people and then we deduct
6:17 the number eight from the current number
6:19 of household residents and then multiply
6:22 that by 451 and then add it so basically
6:25 it sounds complicated but basically all
6:26 we're doing is saying imagine there is
6:28 10 people living this
6:30 property so the the income limit for
6:32 eight people is
6:35 4,430 now we have to add on an extra
6:38 $451 for each extra resident so that's
6:41 10 residents minus 8 gives us two
6:44 multiply by
6:45 $451 gives us
6:48 $92 and then we add that to the
6:52 4,430 now that actually expands out as
6:54 well so there might be 20 people living
6:56 there 30 people it's just a calculation
6:59 because we use the inherent Excel
7:01 formatting and the inherent Excel
7:03 actions it's very easy to add that kind
7:05 of logic into an North52 decision
7:08 table so just going to go back now here
7:11 to the risk factors or sorry to the uh
7:13 food assistance because there is an
7:15 additional requirement
7:17 here so effective July 21 resources must
7:21 be under $5,000 to qualify for food
7:23 stamps and then a whole list of
7:25 resources BH or sorry cash uh bank
7:28 accounts stocks bonds Etc so for the
7:32 purpose of this we didn't actually input
7:34 all of these types but we did just
7:35 create another one called savings so I'm
7:38 just going to do is go back and very
7:39 quickly update the existing decision
7:42 table and put in that that
7:45 calculation
7:48 so second
7:54 that
7:58 so first thing we're just going to add
8:00 in is under the calculations subab a new
8:03 calculation called current
8:07 date just going to use the existing nor
8:09 52 formula uh UTC
8:13 date so that's going to return back the
8:16 um the date for everyone uh then we're
8:19 just going to do add in the value in
8:21 July
8:24 2011 so we're just going to use the
8:27 create a formula
8:35 and then current resources now as I was
8:38 saying we only added in one field the
8:40 actual savings here
8:44 um for all the in a more complex
8:48 environment you would have obviously
8:49 added in each of the
8:50 individual um you know Banks uh bank
8:53 accounts stocks bonds Etc so what we've
8:57 done now is we've had a three additional
9:00 calculations current date is going to
9:01 tell us what our current date actually
9:03 is when the decision table runs July
9:05 2011 is just basically the date July
9:08 2011 and then current resources similar
9:11 to when we did a fine sum on the
9:12 household income the current resources
9:15 does a fine sum uh for all this current
9:17 savings just rather than the monthly
9:19 income this
9:20 time so we go back to the decision
9:23 table and we start making some changes
9:26 here so I'm going need to add in three
9:28 additional colume
9:30 three additional conditions so condition
9:34 one condition
9:35 two and condition
9:38 three so the first one we're going to
9:41 add in is uh what the current date
9:46 is so if the current date is less than
9:50 or equal
9:52 to um this guy one second
9:58 here
10:16 2011
10:22 okay we want the current dat to be less
10:24 than or equal to July
10:26 2011 and just going to very quickly add
10:30 that to all of the existing
10:33 requirements so now what's this what's
10:36 going to happen here is at the moment
10:38 the way it's l out is that you have to
10:40 be legal immigrant our US citizen and
10:43 resident of Idaho and have the correct
10:45 employment status and your housing
10:46 income has to be below limit and and the
10:49 date needs to be before after July
10:52 2011 so that's fine in some instances
10:56 but it's not going to work in all
10:57 instances cuz OB see it's no longer July
11:00 2011 so what we're going to do is add in
11:03 another one here I'm going to say after
11:06 July
11:08 2011 and drag it
11:11 down
11:13 so as per the requirements after July
11:16 2011 your current resources have to be
11:18 less than
11:19 $5,000 so just going to add in current
11:23 resources
11:28 here
11:30 there we
11:39 are
11:46 okay
11:48 so right now we have what is essentially
11:51 in non nonfunctional decision table
11:54 because it has two rules one that says
11:56 it has to be before July 2011 and the
11:58 other one says you have to be after July
12:00 2011 so obviously it's not exactly what
12:02 we're looking for here so we're going to
12:04 we're going to put an or condition on
12:05 these so the way we want this decision
12:07 table actually read is that after it's
12:10 evaluated everything on the left over as
12:11 far as household income we want to check
12:14 is the current date before July 2011 or
12:18 if it is after July 2011 is the current
12:20 resources less than $5,000 as well so
12:24 this is where we go in here we
12:26 add so I can do add toggle column R so
12:30 the first time I do this what happens
12:32 now is it says um it's household income
12:36 less than
12:38 1,276 or is the date below less than
12:41 July 2011 or is the date after July 2011
12:43 or is the current resources less than
12:45 5,000 it's making progress but it's not
12:48 exactly what we're looking for so when
12:50 to come along and hit toggle call you
12:51 more
12:52 again so this is going to create what we
12:54 call a compound or so this guy we want
12:58 to be by himself
12:59 cuz he's not part of the other two
13:02 conditions both of these guys we're just
13:05 going to
13:06 add the number two to them what that
13:09 means is we're telling the decision
13:10 table that we want both of these
13:12 calculations to be evaluated
13:16 together so now when the decision table
13:19 is going to execute this kind of logic
13:21 what I will actually do is it'll check
13:23 are they a legal immigrant or a US
13:25 citizen are they resent in Idaho are
13:28 they part-time employed Etc are their
13:31 household income limits acceptable and
13:34 then they'll
13:35 say and is the date before July
13:40 2011 or if the date is after July 2011
13:43 how are they also less than $5,000 in
13:45 current resources so we can very quickly
13:48 and easily add in quite complex
13:51 functionality quite complex
13:54 um as I like ores ands in all kinds of
13:58 formats to just about any kind of
14:00 business rule and
14:02 requirement now um that was possibly a
14:04 little bit complex but does anybody have
14:06 any questions on it or want to see
14:15 anything okay in that instance um I
14:18 might I just pass you off back to
14:23 brusso