Performing Conditional Calculate Field with Many to One using ArcMap?

Question

Anyone have experience calculating a field based on a many to one scenario?

I have a feature class in a geodatabase with a field of unique names.

---

Feature Class Table (let's call this "MAINTABLE") looks like this:

OID   ;  Name ; B
1    ;   Breakfast ; NULL
2    ;   Lunch ; NULL
3    ;   Dinner ; NULL
4    ;   Dessert ; NULL

---

I also have a stand alone table in a different location that has several entries for each unique name in the feature class.

---

Stand alone table (Let's call this "SIDETABLE") looks like this:

UID ; Name ; A
1 ; Breakfast ; 0
2 ; Breakfast ; -1
3 ; Lunch ; -1
4 ; Dinner ; 0
5 ; Dessert ; NULL
6 ; Breakfast ; -1
7 ; Breakfast ; NULL
8 ; Breakfast ; -1
9 ; Breakfast ; -1
10 ; Breakfast ; NULL

---

I need help to search all values ("0" or "-1" or NULL) in SIDETABLE field "A", and if any entry in SIDETABLE field "A" has a "0", calculate field "B" in MAINTABLE as value "0".

BUT if a unique name entry in SIDETABLE field "A" contains "0" and "-1", calculate field "B" in MAINTABLE as value "0".

If a unique name from MAINTABLE does not in field "A" of SIDETABLE have value "0" but has value "-1", calculate field "B" in MAINTABLE as "-1".

If a unique name from MAINTABLE does not in field "A" of SIDETABLE have value "0" but has NULL value, calculate/keep field "B" in MAINTABLE as NULL.

---

The resulting feature class table ("MAINTABLE") should look like this:

OID   ;  Name ; B
1    ;   Breakfast ; 0
2    ;   Lunch ; -1
3    ;   Dinner ; 0
4    ;    Dessert ; NULL

---

***I would assume python is the best option. I am new scripting/ using python or whichever is best practice. I am assuming I would need some sort of conditional statement using Calculate Field on field "B" in the feature class. And I am also assuming I need to relate the feature class to table based on NAME.

***I am using ArcGIS 10.2 for Desktop Advanced

---

The following is the sort of logic I'm looking for

if A.SIDETABLE = 0 
return into B.MAINTABLE the value "0"

*[if there are 10 "breakfast" entries in SIDETABLE, 
  look at all values in field "A" and if one of the values is 0, 
  then return "0" into B.MAINTABLE]

else if A.SIDETABLE = -1 
return into B.MAINTABLE the value "-1"
else if A.SIDETABLE is NULL 
return into B.MAINTABLE NULL

Answer 1

Run Summary Statistics on SIDETABLE using Name as case field and A as statistics field with MAX as the statistic. You can then update MAINTABLE with the resulting summary table - regular join on Name and field calculate, join field, field calculate directly if both tables in the same geodatabase, etc.

For that matter, if the tables are in the same geodatabase and MAINTABLE doesn't have a lot of records, you could skip Summary Statistics and do this all in the field calculator. Psuedo-code for pre-logic block (and I might be blending Python and VB parsing here):

create a variable
CASE or IF statement on Name variants
for each Name variant, variable = SELECT MAX(A) FROM SIDETABLE WHERE Name = variant

Then your field calculation box would just be variable. Note per ike's statement, I'm not paying any attention to nulls here which may require further refinement or their own case/if. And in order to do a nested query like that, both tables have to be in the same geodatabase.

Answer 2

Before calculating, I would join the tables.

1. define a definition query on table A: FieldName in ("0", "-1", NULL)

2. define a definiton query on table B to include the records that need to be updated

3. Join table A to table B

4. Now, use the field calculator and define the logic in the Python script

   I assume the relationship between table B to A is many to one. When joining the tables, use Keep all records as a Join Option.