eSearch <Table><Relates><Relate> issue.

[JordanBaumgardner1]

I'm having a terrible time getting the Relate to work with a Table entry.

I have a table (non spacial) Meter with the customer name on it.
I have a Feature Class (Service Location) that has a spacial reference.
There is a relate from Meter to Service location: Layer: xxxx.METER (ID: 84) Relationships: Service Location (0)

I'm trying to do a search on customer name and end up at a service location.

I get no errors, and it is attempting to do the join, but I never receive any data.

Thanks in advance. It's probably something stupid, but I just can't see it.

Key Config section:
   <relate id="0" label="Service Location" enableexport="true"icon="widgets/eSearch/assets/images/i_relate.png">
    <fields all="true" />
    <zoomscale usegeometry="true" zoompercent="1.6" />
   </relate>
Key Service def ref:
Service Location (0) -- Related To: De-Energized Service Locations (8)


Here is my config:
<tables>
<table>
  <name>Customers</name>
  <token/>
  <definitionexpression></definitionexpression>
  <enableexport>true</enableexport>
  <url>http://gisweb:6080/arcgis/rest/services/detailed/MapServer/84</url>
  <expressions>
   <expression alias="Location Code" textsearchlabel="Search Service by Location Code [ Example: 232410901 ]:" isvaluerequired="true">
    <values>
     <value prompt="Example: 232410901" isvaluerequired="true" >LOCATION_CODE = '[value]'</value>
    </values>
   </expression>
   <expression alias="Customer Name" textsearchlabel="Search Customer by Customer Name [ Example: FROST ]:" isvaluerequired="true">
    <values>
     <value prompt="Example: FROST" isvaluerequired="true" >upper(CUSTOMER_NAME) LIKE upper('[value]%')</value>
    </values>
   </expression>
   <expression alias="Billing Acct #" textsearchlabel="Search Customer by Account # [ Example: 487788 ]:" isvaluerequired="true">
    <values>
     <value prompt="Example: 487788" isvaluerequired="true" >ACCOUNT_NUMBER = '[value]'</value>
    </values>
   </expression>
   <expression alias="Service Street Address" textsearchlabel="Search Customer by Street Address [ Example: 850 COMMERCE RD ]:" isvaluerequired="true">
    <values>
     <value prompt="Example: 850 COMMERCE RD" isvaluerequired="true" >upper(SERVICE_ADDRESS_1) = upper('[value]')</value>
    </values>
   </expression>
  </expressions>
  <titlefield>CUSTOMER_NAME</titlefield>
  <fields all="false">
   <field name="LOCATION_CODE"/>
   <field name="ACCOUNT_NUMBER" gridfield="true" />
   <field name="CUSTOMER_NAME" gridfield="true" />
   <field name="SERVICE_ADDRESS_1" gridfield="true" />
   <field name="SERVICE_ADDRESS_2" gridfield="true" />
   <field name="SERVICE_CITY_STATE_ZIP" gridfield="true" />
   <field name="HOME_PHONE" gridfield="true" />
   <field name="ACCOUNT_STATUS" gridfield="true" />
  </fields>
  <links/>
  <relates>
   <relate id="0" label="Service Location" enableexport="true"icon="widgets/eSearch/assets/images/i_relate.png">
    <fields all="true" />
    <zoomscale usegeometry="true" zoompercent="1.6" />
   </relate>
  </relates>
</table>
</tables>

And my Service def:

