Solved

Is it possible to hierarchy select a view?

  • 19 October 2021
  • 6 replies
  • 322 views

Userlevel 1
Badge +4

Usually we select tables like this:

<hierarchy_select>
  <attrs>
    <attr>person.person_id</attr>
  </attrs>
  <primary_table>person</primary_table>
  <from>
    <table>person</table>
  </from>
</hierarchy_select>

But I want to select a view in the same manner
<hierarchy_select>
  <attrs>
    <attr>subcon_team_tasks_grid_christine.person_id</attr>
  </attrs>
  <primary_table>subcon_team_tasks_grid_christine</primary_table>
  <from>
    <table>subcon_team_tasks_grid_christine</table>
  </from>
</hierarchy_select>

 

However this gives error:
<metrix_response>
  <result type="Exception">
    <error>
      <system_error>
        <severity>ERROR</severity>
        <message>Unrecognized business object 'subcon_team_tasks_grid_christine'. Contact your system administrator for assistance.</message>
      </system_error>
    </error>
  </result>
</metrix_response>
 

Which I can understand because I am not selecting a table at all, but a view. Is there any way to write something similar to the hierarchy select to select the view instead of a table?

icon

Best answer by Atheeq Maharoof 19 October 2021, 10:33

View original

6 replies

Userlevel 5
Badge +12

Hi @christine.munthe,

Using the hierarchy select you can retrieve the view record. Following is an example for hierarchy select for request_view.

<hierarchy_select>
  <primary_table>request_view</primary_table>
  <attrs>
    <attr>request_view.*</attr>
  </attrs>
  <from>
    <table>request_view</table>
  </from>
  <where />
</hierarchy_select> 

In your scenario, I think the view you are calling is not created in the DB side. Please write a select statement for the above view and try executing in the SQL query tool.  

When I tried to access a view which is not in the DB, the exception you are currently observing can be seen.

 

request_all_view - Incorrect view name

Hope this answer helps.

Best Regards,
Atheeq

Userlevel 1
Badge +4

Hi @christine.munthe,

In your scenario, I think the view you are calling is not created in the DB side. Please write a select statement for the above view and try executing in the SQL query tool.  

Best Regards,
Atheeq

Hi Atheeq! Thank you for your response. I think you are correct. I created the view by accessing my DB remotely and right clicking views, create new view, and I used the designer to create it. I verified that it had been saved before attempting to query.

 

Is this not sufficient? Can you advice how to “create on DB side”?

I checked the view query you gave me in the XML Poster and it worked fine, so accessing veiws is definitely possible. Thank you again.

//Christine

Userlevel 5
Badge +12

Hi @christine.munthe ,

In order to access the view, you have to create a custom metadata. I have created a view called request_all_view (which is used in the initial answer). Following are the steps. 

 

Created the view in the DB side.

Define a custom metadata with the exact view name and the column definitions. 
 

Custom Metadata  - REQUEST_ALL_VIEW

Then refresh the cache and try executing the hierarchy select in the xml poster.

 

XML Poster - hierarchy select - request_all_view

Hope this answer helps

Best Regards,
Atheeq

Badge +3

Hi @christine.munthe, @Atheeq Maharoof 
 

Regarding your question, I would like to add a comment related to my personal experience. 
You should be careful with the XMLs and really make sure that you have the correct documentation.

In that case the request_all_view.* will be fine but in other situations it could really become a problem especially if you have relation at the metadata level.

In my opinion you should be careful with the .* and try to specify as much as you can the column names. Inside the header <hierarchy_select> header you can also add constraints for example like max_rows or return_only_requested_attrs. Especially in the mobile_sync rules cases.

Another personal comment a lot of people tend to use the perform_batch and do quick XML BRs be careful with that especially in high volume inside after_commit execution points. In my opinion this is not really mentioned clearly in the documentation. 

Have a great weekend !

Regards,

Clement

​​​​​​​

Userlevel 1
Badge +4

Hi @christine.munthe@Atheeq Maharoof 
 

In my opinion this is not really mentioned clearly in the documentation. 

Have a great weekend !

Regards,

Clement

 

Hi Clement and thank you for your comment.

What documentation are you referring to? We are basically learning everything by doing, as pretty much any documentation we have been given is very poor and dated 2018.

 Best regards,
Christine

Badge +3

Hi @christine.munthe , 

Do you have the FSM_XML_Message.docx documentation? 
If you do not have it, I think you should ask IFS to give it to you. I could get it in 2017 in a Technical training. 

Regards,

Clement
 

Reply