agency-general Mailing List for AGENCY Software (Page 2)
AGENCY is a Free Software Data System by and for Nonprofits and others
Brought to you by:
ktanzer
You can subscribe to this list here.
2009 |
Jan
|
Feb
|
Mar
|
Apr
|
May
|
Jun
|
Jul
|
Aug
|
Sep
|
Oct
|
Nov
(10) |
Dec
(15) |
---|---|---|---|---|---|---|---|---|---|---|---|---|
2010 |
Jan
(11) |
Feb
(32) |
Mar
(46) |
Apr
(32) |
May
(53) |
Jun
(48) |
Jul
(8) |
Aug
(8) |
Sep
(9) |
Oct
(16) |
Nov
(17) |
Dec
(30) |
2011 |
Jan
(16) |
Feb
(51) |
Mar
(68) |
Apr
(30) |
May
(26) |
Jun
(19) |
Jul
(6) |
Aug
(6) |
Sep
(6) |
Oct
(7) |
Nov
(7) |
Dec
(10) |
2012 |
Jan
(1) |
Feb
(7) |
Mar
(8) |
Apr
(11) |
May
(6) |
Jun
(16) |
Jul
(1) |
Aug
(1) |
Sep
(5) |
Oct
(16) |
Nov
(5) |
Dec
(4) |
2013 |
Jan
(15) |
Feb
(3) |
Mar
(9) |
Apr
(18) |
May
(5) |
Jun
(11) |
Jul
(4) |
Aug
(10) |
Sep
(1) |
Oct
(1) |
Nov
(1) |
Dec
|
2014 |
Jan
(1) |
Feb
(5) |
Mar
(4) |
Apr
(2) |
May
(9) |
Jun
(5) |
Jul
(6) |
Aug
(4) |
Sep
(9) |
Oct
(3) |
Nov
(1) |
Dec
|
2015 |
Jan
|
Feb
|
Mar
(12) |
Apr
(5) |
May
(1) |
Jun
(2) |
Jul
|
Aug
|
Sep
|
Oct
(1) |
Nov
(4) |
Dec
(1) |
2017 |
Jan
|
Feb
|
Mar
|
Apr
|
May
|
Jun
|
Jul
|
Aug
|
Sep
|
Oct
|
Nov
|
Dec
(1) |
2018 |
Jan
|
Feb
|
Mar
|
Apr
|
May
|
Jun
|
Jul
|
Aug
(1) |
Sep
|
Oct
(8) |
Nov
(5) |
Dec
|
From: Michael J. <mj...@pl...> - 2015-04-13 20:32:52
|
Hi Ken, We split client loads in SPC Agency up by the last name of the client. In our big template query, that currently looks like this: (SELECT staff_name( CASE WHEN c.name_last BETWEEN 'A' AND 'M' THEN 18 ELSE 6 END )) AS staff_certifier They want to even out the load, so that anyone with a last name beginning with "Li" would go to one certifier, and anything past that in the alphabet would go to the other. Can you think of a way to do this, or is this just too much? Thanks, Mike |
From: Ken T. <ken...@gm...> - 2015-04-01 20:24:13
|
I was going to suggest using the facility field, but you beat me to it. Looks right to me! On Wed, Apr 1, 2015 at 9:48 AM, Michael James <mj...@pl...> wrote: > Correction: > > > > Match Facility Field: incident_location_code > > > > *From:* Michael James > *Sent:* Wednesday, April 01, 2015 9:27 AM > *To:* Ken Tanzer (ken...@gm...); > age...@li... > *Subject:* Alert notification clean-up > > > > Hi Ken, > > > > I’m starting the process of weeding out alert notifications for individual > staff members. As an example, I want to notify any Building Specialist if > an Incident record has been added that references the building they work > in. Am I getting all the fields correct here? > > > > Alert Object: > > Incident (incident) > > Alert Notify Record Action: > > Insert/Add (INSERT) > > Staff Position: > > Building Specialist (BLDG_SPEC) > > Match Program Field: > > incident_location_code > > > > Any new incident record will now trigger an alert for any Building > Specialist that works at the incident’s location, correct? > > > > Thanks, > > Mike > -- AGENCY Software A Free Software data system By and for non-profits *http://agency-software.org/ <http://agency-software.org/>* *https://agency-software.org/demo/client <https://agency-software.org/demo/client>* ken...@ag... (253) 245-3801 Subscribe to the mailing list <age...@li...?body=subscribe> to learn more about AGENCY or follow the discussion. |
From: Michael J. <mj...@pl...> - 2015-04-01 16:48:10
|
Correction: Match Facility Field: incident_location_code From: Michael James Sent: Wednesday, April 01, 2015 9:27 AM To: Ken Tanzer (ken...@gm...); age...@li... Subject: Alert notification clean-up Hi Ken, I'm starting the process of weeding out alert notifications for individual staff members. As an example, I want to notify any Building Specialist if an Incident record has been added that references the building they work in. Am I getting all the fields correct here? Alert Object: Incident (incident) Alert Notify Record Action: Insert/Add (INSERT) Staff Position: Building Specialist (BLDG_SPEC) Match Program Field: incident_location_code Any new incident record will now trigger an alert for any Building Specialist that works at the incident's location, correct? Thanks, Mike |
From: Michael J. <mj...@pl...> - 2015-04-01 16:27:33
|
Hi Ken, I'm starting the process of weeding out alert notifications for individual staff members. As an example, I want to notify any Building Specialist if an Incident record has been added that references the building they work in. Am I getting all the fields correct here? Alert Object: Incident (incident) Alert Notify Record Action: Insert/Add (INSERT) Staff Position: Building Specialist (BLDG_SPEC) Match Program Field: incident_location_code Any new incident record will now trigger an alert for any Building Specialist that works at the incident's location, correct? Thanks, Mike |
From: Ken T. <ken...@gm...> - 2015-03-26 17:52:17
|
Hey Mike. Unduplication is a two-step process. First, you use that menu option to "mark" two clients as duplicates of each other. After that, there is a second step where you tell it to actually do the unduplication. For the client records, since there will only be one left after unduplicating, it will prompt you as to which fields to keep if the two records conflict. It will also merge all the child records into the remaining client. In some cases, you might then end up with some extra records you need to delete. (For example, two duplicate clients both have the same ethnicity record. You will then end up with two, and want to delete one of them.) I haven't used this in quite a while, so had to go and look at the code. It looks like all the child records that are engine tables will be automatically unduplicated. It's possible we though doubtful that we have an oddball table that wouldn't be included. My suggestion would be to process 1 set of duplicates first and see how it goes and if there are any problems before doing the bigger batch. Cheers, Ken On Tue, Mar 24, 2015 at 10:03 AM, Michael James <mj...@pl...> wrote: > Hi Ken, > > > > I’ve got about twenty clients that we imported into SPC Agency that are > duplicates. What exactly does “unduplication” under the Agency menu > accomplish? Will it merge child records from one into the other, or do I > need to do that manually before unduplicating? > > > > Thanks, > > Mike > -- AGENCY Software A Free Software data system By and for non-profits *http://agency-software.org/ <http://agency-software.org/>* *https://agency-software.org/demo/client <https://agency-software.org/demo/client>* ken...@ag... (253) 245-3801 Subscribe to the mailing list <age...@li...?body=subscribe> to learn more about AGENCY or follow the discussion. |
From: Michael J. <mj...@pl...> - 2015-03-24 17:03:15
|
Hi Ken, I've got about twenty clients that we imported into SPC Agency that are duplicates. What exactly does "unduplication" under the Agency menu accomplish? Will it merge child records from one into the other, or do I need to do that manually before unduplicating? Thanks, Mike |
From: Michael J. <mj...@pl...> - 2015-03-23 18:25:21
|
That’s exactly what I wanted, thanks! Mike From: Ken Tanzer [mailto:ken...@gm...] Sent: Monday, March 23, 2015 10:49 AM To: Michael James Cc: age...@li... Subject: Re: Appending text in an Agency field Hey Mike. I think you want to use this: SET sys_log=COALESCE(sys_log||E'\n','') || 'Add your new note here' It's a bit cumbersome, but it ensures that each comment added to the sys_log gets its own line. Cheers, Ken On Mon, Mar 23, 2015 at 9:00 AM, Michael James <mj...@pl...<mailto:mj...@pl...>> wrote: Hi Ken, I need to make a batch of minor changes to some fields, and I’d like to add a note in the sys_log of each record explaining what happened. I don’t want to write over anything that may already be there. I’ve seen you append notes like that but don’t remember the code you used and can’t find any examples. Could you help me out? Thanks, Mike -- [http://agency-software.org/demo/client/images/agency_logo_small.png] AGENCY Software A Free Software data system By and for non-profits http://agency-software.org/ https://agency-software.org/demo/client ken...@ag...<mailto:ken...@ag...> (253) 245-3801 Subscribe to the mailing list<mailto:age...@li...?body=subscribe> to learn more about AGENCY or follow the discussion. |
From: Ken T. <ken...@gm...> - 2015-03-23 17:50:16
|
Hey Mike. I think you want to use this: SET sys_log=COALESCE(sys_log||E'\n','') || 'Add your new note here' It's a bit cumbersome, but it ensures that each comment added to the sys_log gets its own line. Cheers, Ken On Mon, Mar 23, 2015 at 9:00 AM, Michael James <mj...@pl...> wrote: > Hi Ken, > > > > I need to make a batch of minor changes to some fields, and I’d like to > add a note in the sys_log of each record explaining what happened. I don’t > want to write over anything that may already be there. I’ve seen you > append notes like that but don’t remember the code you used and can’t find > any examples. Could you help me out? > > > > Thanks, > > Mike > -- AGENCY Software A Free Software data system By and for non-profits *http://agency-software.org/ <http://agency-software.org/>* *https://agency-software.org/demo/client <https://agency-software.org/demo/client>* ken...@ag... (253) 245-3801 Subscribe to the mailing list <age...@li...?body=subscribe> to learn more about AGENCY or follow the discussion. |
From: Michael J. <mj...@pl...> - 2015-03-23 16:00:44
|
Hi Ken, I need to make a batch of minor changes to some fields, and I'd like to add a note in the sys_log of each record explaining what happened. I don't want to write over anything that may already be there. I've seen you append notes like that but don't remember the code you used and can't find any examples. Could you help me out? Thanks, Mike |
From: Michael J. <mj...@pl...> - 2015-03-16 20:22:45
|
This is still giving any move-ins during the selected time period, as opposed to first move-ins. Pick almost any client at random, especially toward the top of the list, and you'll find tenants on their sixth building. Would it help to bring in their SPC registration date? I can't think of a way to do that without setting an arbitrary time between registration and move-in. ________________________________ From: Ken Tanzer [ken...@gm...] Sent: Wednesday, March 11, 2015 4:38 PM To: Michael James Cc: age...@li... Subject: Re: First move-in report? Hey Mike. I don't think that DISTINCT ON (residence_date) will get you where you want to be. If you do that, it's going to filter out multiple records with the same move in date, so for example on the first of any month you'd only see one movein. DISTINCT ON (client_id) is definitely what you want, since that will filter out multiple records for the same client. I'm not sure what issue you encountered with the DISTINCT ON (client_id). I went ahead and tried it on your system, and the SQL I sent didn't work, because the last line said order by residence_date, but the subquery had already renamed that to move_in. So that last line should read ORDER BY move_in. Try this query first without, and then with, the DISTINCT ON (client_id). When you run it without DISTINCT, you'll see the 5th & 6th rows are the same client, and with the DISTINCT you only get one row for them. I'm pretty sure that's what you want, but let me know if I'm missing something. Cheers, Ken SELECT DISTINCT ON (client_id) * FROM ( SELECT client_id,ro.housing_project_code,housing_unit_label AS unit,residence_date AS move_in,residence_date_end AS move_out FROM residence_own ro LEFT JOIN l_housing_project USING (housing_project_code) LEFT JOIN housing_unit USING (housing_unit_code) WHERE residence_date BETWEEN '1/1/14' AND '12/31/14' ORDER BY client_id,residence_date ) foo ORDER BY client_id,move_in; On Wed, Mar 11, 2015 at 11:20 AM, Michael James <mj...@pl...<mailto:mj...@pl...>> wrote: I changed your “DISTINCT ON” to grab the residence_date instead of the client_id, and that got me closer: SELECT DISTINCT ON (residence_date) residence_date AS move_in, client_id, ro.housing_project_code,housing_unit_label AS unit FROM residence_own ro LEFT JOIN l_housing_project USING (housing_project_code) LEFT JOIN housing_unit USING (housing_unit_code) WHERE residence_date BETWEEN '$sdate' AND '$edate' ORDER BY residence_date,client_id It’s showing brand new SPC tenants *except* for the first to clients returned by the query. They’ve been around a while. Any ideas? From: Ken Tanzer [mailto:ken...@gm...<mailto:ken...@gm...>] Sent: Tuesday, March 10, 2015 12:52 PM To: Michael James Cc: age...@li...<mailto:age...@li...> Subject: Re: First move-in report? This is your current SQL: SELECT client_id,ro.housing_project_code,housing_unit_label AS unit,residence_date AS move_in,residence_date_end AS move_out FROM residence_own ro LEFT JOIN l_housing_project USING (housing_project_code) LEFT JOIN housing_unit USING (housing_unit_code) WHERE $which BETWEEN '$sdate' AND '$edate' This SQL would only get the first moveins: SELECT DISTINCT ON (client_id) client_id,ro.housing_project_code,housing_unit_label AS unit,residence_date AS move_in,residence_date_end AS move_out FROM residence_own ro LEFT JOIN l_housing_project USING (housing_project_code) LEFT JOIN housing_unit USING (housing_unit_code) WHERE $which BETWEEN '$sdate' AND '$edate' ORDER BY client_id,residence_date If you want a separate report, you can just run with that. Or you could add a new option to the report: PICK first_only "All moves?" "" "Showing all moves" "DISTINCT ON (client_id)" "Showing only one move per client" ENDPICK And then your SQL can become: SELECT $first_only client_id,ro.housing_project_code,housing_unit_label AS unit,residence_date AS move_in,residence_date_end AS move_out FROM residence_own ro LEFT JOIN l_housing_project USING (housing_project_code) LEFT JOIN housing_unit USING (housing_unit_code) WHERE $which BETWEEN '$sdate' AND '$edate' ORDER BY client_id,residence_date The only issue then is that your report is now sorted by client. Currently, there is no sort specified, but it looks like it's coming out by residence_date. So as a final step, you could wrap the whole thing as a subselect and restore the residence_date order: SELECT * FROM ( SELECT $first_only client_id,ro.housing_project_code,housing_unit_label AS unit,residence_date AS move_in,residence_date_end AS move_out FROM residence_own ro LEFT JOIN l_housing_project USING (housing_project_code) LEFT JOIN housing_unit USING (housing_unit_code) WHERE $which BETWEEN '$sdate' AND '$edate' ORDER BY client_id,residence_date ) foo ORDER BY residence_date I think that will all work as described, but I haven't tested it. So let me know if it doesn't work, or if you have any questions about it. Thanks. Ken On Tue, Mar 10, 2015 at 12:31 PM, Michael James <mj...@pl...<mailto:mj...@pl...>> wrote: I’d be happy with #1 or #3. I think the existing report is very useful as it is. Mike From: Ken Tanzer [mailto:ken...@gm...<mailto:ken...@gm...>] Sent: Tuesday, March 10, 2015 12:12 PM To: Michael James Cc: age...@li...<mailto:age...@li...> Subject: Re: First move-in report? Hey Mike. Which of these are you wanting? 1) A separate report to only list first moveins 2) This report changed to only list first moveins 3) An option for this report, so it could report as it does now, or optionally only list first moveins? Let me know. Thanks. Ken On Tue, Mar 10, 2015 at 9:42 AM, Michael James <mj...@pl...<mailto:mj...@pl...>> wrote: Hi Ken, We’ve got a move-in/move-out report in SPC that works well (see variable and code block below). There’s a lot of tenant movement, so if you pick a two-week stretch you’ll likely see a dozen or so folks who’ve moved from one building to another. Is there an easy way to get a report of people moving into their first unit during the chosen time period? It would be useful to see who is moving in for the first time. Thanks, Mike ----- DATE sdate "Start date" DATE edate "End date" PICK which "Move-ins or Move-outs?" "residence_date" "Move ins" "residence_date_end" "Move outs" ENDPICK SELECT client_id,ro.housing_project_code,housing_unit_label AS unit,residence_date AS move_in,residence_date_end AS move_out FROM residence_own ro LEFT JOIN l_housing_project USING (housing_project_code) LEFT JOIN housing_unit USING (housing_unit_code) WHERE $which BETWEEN '$sdate' AND '$edate' -- [X] AGENCY Software A Free Software data system By and for non-profits http://agency-software.org/ https://agency-software.org/demo/client ken...@ag...<mailto:ken...@ag...> (253) 245-3801<tel:%28253%29%20245-3801> Subscribe to the mailing list<mailto:age...@li...?body=subscribe> to learn more about AGENCY or follow the discussion. -- [X] AGENCY Software A Free Software data system By and for non-profits http://agency-software.org/ https://agency-software.org/demo/client ken...@ag...<mailto:ken...@ag...> (253) 245-3801<tel:%28253%29%20245-3801> Subscribe to the mailing list<mailto:age...@li...?body=subscribe> to learn more about AGENCY or follow the discussion. -- [X] AGENCY Software A Free Software data system By and for non-profits http://agency-software.org/ https://agency-software.org/demo/client ken...@ag...<mailto:ken...@ag...> (253) 245-3801 Subscribe to the mailing list<mailto:age...@li...?body=subscribe> to learn more about AGENCY or follow the discussion. |
From: Ken T. <ken...@gm...> - 2015-03-11 23:39:32
|
Hey Mike. I don't think that DISTINCT ON (residence_date) will get you where you want to be. If you do that, it's going to filter out multiple records with the same move in date, so for example on the first of any month you'd only see one movein. DISTINCT ON (client_id) is definitely what you want, since that will filter out multiple records for the same client. I'm not sure what issue you encountered with the DISTINCT ON (client_id). I went ahead and tried it on your system, and the SQL I sent didn't work, because the last line said order by residence_date, but the subquery had already renamed that to move_in. So that last line should read ORDER BY move_in. Try this query first without, and then with, the DISTINCT ON (client_id). When you run it without DISTINCT, you'll see the 5th & 6th rows are the same client, and with the DISTINCT you only get one row for them. I'm pretty sure that's what you want, but let me know if I'm missing something. Cheers, Ken SELECT DISTINCT ON (client_id) * FROM ( SELECT client_id,ro.housing_project_code,housing_unit_label AS unit,residence_date AS move_in,residence_date_end AS move_out FROM residence_own ro LEFT JOIN l_housing_project USING (housing_project_code) LEFT JOIN housing_unit USING (housing_unit_code) WHERE residence_date BETWEEN '1/1/14' AND '12/31/14' ORDER BY client_id,residence_date ) foo ORDER BY client_id,move_in; On Wed, Mar 11, 2015 at 11:20 AM, Michael James <mj...@pl...> wrote: > I changed your “DISTINCT ON” to grab the residence_date instead of the > client_id, and that got me closer: > > > > SELECT > DISTINCT ON (residence_date) residence_date AS move_in, > client_id, > ro.housing_project_code,housing_unit_label AS unit > FROM residence_own ro LEFT JOIN l_housing_project USING > (housing_project_code) LEFT JOIN housing_unit USING (housing_unit_code) > WHERE > residence_date BETWEEN '$sdate' AND '$edate' > ORDER BY residence_date,client_id > > > > It’s showing brand new SPC tenants **except** for the first to clients > returned by the query. They’ve been around a while. Any ideas? > > > > *From:* Ken Tanzer [mailto:ken...@gm...] > *Sent:* Tuesday, March 10, 2015 12:52 PM > > *To:* Michael James > *Cc:* age...@li... > *Subject:* Re: First move-in report? > > > > > > This is your current SQL: > > > > SELECT client_id,ro.housing_project_code,housing_unit_label AS > unit,residence_date AS move_in,residence_date_end AS move_out > FROM residence_own ro LEFT JOIN l_housing_project USING > (housing_project_code) LEFT JOIN housing_unit USING (housing_unit_code) > WHERE > $which BETWEEN '$sdate' AND '$edate' > > > > This SQL would only get the first moveins: > > > > SELECT *DISTINCT ON (client_id)* > client_id,ro.housing_project_code,housing_unit_label AS > unit,residence_date AS move_in,residence_date_end AS move_out > FROM residence_own ro LEFT JOIN l_housing_project USING > (housing_project_code) LEFT JOIN housing_unit USING (housing_unit_code) > WHERE > $which BETWEEN '$sdate' AND '$edate' > > *ORDER BY client_id,residence_date* > > > > If you want a separate report, you can just run with that. Or you could > add a new option to the report: > > > > PICK first_only "All moves?" > > "" "Showing all moves" > > "DISTINCT ON (client_id)" "Showing only one move per client" > > ENDPICK > > > > And then your SQL can become: > > > > SELECT *$first_only* client_id,ro.housing_project_code,housing_unit_label > AS unit,residence_date AS move_in,residence_date_end AS move_out > FROM residence_own ro LEFT JOIN l_housing_project USING > (housing_project_code) LEFT JOIN housing_unit USING (housing_unit_code) > WHERE > $which BETWEEN '$sdate' AND '$edate' > > *ORDER BY client_id,residence_date* > > > > The only issue then is that your report is now sorted by client. > Currently, there is no sort specified, but it looks like it's coming out by > residence_date. So as a final step, you could wrap the whole thing as a > subselect and restore the residence_date order: > > > > *SELECT * FROM (* > > > > SELECT *$first_only* client_id,ro.housing_project_code,housing_unit_label > AS unit,residence_date AS move_in,residence_date_end AS move_out > FROM residence_own ro LEFT JOIN l_housing_project USING > (housing_project_code) LEFT JOIN housing_unit USING (housing_unit_code) > WHERE > $which BETWEEN '$sdate' AND '$edate' > > *ORDER BY client_id,residence_date* > > > > *) foo* > > *ORDER BY residence_date* > > > > I think that will all work as described, but I haven't tested it. So let > me know if it doesn't work, or if you have any questions about it. Thanks. > > > > Ken > > > > > > On Tue, Mar 10, 2015 at 12:31 PM, Michael James < > mj...@pl...> wrote: > > I’d be happy with #1 or #3. I think the existing report is very useful > as it is. > > > > Mike > > > > *From:* Ken Tanzer [mailto:ken...@gm...] > *Sent:* Tuesday, March 10, 2015 12:12 PM > *To:* Michael James > *Cc:* age...@li... > *Subject:* Re: First move-in report? > > > > Hey Mike. Which of these are you wanting? > > > > 1) A separate report to only list first moveins > > 2) This report changed to only list first moveins > > 3) An option for this report, so it could report as it does now, or > optionally only list first moveins? > > > > Let me know. Thanks. > > > > Ken > > > > On Tue, Mar 10, 2015 at 9:42 AM, Michael James <mj...@pl...> > wrote: > > Hi Ken, > > > > We’ve got a move-in/move-out report in SPC that works well (see variable > and code block below). There’s a lot of tenant movement, so if you pick a > two-week stretch you’ll likely see a dozen or so folks who’ve moved from > one building to another. > > > > Is there an easy way to get a report of people moving into their first > unit during the chosen time period? It would be useful to see who is > moving in for the first time. > > > > Thanks, > > Mike > > > > ----- > > > > DATE sdate "Start date" > DATE edate "End date" > > PICK which "Move-ins or Move-outs?" > "residence_date" "Move ins" > "residence_date_end" "Move outs" > ENDPICK > > > > SELECT client_id,ro.housing_project_code,housing_unit_label AS > unit,residence_date AS move_in,residence_date_end AS move_out > FROM residence_own ro LEFT JOIN l_housing_project USING > (housing_project_code) LEFT JOIN housing_unit USING (housing_unit_code) > WHERE > $which BETWEEN '$sdate' AND '$edate' > > > > > > -- > > AGENCY Software > > A Free Software data system > > By and for non-profits > > *http://agency-software.org/ <http://agency-software.org/>* > > *https://agency-software.org/demo/client > <https://agency-software.org/demo/client>* > > ken...@ag... > > (253) 245-3801 > > > > Subscribe to the mailing list > <age...@li...?body=subscribe> to > > learn more about AGENCY or > > follow the discussion. > > > > > > -- > > AGENCY Software > > A Free Software data system > > By and for non-profits > > *http://agency-software.org/ <http://agency-software.org/>* > > *https://agency-software.org/demo/client > <https://agency-software.org/demo/client>* > > ken...@ag... > > (253) 245-3801 > > > > Subscribe to the mailing list > <age...@li...?body=subscribe> to > > learn more about AGENCY or > > follow the discussion. > -- AGENCY Software A Free Software data system By and for non-profits *http://agency-software.org/ <http://agency-software.org/>* *https://agency-software.org/demo/client <https://agency-software.org/demo/client>* ken...@ag... (253) 245-3801 Subscribe to the mailing list <age...@li...?body=subscribe> to learn more about AGENCY or follow the discussion. |
From: Michael J. <mj...@pl...> - 2015-03-11 18:21:12
|
I changed your “DISTINCT ON” to grab the residence_date instead of the client_id, and that got me closer: SELECT DISTINCT ON (residence_date) residence_date AS move_in, client_id, ro.housing_project_code,housing_unit_label AS unit FROM residence_own ro LEFT JOIN l_housing_project USING (housing_project_code) LEFT JOIN housing_unit USING (housing_unit_code) WHERE residence_date BETWEEN '$sdate' AND '$edate' ORDER BY residence_date,client_id It’s showing brand new SPC tenants *except* for the first to clients returned by the query. They’ve been around a while. Any ideas? From: Ken Tanzer [mailto:ken...@gm...] Sent: Tuesday, March 10, 2015 12:52 PM To: Michael James Cc: age...@li... Subject: Re: First move-in report? This is your current SQL: SELECT client_id,ro.housing_project_code,housing_unit_label AS unit,residence_date AS move_in,residence_date_end AS move_out FROM residence_own ro LEFT JOIN l_housing_project USING (housing_project_code) LEFT JOIN housing_unit USING (housing_unit_code) WHERE $which BETWEEN '$sdate' AND '$edate' This SQL would only get the first moveins: SELECT DISTINCT ON (client_id) client_id,ro.housing_project_code,housing_unit_label AS unit,residence_date AS move_in,residence_date_end AS move_out FROM residence_own ro LEFT JOIN l_housing_project USING (housing_project_code) LEFT JOIN housing_unit USING (housing_unit_code) WHERE $which BETWEEN '$sdate' AND '$edate' ORDER BY client_id,residence_date If you want a separate report, you can just run with that. Or you could add a new option to the report: PICK first_only "All moves?" "" "Showing all moves" "DISTINCT ON (client_id)" "Showing only one move per client" ENDPICK And then your SQL can become: SELECT $first_only client_id,ro.housing_project_code,housing_unit_label AS unit,residence_date AS move_in,residence_date_end AS move_out FROM residence_own ro LEFT JOIN l_housing_project USING (housing_project_code) LEFT JOIN housing_unit USING (housing_unit_code) WHERE $which BETWEEN '$sdate' AND '$edate' ORDER BY client_id,residence_date The only issue then is that your report is now sorted by client. Currently, there is no sort specified, but it looks like it's coming out by residence_date. So as a final step, you could wrap the whole thing as a subselect and restore the residence_date order: SELECT * FROM ( SELECT $first_only client_id,ro.housing_project_code,housing_unit_label AS unit,residence_date AS move_in,residence_date_end AS move_out FROM residence_own ro LEFT JOIN l_housing_project USING (housing_project_code) LEFT JOIN housing_unit USING (housing_unit_code) WHERE $which BETWEEN '$sdate' AND '$edate' ORDER BY client_id,residence_date ) foo ORDER BY residence_date I think that will all work as described, but I haven't tested it. So let me know if it doesn't work, or if you have any questions about it. Thanks. Ken On Tue, Mar 10, 2015 at 12:31 PM, Michael James <mj...@pl...<mailto:mj...@pl...>> wrote: I’d be happy with #1 or #3. I think the existing report is very useful as it is. Mike From: Ken Tanzer [mailto:ken...@gm...<mailto:ken...@gm...>] Sent: Tuesday, March 10, 2015 12:12 PM To: Michael James Cc: age...@li...<mailto:age...@li...> Subject: Re: First move-in report? Hey Mike. Which of these are you wanting? 1) A separate report to only list first moveins 2) This report changed to only list first moveins 3) An option for this report, so it could report as it does now, or optionally only list first moveins? Let me know. Thanks. Ken On Tue, Mar 10, 2015 at 9:42 AM, Michael James <mj...@pl...<mailto:mj...@pl...>> wrote: Hi Ken, We’ve got a move-in/move-out report in SPC that works well (see variable and code block below). There’s a lot of tenant movement, so if you pick a two-week stretch you’ll likely see a dozen or so folks who’ve moved from one building to another. Is there an easy way to get a report of people moving into their first unit during the chosen time period? It would be useful to see who is moving in for the first time. Thanks, Mike ----- DATE sdate "Start date" DATE edate "End date" PICK which "Move-ins or Move-outs?" "residence_date" "Move ins" "residence_date_end" "Move outs" ENDPICK SELECT client_id,ro.housing_project_code,housing_unit_label AS unit,residence_date AS move_in,residence_date_end AS move_out FROM residence_own ro LEFT JOIN l_housing_project USING (housing_project_code) LEFT JOIN housing_unit USING (housing_unit_code) WHERE $which BETWEEN '$sdate' AND '$edate' -- [http://agency-software.org/demo/client/images/agency_logo_small.png] AGENCY Software A Free Software data system By and for non-profits http://agency-software.org/ https://agency-software.org/demo/client ken...@ag...<mailto:ken...@ag...> (253) 245-3801<tel:%28253%29%20245-3801> Subscribe to the mailing list<mailto:age...@li...?body=subscribe> to learn more about AGENCY or follow the discussion. -- [http://agency-software.org/demo/client/images/agency_logo_small.png] AGENCY Software A Free Software data system By and for non-profits http://agency-software.org/ https://agency-software.org/demo/client ken...@ag...<mailto:ken...@ag...> (253) 245-3801 Subscribe to the mailing list<mailto:age...@li...?body=subscribe> to learn more about AGENCY or follow the discussion. |
From: Ken T. <ken...@gm...> - 2015-03-10 19:52:37
|
This is your current SQL: SELECT client_id,ro.housing_project_code,housing_unit_label AS unit,residence_date AS move_in,residence_date_end AS move_out FROM residence_own ro LEFT JOIN l_housing_project USING (housing_project_code) LEFT JOIN housing_unit USING (housing_unit_code) WHERE $which BETWEEN '$sdate' AND '$edate' This SQL would only get the first moveins: SELECT *DISTINCT ON (client_id)* client_id,ro.housing_project_code,housing_unit_label AS unit,residence_date AS move_in,residence_date_end AS move_out FROM residence_own ro LEFT JOIN l_housing_project USING (housing_project_code) LEFT JOIN housing_unit USING (housing_unit_code) WHERE $which BETWEEN '$sdate' AND '$edate' *ORDER BY client_id,residence_date* If you want a separate report, you can just run with that. Or you could add a new option to the report: PICK first_only "All moves?" "" "Showing all moves" "DISTINCT ON (client_id)" "Showing only one move per client" ENDPICK And then your SQL can become: SELECT *$first_only* client_id,ro.housing_project_code,housing_unit_label AS unit,residence_date AS move_in,residence_date_end AS move_out FROM residence_own ro LEFT JOIN l_housing_project USING (housing_project_code) LEFT JOIN housing_unit USING (housing_unit_code) WHERE $which BETWEEN '$sdate' AND '$edate' *ORDER BY client_id,residence_date* The only issue then is that your report is now sorted by client. Currently, there is no sort specified, but it looks like it's coming out by residence_date. So as a final step, you could wrap the whole thing as a subselect and restore the residence_date order: *SELECT * FROM (* SELECT *$first_only* client_id,ro.housing_project_code,housing_unit_label AS unit,residence_date AS move_in,residence_date_end AS move_out FROM residence_own ro LEFT JOIN l_housing_project USING (housing_project_code) LEFT JOIN housing_unit USING (housing_unit_code) WHERE $which BETWEEN '$sdate' AND '$edate' *ORDER BY client_id,residence_date* *) foo* *ORDER BY residence_date* I think that will all work as described, but I haven't tested it. So let me know if it doesn't work, or if you have any questions about it. Thanks. Ken On Tue, Mar 10, 2015 at 12:31 PM, Michael James <mj...@pl...> wrote: > I’d be happy with #1 or #3. I think the existing report is very useful > as it is. > > > > Mike > > > > *From:* Ken Tanzer [mailto:ken...@gm...] > *Sent:* Tuesday, March 10, 2015 12:12 PM > *To:* Michael James > *Cc:* age...@li... > *Subject:* Re: First move-in report? > > > > Hey Mike. Which of these are you wanting? > > > > 1) A separate report to only list first moveins > > 2) This report changed to only list first moveins > > 3) An option for this report, so it could report as it does now, or > optionally only list first moveins? > > > > Let me know. Thanks. > > > > Ken > > > > On Tue, Mar 10, 2015 at 9:42 AM, Michael James <mj...@pl...> > wrote: > > Hi Ken, > > > > We’ve got a move-in/move-out report in SPC that works well (see variable > and code block below). There’s a lot of tenant movement, so if you pick a > two-week stretch you’ll likely see a dozen or so folks who’ve moved from > one building to another. > > > > Is there an easy way to get a report of people moving into their first > unit during the chosen time period? It would be useful to see who is > moving in for the first time. > > > > Thanks, > > Mike > > > > ----- > > > > DATE sdate "Start date" > DATE edate "End date" > > PICK which "Move-ins or Move-outs?" > "residence_date" "Move ins" > "residence_date_end" "Move outs" > ENDPICK > > > > SELECT client_id,ro.housing_project_code,housing_unit_label AS > unit,residence_date AS move_in,residence_date_end AS move_out > FROM residence_own ro LEFT JOIN l_housing_project USING > (housing_project_code) LEFT JOIN housing_unit USING (housing_unit_code) > WHERE > $which BETWEEN '$sdate' AND '$edate' > > > > > > -- > > AGENCY Software > > A Free Software data system > > By and for non-profits > > *http://agency-software.org/ <http://agency-software.org/>* > > *https://agency-software.org/demo/client > <https://agency-software.org/demo/client>* > > ken...@ag... > > (253) 245-3801 > > > > Subscribe to the mailing list > <age...@li...?body=subscribe> to > > learn more about AGENCY or > > follow the discussion. > -- AGENCY Software A Free Software data system By and for non-profits *http://agency-software.org/ <http://agency-software.org/>* *https://agency-software.org/demo/client <https://agency-software.org/demo/client>* ken...@ag... (253) 245-3801 Subscribe to the mailing list <age...@li...?body=subscribe> to learn more about AGENCY or follow the discussion. |
From: Michael J. <mj...@pl...> - 2015-03-10 19:31:22
|
I’d be happy with #1 or #3. I think the existing report is very useful as it is. Mike From: Ken Tanzer [mailto:ken...@gm...] Sent: Tuesday, March 10, 2015 12:12 PM To: Michael James Cc: age...@li... Subject: Re: First move-in report? Hey Mike. Which of these are you wanting? 1) A separate report to only list first moveins 2) This report changed to only list first moveins 3) An option for this report, so it could report as it does now, or optionally only list first moveins? Let me know. Thanks. Ken On Tue, Mar 10, 2015 at 9:42 AM, Michael James <mj...@pl...<mailto:mj...@pl...>> wrote: Hi Ken, We’ve got a move-in/move-out report in SPC that works well (see variable and code block below). There’s a lot of tenant movement, so if you pick a two-week stretch you’ll likely see a dozen or so folks who’ve moved from one building to another. Is there an easy way to get a report of people moving into their first unit during the chosen time period? It would be useful to see who is moving in for the first time. Thanks, Mike ----- DATE sdate "Start date" DATE edate "End date" PICK which "Move-ins or Move-outs?" "residence_date" "Move ins" "residence_date_end" "Move outs" ENDPICK SELECT client_id,ro.housing_project_code,housing_unit_label AS unit,residence_date AS move_in,residence_date_end AS move_out FROM residence_own ro LEFT JOIN l_housing_project USING (housing_project_code) LEFT JOIN housing_unit USING (housing_unit_code) WHERE $which BETWEEN '$sdate' AND '$edate' -- [http://agency-software.org/demo/client/images/agency_logo_small.png] AGENCY Software A Free Software data system By and for non-profits http://agency-software.org/ https://agency-software.org/demo/client ken...@ag...<mailto:ken...@ag...> (253) 245-3801 Subscribe to the mailing list<mailto:age...@li...?body=subscribe> to learn more about AGENCY or follow the discussion. |
From: Ken T. <ken...@gm...> - 2015-03-10 19:12:55
|
Hey Mike. Which of these are you wanting? 1) A separate report to only list first moveins 2) This report changed to only list first moveins 3) An option for this report, so it could report as it does now, or optionally only list first moveins? Let me know. Thanks. Ken On Tue, Mar 10, 2015 at 9:42 AM, Michael James <mj...@pl...> wrote: > Hi Ken, > > > > We’ve got a move-in/move-out report in SPC that works well (see variable > and code block below). There’s a lot of tenant movement, so if you pick a > two-week stretch you’ll likely see a dozen or so folks who’ve moved from > one building to another. > > > > Is there an easy way to get a report of people moving into their first > unit during the chosen time period? It would be useful to see who is > moving in for the first time. > > > > Thanks, > > Mike > > > > ----- > > > > DATE sdate "Start date" > DATE edate "End date" > > PICK which "Move-ins or Move-outs?" > "residence_date" "Move ins" > "residence_date_end" "Move outs" > ENDPICK > > > > SELECT client_id,ro.housing_project_code,housing_unit_label AS > unit,residence_date AS move_in,residence_date_end AS move_out > FROM residence_own ro LEFT JOIN l_housing_project USING > (housing_project_code) LEFT JOIN housing_unit USING (housing_unit_code) > WHERE > $which BETWEEN '$sdate' AND '$edate' > -- AGENCY Software A Free Software data system By and for non-profits *http://agency-software.org/ <http://agency-software.org/>* *https://agency-software.org/demo/client <https://agency-software.org/demo/client>* ken...@ag... (253) 245-3801 Subscribe to the mailing list <age...@li...?body=subscribe> to learn more about AGENCY or follow the discussion. |
From: Michael J. <mj...@pl...> - 2015-03-10 16:55:31
|
Hi Ken, We've got a move-in/move-out report in SPC that works well (see variable and code block below). There's a lot of tenant movement, so if you pick a two-week stretch you'll likely see a dozen or so folks who've moved from one building to another. Is there an easy way to get a report of people moving into their first unit during the chosen time period? It would be useful to see who is moving in for the first time. Thanks, Mike ----- DATE sdate "Start date" DATE edate "End date" PICK which "Move-ins or Move-outs?" "residence_date" "Move ins" "residence_date_end" "Move outs" ENDPICK SELECT client_id,ro.housing_project_code,housing_unit_label AS unit,residence_date AS move_in,residence_date_end AS move_out FROM residence_own ro LEFT JOIN l_housing_project USING (housing_project_code) LEFT JOIN housing_unit USING (housing_unit_code) WHERE $which BETWEEN '$sdate' AND '$edate' |
From: Ken T. <ken...@gm...> - 2014-11-13 22:01:26
|
Mike pointed out to me that people entering a 2015 date with a 2 digit year (e.g., 2/1/15) are getting 1915 and not the 2015 they're expecting. A long time ago, when 2015 seemed far away, that was the cutoff we set for interpreting 2015 dates. I'm going to go ahead in the near future and update this in AGENCY so the cutoff is 2020. Mike, if you want to make the change now, you can just change this line in agency_config.php: define('AG_DATE_CENTURY_CUTOFF',15); Cheers, Ken -- AGENCY Software A Free Software data system By and for non-profits *http://agency-software.org/ <http://agency-software.org/>* *https://agency-software.org/demo/client <https://agency-software.org/demo/client>* ken...@ag... (253) 245-3801 Subscribe to the mailing list <age...@li...?body=subscribe> to learn more about AGENCY or follow the discussion. |
From: Ken T. <ken...@gm...> - 2014-10-15 01:18:53
|
Just to follow up for the list, we changed the PHP attachment size settings, and no adjustment to the engine config was necessary. I guess you would use the AGENCY max_file_size if you wanted something lower than the maximum size the system will accept. Ken On Mon, Oct 13, 2014 at 4:30 PM, Ken Tanzer <ken...@gm...> wrote: > Hey Mike. I looked into this a bit, and was pleasantly surprised to see > this is an engine option. In theory at least, you can specify > 'max_file_size' for an attachment field in the config file. I'd say give > it a shot, and let me know if you hit any snags with it. > > On a related note, it looks like photos are currently hard-coded for a 3M > limit, which should get changed at some point. > > Cheers, > Ken > > On Mon, Oct 13, 2014 at 10:05 AM, Michael James < > mj...@pl...> wrote: > >> Hi Ken, >> >> >> >> On SPC’s version of Agency we have a need to upload attachments that are >> pretty large (for us) – I’ve got one that’s 18 megs but they can certainly >> get bigger than that. >> >> >> >> I see this in php.ini: >> >> >> >> post_max_size = 8M >> >> >> >> and this: >> >> >> >> upload_max_filesize = 2M >> >> >> >> I assume I need to bump those two and restart Apache? Are there any >> other PHP limits I’m missing, and are there any limits to file upload size >> anywhere else in Agency? >> >> >> >> Thanks, >> >> Mike >> > > > > -- > AGENCY Software > A Free Software data system > By and for non-profits > *http://agency-software.org/ <http://agency-software.org/>* > *https://agency-software.org/demo/client > <https://agency-software.org/demo/client>* > ken...@ag... > (253) 245-3801 > > Subscribe to the mailing list > <age...@li...?body=subscribe> to > learn more about AGENCY or > follow the discussion. > -- AGENCY Software A Free Software data system By and for non-profits *http://agency-software.org/ <http://agency-software.org/>* *https://agency-software.org/demo/client <https://agency-software.org/demo/client>* ken...@ag... (253) 245-3801 Subscribe to the mailing list <age...@li...?body=subscribe> to learn more about AGENCY or follow the discussion. |
From: Ken T. <ken...@gm...> - 2014-10-13 23:31:30
|
Hey Mike. I looked into this a bit, and was pleasantly surprised to see this is an engine option. In theory at least, you can specify 'max_file_size' for an attachment field in the config file. I'd say give it a shot, and let me know if you hit any snags with it. On a related note, it looks like photos are currently hard-coded for a 3M limit, which should get changed at some point. Cheers, Ken On Mon, Oct 13, 2014 at 10:05 AM, Michael James <mj...@pl...> wrote: > Hi Ken, > > > > On SPC’s version of Agency we have a need to upload attachments that are > pretty large (for us) – I’ve got one that’s 18 megs but they can certainly > get bigger than that. > > > > I see this in php.ini: > > > > post_max_size = 8M > > > > and this: > > > > upload_max_filesize = 2M > > > > I assume I need to bump those two and restart Apache? Are there any other > PHP limits I’m missing, and are there any limits to file upload size > anywhere else in Agency? > > > > Thanks, > > Mike > -- AGENCY Software A Free Software data system By and for non-profits *http://agency-software.org/ <http://agency-software.org/>* *https://agency-software.org/demo/client <https://agency-software.org/demo/client>* ken...@ag... (253) 245-3801 Subscribe to the mailing list <age...@li...?body=subscribe> to learn more about AGENCY or follow the discussion. |
From: Michael J. <mj...@pl...> - 2014-10-13 17:05:24
|
Hi Ken, On SPC's version of Agency we have a need to upload attachments that are pretty large (for us) - I've got one that's 18 megs but they can certainly get bigger than that. I see this in php.ini: post_max_size = 8M and this: upload_max_filesize = 2M I assume I need to bump those two and restart Apache? Are there any other PHP limits I'm missing, and are there any limits to file upload size anywhere else in Agency? Thanks, Mike |
From: Ken T. <ken...@gm...> - 2014-09-29 19:43:58
|
Hey Mike. Presumably any time a record is added, it should be a move in. So the action should be INSERT. In this scenario we're not really looking at the residence date, so leave the ANRF blank. Cheers, Ken On Mon, Sep 29, 2014 at 10:37 AM, Michael James <mj...@pl...> wrote: > Hi Ken, > > > > We’ve got a guy in our Compliance department who wants to receive an alert > whenever someone is moved into a unit in Agency. Would this do it? > > > > Object: residence_own > > Action: ANY (or should it be UPDATE?) > > Staff: <guy’s name> > > Alert Notify Record Field: residence_date > > > > Thanks, > > Mike > -- AGENCY Software A Free Software data system By and for non-profits *http://agency-software.org/ <http://agency-software.org/>* *https://agency-software.org/demo/client <https://agency-software.org/demo/client>* ken...@ag... (253) 245-3801 Subscribe to the mailing list <age...@li...?body=subscribe> to learn more about AGENCY or follow the discussion. |
From: Ken T. <ken...@gm...> - 2014-09-29 19:42:00
|
Hey Aaron. For the default, you can specify "EVAL: $GLOBALS['UID']" (including the double quotes. That will default to the current user, and if they're not on the pick list it will be ignored and therefore default to the first value on the list. Let me know if you have any questions about this. Cheers. Ken On Fri, Sep 26, 2014 at 3:31 PM, Aaron Emery <ae...@pl...> wrote: > Does anyone know if there’s a way to write a report variable for a drop > down menu that will have the default value listed as the person who’s > logged in at that time if they’re available to be chosen from that list and > if not it would default to no value? > > > > Aaron Emery > > > -- AGENCY Software A Free Software data system By and for non-profits *http://agency-software.org/ <http://agency-software.org/>* *https://agency-software.org/demo/client <https://agency-software.org/demo/client>* ken...@ag... (253) 245-3801 Subscribe to the mailing list <age...@li...?body=subscribe> to learn more about AGENCY or follow the discussion. |
From: Michael J. <mj...@pl...> - 2014-09-29 17:37:16
|
Hi Ken, We've got a guy in our Compliance department who wants to receive an alert whenever someone is moved into a unit in Agency. Would this do it? Object: residence_own Action: ANY (or should it be UPDATE?) Staff: <guy's name> Alert Notify Record Field: residence_date Thanks, Mike |
From: Aaron E. <ae...@pl...> - 2014-09-26 22:31:14
|
Does anyone know if there's a way to write a report variable for a drop down menu that will have the default value listed as the person who's logged in at that time if they're available to be chosen from that list and if not it would default to no value? Aaron Emery |
From: Ken T. <ken...@gm...> - 2014-09-17 20:06:45
|
Hey Mike. As a matter of fact, there is. Use the unit_no function. unit_no(client_id,'2003-12-31') will return a unit # if they lived somewhere on that date, and NULL if they didn't. Let me know if you have any questions about this. Thanks. Ken On Wed, Sep 17, 2014 at 12:51 PM, Michael James <mj...@pl...> wrote: > Hi Ken, > > > > Is there any function in the DB that could tell me if a client was housed > on a given date? For example, I’m generating a list of tenants who moved > in before 2003, and I’d like to know if they were still housed (in any > unit, not necessarily their original room) on 12/31/2003? > > > > Thanks, > > Mike > -- AGENCY Software A Free Software data system By and for non-profits *http://agency-software.org/ <http://agency-software.org/>* *https://agency-software.org/demo/client <https://agency-software.org/demo/client>* ken...@ag... (253) 245-3801 Subscribe to the mailing list <age...@li...?body=subscribe> to learn more about AGENCY or follow the discussion. |