Layer: xxxx.METER (ID: 84)
Name: xxxx.METER
Display Field: CUSTOMER_NAME
Type: Table
Geometry Type: N/A
Description: null
Definition Expression:
Copyright Text: N/A
Default Visibility: false
MaxRecordCount: 1000
Supported Query Formats: JSON, AMF
Supports Advanced Queries: true
Supports Statistics: true
Extent:
Drawing Info:
N/A
HasZ: false
HasM: false
Has Attachments: false
HTML Popup Type: esriServerHTMLPopupTypeNone
Type ID Field: null
Fields:
OBJECTID ( type: esriFieldTypeOID , alias: OBJECTID )
LOCATION_CODE ( type: esriFieldTypeString , alias: Location Code , length: 30 )
METER_NUMBER ( type: esriFieldTypeString , alias: Meter Number , length: 30 )
CUSTOMER_NAME ( type: esriFieldTypeString , alias: Customer Name , length: 40 )
ACCOUNT_NUMBER ( type: esriFieldTypeString , alias: Account Number , length: 20 )
ACCOUNT_STATUS ( type: esriFieldTypeString , alias: Account Status , length: 10 )
BILLING_ADDRESS_1 ( type: esriFieldTypeString , alias: Billing Address 1 , length: 40 )
BILLING_ADDRESS_2 ( type: esriFieldTypeString , alias: Billing Address 2 , length: 40 )
BILLING_CITY_STATE_ZIP ( type: esriFieldTypeString , alias: Billing City, State, Zip , length: 40 )
SERVICE_DESC ( type: esriFieldTypeString , alias: Service Description , length: 40 )
SERVICE_ADDRESS_1 ( type: esriFieldTypeString , alias: Service Address 1 , length: 40 )
SERVICE_ADDRESS_2 ( type: esriFieldTypeString , alias: Service Address 2 , length: 40 )
SERVICE_CITY_STATE_ZIP ( type: esriFieldTypeString , alias: Service City, State, Zip , length: 40 )
HOME_PHONE ( type: esriFieldTypeString , alias: Home Phone Number , length: 40 )
SUBDIVISION ( type: esriFieldTypeString , alias: SUBDIVISION , length: 40 )
BLOCK_NUMBER ( type: esriFieldTypeString , alias: Block Number , length: 10 )
LOT_NUMBER ( type: esriFieldTypeString , alias: Lot Number , length: 10 )
APARTMENT ( type: esriFieldTypeString , alias: Apartment Number , length: 10 )
SUITE ( type: esriFieldTypeString , alias: Suite Number , length: 10 )
ON_DATE ( type: esriFieldTypeDate , alias: ON_DATE , length: 36 )
SECTION_ID ( type: esriFieldTypeString , alias: Line Section ID , length: 20 )
PRIMARY_METER ( type: esriFieldTypeString , alias: Primary Meter? , length: 1 , Coded Values: , )
SERVICE_LOCATION_ID ( type: esriFieldTypeInteger , alias: Service Location ID (Foreign Key) )
CAYENTA_LOC_REC_NO ( type: esriFieldTypeString , alias: CAYENTA_LOC_REC_NO , length: 20 )
ROUTE_NO ( type: esriFieldTypeString , alias: Route No , length: 10 )
SEQ_NO ( type: esriFieldTypeString , alias: Sequence No , length: 10 )
CYCLE_NO ( type: esriFieldTypeString , alias: Cycle No , length: 10 )
JDE_WO_ID ( type: esriFieldTypeString , alias: JDE_WO_ID , length: 20 )
DATE_INSERVICE ( type: esriFieldTypeDate , alias: Date Inservice , length: 36 )
WIRE_COUNT ( type: esriFieldTypeInteger , alias: Wire Count )
SERVICE_STATUS ( type: esriFieldTypeString , alias: Service Status , length: 10 )
LOCATION_CLASS ( type: esriFieldTypeString , alias: Location Class , length: 10 )
LOCATION_SUBCLASS ( type: esriFieldTypeString , alias: Location Subclass , length: 10 )
METER_BILL_CODE ( type: esriFieldTypeString , alias: Meter Bill Code , length: 10 )
KW_DEMAND ( type: esriFieldTypeString , alias: KW Demand , length: 20 )
KWH_CONSUMED ( type: esriFieldTypeString , alias: KWH Consumed , length: 20 )
READ_DATE ( type: esriFieldTypeDate , alias: Read Date , length: 36 )
BILLING_CYCLE_DAYS ( type: esriFieldTypeString , alias: Billing Cycle Days , length: 10 )
CRITICAL_LOAD ( type: esriFieldTypeString , alias: Critical Load , length: 7 )
SPOT_LOAD ( type: esriFieldTypeString , alias: Spot Load , length: 5 , Coded Values: , )
METER_TYPE ( type: esriFieldTypeString , alias: Meter Type , length: 15 )
METER_MULTIPLIER ( type: esriFieldTypeString , alias: Meter Multiplier , length: 5 )
TAX_DISTRICT ( type: esriFieldTypeString , alias: Tax District , length: 5 )
METER_COMMENTS ( type: esriFieldTypeString , alias: Meter Comments , length: 100 )
WORKREQUESTID ( type: esriFieldTypeString , alias: Work Request ID , length: 20 )
DESIGNID ( type: esriFieldTypeString , alias: Design ID , length: 20 )
WORKLOCATIONID ( type: esriFieldTypeString , alias: Work Location ID , length: 20 )
WORKFLOWSTATUS ( type: esriFieldTypeInteger , alias: Work Flow Status , Coded Values: [0: None] , [1: In Design] , [2: Pending Approval] , ...5 more... )
WORKFUNCTION ( type: esriFieldTypeInteger , alias: Work Function , Coded Values: [0: In-Service] , [1: Install] , [2: Remove] , ...5 more... )
HOUSE_NO ( type: esriFieldTypeString , alias: House Number , length: 18 )
STREET_PFX_DIR ( type: esriFieldTypeString , alias: Street Prefix Direction , length: 5 , Coded Values: , , [NE: North East] , ...5 more... )
STREET_NM ( type: esriFieldTypeString , alias: Street Name , length: 40 )
STREET_NM_SFX ( type: esriFieldTypeString , alias: Street Name Suffix , length: 6 , Coded Values: [ALY: Alley] , [ANX: Annex] , [ARC: Arcade] , ...196 more... )
STREET_SFX_DIR ( type: esriFieldTypeString , alias: Street Suffix Direction , length: 5 , Coded Values: , , [NE: North East] , ...5 more... )
SEC_ADDR_ID ( type: esriFieldTypeString , alias: Secondary Address (unit) , length: 5 , Coded Values: [APT: Apartment] , [BLDG: Building] , [BOX: Box] , ...12 more... )
SEC_ADDR_RANGE ( type: esriFieldTypeString , alias: Secondary Address Range , length: 18 )
SPECIAL_DIRECTIONS ( type: esriFieldTypeString , alias: Special Directions , length: 50 )
BAD_DOG ( type: esriFieldTypeString , alias: Bad Dog , length: 10 )
TECH_SERV_MULTIPLIER ( type: esriFieldTypeString , alias: Tech Services Multiplier , length: 10 )
FLEXNET_ID ( type: esriFieldTypeString , alias: FLEXNET_ID , length: 20 )
Relationships:

