Foreign Key Relationships to external associations - Is it really this dodgy or is there a better way?

[MattHarding]

Hi All,

Let's picture that I have a GW service called Search Helps and within that service I have a PersonalAreas Entity Set which acts as a search help for drop down fields effectively that need to select a Personal Area.  I have 3 properties, with Personal Area Id and Company Code being the keys, and the text for Personal Area being the 3rd and last property. You really need to filter PersonalAreas by Company Code otherwise it's a fairly useless result set.

Now within a different GW service, I have an entity called Position which has a single key of PositionId.  There is also a a property of company code on this Position.

Now rather than asking the UI developer to get the Company code value and filter PersonalAreas with it directly, I thought it would be nicer for the UI developer just to use the navigation from a specific Position to PossiblePersonalAreas.  e.g. Positions('123')/PossiblePersonalAreas returns the entity set of PersonalAreas filtered by the Position's Company Code.

So with that in mind, I add the external model reference pointing at the search helps service; then create an external association via the sneaky External Associations Editor button (that doesn't appear on the Wizard just to confuse you), and set up my Dependent entity to point at the Search Helps PersonalAreas entity.

At this point I really want to tie the Position's Company Code to the PersonalAreas Company Code, but all I can do is set up a referential constraint of PositionId to CompanyCode or have no referential constraint at all (note - in my example I added the referential constraint as there is an issue with navigation keys being provided to external associations from what I can tell - more about this below).

So, without any other option, I go into my GetEntitySet method of PersonalAreas and write something like the following:

  CASE iv_source_name.
    WHEN 'Position'.
      READ TABLE it_filter_select_options ASSIGNING <filter> INDEX 1.
      CHECK sy-subrc = 0.
      READ TABLE <filter>-select_options assigning <select_option> INDEX 1.
      CHECK sy-subrc = 0.
      position_id = <select_option>-low.
*     Get company code for position
      TRY.
          CREATE OBJECT o_position
            EXPORTING
              i_position_id = position_id.
          o_position->get_cost_centre_information(
            IMPORTING
              e_company_code = company_code
          ).
        CATCH zcx_hr_object.


      ENDTRY.
  endcase.
* Continue with retrieving PersonalAreas for given Company Code
<Remaining code not shown>

e.g. I have to assume CompanyCode is actually "Position Id" when the source of this call is from Position (I could also look at the navigation to find this has come from Position but if you're wondering, unfortunately for external associations, the key is not included in this for some reason but the same followup logic of reloading the object would be required).

So the question I'm hoping the answer to is Yes is: Am I missing something in my understanding here?

Thanks,

Matt

Edit since posting and playing around a bit more: Thinking about this further - it's unfortunate, but the navigation property in odata really implies the dependent entity has knowledge of the principal entity and realistically must know how to instantiate it if it needs to get data based on one of the properties. 

Now while I'm fine with that (sort of) I think it's wrong that the dependent entity needs to have the required key properties to hold the principal entities as it makes something like a generic search help impossible to do with navigation - Maybe I just need to be done with that and expect filters to be used by UI programmers to find foreign key'ed entities....

[MattHarding · Accepted solution]

Okay - due to lack of reply, and based on my edit, I'm assuming this is a bug similar to note 1761919 but with the navigation keys not being provided; with the assumption that yes - you need to retrieve the principal entity, and know about the principal entity (even though it will mean I can't make a nice navigation property to a generic search help like entity).

[Former Member]

Hi Matt,

I'm not surprised that you have an issue with this. One bugbear I have with Gateway is that for some reason the designers don't think any context beyond the immediate preceding navigation node is important. They don't support this within the same service,so it's hardly likley to work in a service reference.

I may be wrong but I don't think GW is OData compliant in this respect. If I have the navigation path /blindservice/FromHereSet(1)/ToAPoints(22)/ToBPoints, it's rather restrictive to say I cannot see the initial key of '1' in the path when I am trying to resolve 'ToBPoints'. If that value - where I started - is key to the context, I have to know it.

Currently the only way to access this context is to embed the 'key history' in the intermediate entities, i.e. /blindservice/FromHereSet(1)/ToAPoints(1,22)/ToBPoints. In my view that is corrupting the URl to suit the limitations of the SAP OData implementation. What's even more confusing/frustrating is that the technical request context appears to be able to store the stacked navigation keys but doesn't make use of this design. 

I feel your pain

Ron.

[MattHarding]

Thanks Ron - Thanks for sharing the pain. I'm only dealing with 1 level but I can see that in the future I would need this type of navigation, so sad it's not really going to work out in today's GW world. For now, I'm just abusing the relationship so that I get the Id I need to retrieve the foreign key, but hopefully there will be a better way in the future to do this though I imagine we'll need to update oData to support target parameters (foreign keys) being passed in the future which is a little less ODBC (the more I use odata, the more it feels like just a different way of doing SQL and not a very nice API language).