Thanks for the quick reply!!!
Any plans to incorperate this into a future release?
So if I convert this table into a point layer and give each record the geometry of the related existing point, could I just turn off symbology in the map document and accomplish the same thing?
Thanks!
I kind of acheieved what you are looking for. I have a point layer of schools and a table only dataset with a list of school reservations. I wanted Robert's tool to return the location of every schoool that had made a reservation. I did it using a little SQL:<definitionexpression>
EXISTS (SELECT * FROM central2.GISADMIN.TRPD_Schdgrps_Schools
WHERE central2.GISADMIN.MN_Schools_PUBLIC.ORGID_REL = central2.GISADMIN.TRPD_Schdgrps_Schools.School_Code)
</definitionexpression>
<enableexport>true</enableexport>
<name>Minnesota Schools - Users</name>
<url>http://[server]/ArcGIS/rest/services/swan/CRANE_alldata/MapServer/1</url>
<expressions>
<expression alias="All users"
textsearchlabel="Search for all non-user schools"
userlist="1=1">[value]</expression>
<expression alias="All users by time period"
textsearchlabel="Enter a year (2010) or a time period (2010 Q1)">
EXISTS (SELECT * FROM central2.GISADMIN.TRPD_Schdgrps_Schools
WHERE central2.GISADMIN.MN_Schools_PUBLIC.ORGID_REL = central2.GISADMIN.TRPD_Schdgrps_Schools.School_Code
AND central2.GISADMIN.TRPD_Schdgrps_Schools.TimePeriod LIKE '%[value]%')</expression>
<expression alias="Multiple reservations"
textsearchlabel="Enter a year (2010) or a time period (2010 Q1)">
gisadmin.MN_SCHOOLS_PUBLIC.ORGID_REL IN
(SELECT tss.School_Code FROM central2.gisadmin.TRPD_SCHDGRPS_SCHOOLS tss
WHERE tss.TimePeriod Like '%[value]%' GROUP BY tss.School_Code HAVING COUNT(*) > 1)</expression>
<expression alias="Single reservation"
textsearchlabel="Enter a year (2010) or a time period (2010 Q1)">
gisadmin.MN_SCHOOLS_PUBLIC.ORGID_REL IN
(SELECT tss.School_Code FROM central2.gisadmin.TRPD_SCHDGRPS_SCHOOLS tss
WHERE tss.TimePeriod Like '%[value]%' GROUP BY tss.School_Code HAVING COUNT(*) = 1)</expression>
<expression alias="School Name"
textsearchlabel="Search for schools by name">
SCHNAME Like '%[value]%'</expression>
<expression alias="Type of School"
textsearchlabel="Search for schools by type"
userlist="ELEM,MIDD,HIGH,ALC,SECOND FACILITY COOP,CHARTER,COLLEGE,COOP,
CORRECTIONAL,EDUCATION_DISTRICT,DISTRICT-OTHER,
INDEPENDENT,INTERMEDIATE DIST,OFFICE,OTHER,SPECIAL">SCH_TYPE1 LIKE '%[value]%'</expression>
<expression alias="School District Number"
textsearchlabel="Search for schools by Schoo District Number">
SDNUM = [value]</expression>
</expressions>
You will notice that in the definition query tag, I used the exists command of SQL. ArcGIS automatically starts every query with the select statement, so you essentially can only build a query after "select * from where" and so you do not need to type that as you may already know.
EXISTS (SELECT * FROM central2.GISADMIN.TRPD_Schdgrps_Schools
WHERE central2.GISADMIN.MN_Schools_PUBLIC.ORGID_REL = central2.GISADMIN.TRPD_Schdgrps_Schools.School_Code)
This query essentially looks at the schools table and only returns schools that have a matching school ID in the reservations table.
Just thought I would share, it may not do exactly what you need, but hopefully it was helpful!
One thign to note, this query may not give an expected result if your table is registered as versioned because it does nto query the A and D tables.