Service Location (0) -- Related To: De-Energized Service Locations (8)

Supported Operations:   Query   Query Related Records   Generate Renderer   Return Updates

[JordanBaumgardner1]

Thanks for the reply, I will republish with just those two tables, verify the relate, and test again.

But...

I was thinking, since all I want is that customer name. Would it be possible to preform the query from the parent table?

So search ServiceLocation Joined to Meter where Meter.CustomerName like 'FROST%' and return ServiceLocation Records.

They just want to search on Customer name and zoom to the loc, but they removed customer info from the Geodetic info and grrr...


Thanks in advance.

[JordanBaumgardner1]

Sorry, I didn't answer your question.

Yes, the query executes fine, and returns a header and 0 recs.

http://gisweb:6080/arcgis/rest/services/detailed/MapServer/84/queryRelatedRecords?
outFields=%2A
&f=amf
&relationshipId=0
&returnGeometry=false
&objectIds=278

I've verified through ArcMap that Meter.ObjectID = 278 has a relate and is related to ServiceLocation.ObjectID = 276

The above URL (if I change the reuturn type to json) returns a header and no recs:

{"fields":[{"name":"OBJECTID","type":"esriFieldTypeOID","alias":"OBJECTID"},{"name":"ANCILLARYROLE","type":"esriFieldTypeSmallInteger","alias":"ANCILLARYROLE"},{"name":"ENABLED","type":"esriFieldTypeSmallInteger","alias":"ENABLED"},{"name":"LOCATION_CODE","type":"esriFieldTypeString","alias":"Location Code","length":20},{"name":"PHASE","type":"esriFieldTypeInteger","alias":"Phase"},{"name":"SECTION_ID","type":"esriFieldTypeString","alias":"Line Section ID","length":20},{"name":"ASSEMBLY_CODE","type":"esriFieldTypeString","alias":"Assembly Code","length":20},{"name":"NOMINAL_VOLTAGE","type":"esriFieldTypeInteger","alias":"Nominal Voltage"},{"name":"CREATED_BY","type":"esriFieldTypeString","alias":"Created By","length":40},{"name":"DATE_CREATED","type":"esriFieldTypeDate","alias":"DATE_CREATED","length":36},{"name":"MODIFIED_BY","type":"esriFieldTypeString","alias":"Modified By","length":40},{"name":"DATE_MODIFIED","type":"esriFieldTypeDate","alias":"DATE_MODIFIED","length":36},{"name":"LABEL_TEXT","type":"esriFieldTypeString","alias":"Label Text","length":80},{"name":"SYMBOL_ROTATION","type":"esriFieldTypeDouble","alias":"Symbol_Rotation"},{"name":"FEEDER_ID","type":"esriFieldTypeString","alias":"Feeder ID","length":20},{"name":"FEEDER_ID2","type":"esriFieldTypeString","alias":"Feeder ID2","length":20},{"name":"FEEDER_INFO","type":"esriFieldTypeInteger","alias":"Feeder Info"},{"name":"TRACE_WEIGHT","type":"esriFieldTypeInteger","alias":"Trace Weight"},{"name":"IPID","type":"esriFieldTypeInteger","alias":"FRAMME IPID - (for Data Migration)"},{"name":"NODE_1","type":"esriFieldTypeInteger","alias":"FRAMME Node #1 - (for Data Migration)"},{"name":"NODE_2","type":"esriFieldTypeInteger","alias":"FRAMME Node #2 - (for Data Migration)"},{"name":"SUBTYPE","type":"esriFieldTypeInteger","alias":"Subtype"},{"name":"COUNTY_CODE","type":"esriFieldTypeInteger","alias":"County Code"},{"name":"SUBDIVISION","type":"esriFieldTypeString","alias":"SUBDIVISION","length":40},{"name":"LOT_NUMBER","type":"esriFieldTypeString","alias":"Lot Number","length":10},{"name":"BLOCK_NUMBER","type":"esriFieldTypeString","alias":"Block Number","length":10},{"name":"MUNICIPALITY","type":"esriFieldTypeString","alias":"MUNICIPALITY","length":30},{"name":"DISTRICT","type":"esriFieldTypeString","alias":"DISTRICT","length":30},{"name":"METER_COUNT","type":"esriFieldTypeInteger","alias":"Meter Count"},{"name":"GPS_X_COORD","type":"esriFieldTypeDouble","alias":"GPS X Coordinate"},{"name":"GPS_Y_COORD","type":"esriFieldTypeDouble","alias":"GPS Y Coordinate"},{"name":"TRANSFORMER_NUMBER","type":"esriFieldTypeString","alias":"Transformer Number","length":20},{"name":"TRANSFORMER_ID","type":"esriFieldTypeInteger","alias":"Transformer ID (Foreign Key)"},{"name":"PEDESTAL_ID","type":"esriFieldTypeInteger","alias":"Pedestal ID (Foreign Key)"},{"name":"POLE_ID","type":"esriFieldTypeInteger","alias":"Pole ID (Foreign Key)"},{"name":"WORKREQUESTID","type":"esriFieldTypeString","alias":"Work Request ID","length":20},{"name":"DESIGNID","type":"esriFieldTypeString","alias":"Design ID","length":20},{"name":"WORKLOCATIONID","type":"esriFieldTypeString","alias":"Work Location ID","length":20},{"name":"WORKFLOWSTATUS","type":"esriFieldTypeInteger","alias":"Work Flow Status"},{"name":"WORKFUNCTION","type":"esriFieldTypeInteger","alias":"Work Function"},{"name":"JDE_WO_ID","type":"esriFieldTypeString","alias":"JDE_WO_ID","length":20},{"name":"DATE_INSERVICE","type":"esriFieldTypeDate","alias":"Date Inservice","length":36},{"name":"OLD_SECTION_ID","type":"esriFieldTypeString","alias":"Old Line Section ID","length":20},{"name":"CLASS","type":"esriFieldTypeString","alias":"Class","length":5},{"name":"SUBCLASS","type":"esriFieldTypeString","alias":"Subclass","length":5}]
,"relatedRecordGroups":[]}

[RobertScheitlin__GISP]

Jordan,

   Yep time to give Tech Support a call.

[JordanBaumgardner · Accepted solution]

We were able to solve this problem by creating an Oracle View, and pointing the layer to that. Once that was setup, the search just became an normal Layer search.