Symbolize point based on related table record entry using Python.

[MegPeterson]

Hi all - I am a newbie to Sql and Python and am trying to convert some sql statements that a consultant put together for our old IMS/SQL set up, to work with our new ArcSDE/SQL set up and am wondering what the right approach is. At the end of this post are the original Sql statements for reference. I feel like this is an easier solution through Python and ArcMap (V.10) etc, but I'm just not putting it together.

The situation is:

I have a point file that has a 1:Many relationship with a non-spatial table. As an example: Point #1 has 3 different maintenance issues that need fixing at that location.

The symbology of the points are based on the completion of the issues:
???When nothing has been fixed, the point is symbolized as a red circle.
???When some things have been fixed, the point turns to a green, half circle.
???When all 3 issues have been fixed, the point turns to a green circle.

In the past, this was accomplished by basing the symbology on a "STATUS" field that referenced the 'CompletedDate' field in the related table. Whenever a date was entered in the 'CompletedDate' field the 'Status' field will automatically update and voila(!), the point color will change.

???If the date was null (ie: nothing had been worked on), the Status field would = 0, and the point color = Red.
???If the date was not null, Status = 1 (and the point would be half green), and
???If the CompletedDate (as a DateCount) in the related table was equal to the number of issues in the RecordCount field, the point would turn green.

I'm wondering if this is something that I can run from inside the MXD through Python and the Field Calculator instead of running it in the acutal SQL database. We're using Enterprise v.10, SQL Server 2008, Flex 4.0 (where the editing will happen). We are not using versioned tables.

Any words of wisdom, or suggestions on how to do this in Python using the Field Calculator somehow with if and else statements?

Thank you, Meg

____________________________________
STORED PROCEDURE

CREATE PROCEDURE [sql_data].[sp_Update_ADASTATS]
AS
truncate table sql_data.ADASTATS;
INSERT INTO sql_data.ADASTATS SELECT DISTINCT FACILITYZONEID UNIQCODE, '0', COUNT(*) FROM sql_data.ADA_Actions GROUP BY FACILITYZONEID;
UPDATE sql_data.ADASTATS SET STATUS=1 WHERE sql_data.ADASTATS.UNIQCODE IN (SELECT DISTINCT FACILITYZONEID UNIQCODE FROM sql_data.ADA_Actions WHERE CompletedDate IS NOT NULL);
UPDATE sql_data.ADASTATS SET STATUS=2 WHERE sql_data.ADASTATS.UNIQCODE IN (SELECT DISTINCT UNIQCODE FROM sql_data.vw_ADASTATS WHERE DateCount = RecCount);
GO

____________________________________
VIEW

SELECT Sql_data.ADASTATS.UNIQCODE, Sql_data.ADASTATS.RECCOUNT, COUNT(Sql_data.ADA_Actions.CompletedDate) AS DateCount
FROM Sql_data.ADA_Actions INNER JOIN
Sql_data.ADASTATS ON Sql_data.ADA_Actions.FacilityZoneID = Sql_data.ADASTATS.UNIQCODE
WHERE (Sql_data.ADA_Actions.CompletedDate IS NOT NULL)
GROUP BY Sql_data.ADASTATS.UNIQCODE, Sql_data.ADASTATS.RECCOUNT

[RyanForbes1]

Hey Meg,

I saw this the other day and have been trying to think of a way to do this through Python.  I would like to ask a few questions to better understand what you're trying to do and then offer up an idea that just occured to me.

How exactly are you tracking these issues?  Are they empty fields that get populated and thus having been populated they are considered resolved?  If this is the case then I believe I have an idea that might work for you.  It wouldn't be as instant, but at the end of an editing session you could run the script and it would update the [STATUS] field accordingly.

A script could perhaps check the length each of the three (or however many) fields, if they return 0 (aka empty) the script could set the [STATUS] for that record to 0/unresolved, if one or more of the fields return >0 then it could set the status to 1/in_progress and if all of the fields return >0 then the status could be set to 2/resolved and then the dots can be symbolized by the varrying levels of the status field.

[MegPeterson]

Ryan - thank you for thinking on this! I'm just stuck. I actually wonder if I'm overthinking and could accomplish this through a simple definition query (basically forgetting about the STATUS field) - see:

http://forums.arcgis.com/threads/55751-Simple-Definition-Query-Group-By?p=191689&posted=1#post191689

The process is this:
Yes, when the CompletedDate field is populated, we were using that as a count and when the total counts for each point equaled the record number for that point, the symbol would turn green.

The part I'm running into trouble with is how to GROUP BY the point locations. The "english" version of what I'm trying to do is:

For Each location grouped by the location number (ie: all points at location 1), if CompletedDate is not null, then turn that little circle fully green (!), else, do nothing (because I can display the partially completed points using a definition query on the feature class.

Many thanks...