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

[RobertScheitlin__GISP]

Jordan,

   So is 84 the meter or the Service location?

[JordanBaumgardner1]

Thanks for the quick reply Robert.

Yes 84 is my non-spacial table containing the Customer name. It has a relate (0) to Service Location (12)

Electric Dataset (11)
---Service Location (12)
...
Tables:
xxx.METER (84)

And 84 has:
Relationships:
Service Location (0) -- Related To: De-Energized Service Locations (8)

[RobertScheitlin__GISP]

Jordan,

  So are you saying that you are not receiving any results from any of your expressions or is it just the customers name expression?

[JordanBaumgardner1]

The First query works great, I enter Frost, and get 12 records back. When I press the "Related Records" button (Service Location) It preforms the query, and I get a header with no records.

http://gisweb:6080/arcgis/rest/services/detailed/MapServer/84/queryRelatedRecords?relationshipId=0&r...

I changed the Format from AMF to json and received the following:

{"geometryType":"esriGeometryPoint","spatialReference":{"wkid":102667,"latestWkid":2240},"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,

   It is extremely hard to diagnose issue like this without access to your data. The best I can do is provide you this layer configuration that does work and uses esri sample services to test and compare with yours:

        <table>
            <token/>
            <definitionexpression></definitionexpression>
            <enableexport>true</enableexport>
            <enableprintgrid title="Selected SF Incidents">true</enableprintgrid>
            <name>SF Incidents</name>
            <url>http://sampleserver3.arcgisonline.com/ArcGIS/rest/services/SanFrancisco/311Incidents/MapServer/1</url>
            <expressions>
                <expression alias="Search By Object ID" textsearchlabel="Search Incidents By Object ID:">
                    <values>
                        <value prompt="Example: 10302644">sf_311_serviceoid = [value]</value>
                    </values>
                </expression>
            </expressions>
            <titlefield>sf_311_serviceoid</titlefield>
            <fields all="false">
                <field name="sf_311_serviceoid" alias="Incident OID" gridfield="true"/>
                <field name="agree_with_incident" alias="Website Link" hyperlinkgridfield="true"
                    hyperlinkaliastext="Go to Website" 
                    linkprefix="http://someWebSite/aspx/web/details.aspx?p_entity=" 
                    linksuffix=".aspx"/>
                <field name="cient_ip" alias="Client IP" gridfield="true"/>
                <field name="datetime" alias="Date" dateformat="MM/DD/YYYY" useutc="true" gridfield="true"/>
                <field name="notes" alias="Notes" gridfield="true"/>
            </fields>
            <links>
                <link alias="View Traffic Photo" disablelinksifnull="true">
                    <![CDATA[{agree_with_incident}]]>
                    <icon><![CDATA[assets/images/i_camera.png]]></icon>
                </link>
            </links>
            <relates>
                <relate id="1" label="Incident Priority Service Request" enableexport="true" icon="widgets/eSearch/assets/images/i_relate.png"
                    enableprintgrid="true" printitle="Incident Priority Service Request">
                    <fields all="true" />
                </relate>
            </relates>
            <queryattachments>false</queryattachments>
        </table>

[JordanBaumgardner1]

Well, that sample worked really great.

I'll compare the two, and double check my data. I'll be really ticked at them if the relate is there but the data is not populated.

Thanks again. I'll compare, then note my fix here and mark your answer as correct.

[JordanBaumgardner1]

Ok, thanks for the help, I think I found the Issue.

Seems that, although they have a relate, there is no related data. So eSearch is "Working as Designed". "No related records found" is the correct response to "You aint got no data". Looks like we have some Updating to do.

[JordanBaumgardner1]

Well, turns out, there are some relates.

I was able to query the table from ArcMap and find a few examples that have valid relates. And still getting no results on the Related table query:
http://gisweb:6080/arcgis/rest/services/detailed/MapServer/84/queryRelatedRecords?outFields=LOCATION...

276 has a relate to the Service Location OID 278

Any thoughs? I'm rather stuck. Looks like everything is setup properly, your example works fine, relates all there, no errors. Just no data. I feel like I'm missing something stupid.

[RobertScheitlin__GISP]

Jordan,

   So your saying that when you click that link you do not get a result? If this is the case I would look at republishing the service again and make sure that both layers involved in the relate don not have any hidden fields (just for testing) if you still can not get it to work then it is time to call esri tech support.