Solved

FSM6 - Mass Insert from XML Hierarchy Select

  • 23 February 2021
  • 7 replies
  • 633 views
R
Rank
Userlevel 3
Badge +8

Hi All,

Does anyone have recommendations on how to mass-insert records from a XML Hierarchy select?

 

Our goal is to automatically add “person_place - PORTAL_PLACE” records when a new place is created in FSM, based on the place’s global_name.    

 

i.e.    new place (PlaceA) is added for global_name (Spencer)

  • hierarchy select retrieves all person_id’s for people that have Portal_Place entries for “Spencer”
  • Mass-insert inserts a new person_place record for “PlaceA” for the people identified in the Hierarchy Select.

 

We tried with Mass_Update, which seems to be limited to “update” or “delete” only..  we can’t insert new entries

we tried with Perform_batch, but the insert portion only applies to the 1st result of the hierarchy select (multiple results, always just 1 insert)

 

sample of the perform batch: 

<perform_batch>
<hierarchy_select result_name="ppinsert">
 <primary_table>person_place</primary_table>
 <from>person_place</from>
 <attrs>
  <attr>person_place.person_id</attr>
 </attrs>
 <where>
  <data_constraint>
    <constraint>
    <left_operand>person_place.place_id</left_operand>
    <operator>eq</operator>
    <right_operand>@global_name</right_operand>
   </constraint>
   <constraint>
    <left_operand>person_place.place_relationship</left_operand>
    <operator>eq</operator>
    <right_operand>PORTAL_PLACE</right_operand>
   </constraint>
  </data_constraint>
 </where>
</hierarchy_select>
<update_person_place>
 <person_place>
  <person_id xpath_node="//person_place_hierarchy_select_result[@result_name='ppinsert']/person_place/person_id" />
  <place_relationship>PORTAL_PLACE</place_relationship>
  <place_id>@place_id</place_id> 
  <insert />
 </person_place>
</update_person_place>
</perform_batch>

 

Any advice is appreciated.

 

Sincerely

-Rudy

 

icon

Best answer by AdrianEgley 23 February 2021, 18:39

View original
This topic has been closed for comments

7 replies

AdrianEgley
Rank
Userlevel 5
Badge +14

Hi @Rwjgoedhart,

I think this is slightly similar to what Graeme raised in this post.

You have a hierarchy select that returns ‘x’ rows and you want the <insert /> (or perform) to be actioned on each return.

Only way I have been able to do what you ask above (and what Graeme refers to in his question) is to do that outside of the application. Happy to send through what we do as an example but its not entirely inside the application using Business Rules to trigger the Select and Action.

Regards


Ady

AdrianEgley
Rank
Userlevel 5
Badge +14

@Rwjgoedhart 

 

Another option if you are wanting to use a Business Rule would be to use the <perform_exec_db_edit>.

If this is the final step and you don’t want another action of the back of the insert this would be a candidate.

Essentially a SQL Insert off the back of getting the data you want to insert from a SQL select as opposed to a XML Hierarchy Select.

 

Ady

R
Rank
Userlevel 3
Badge +8

Adrian,


Thank you.   That’s what I was worried about..    we currently use perform_exec_db_edit, and really hoped to switch to a cleaner solution.

 

S
Rank
Userlevel 3
Badge +6

Hi @Rwjgoedhart ,

 

another option could be a combination of update and insert.

Use an user_def YOR field on the person. If a new place has been created, the hierarchial select will identify the person which needs to be updated.

Then create an insert for every person which has been updated. This can also be done over night via a scheduled process.

To make it very clear, you can create another update at the end based on the hierarchial select to clear the YOR field.

I did this very often due the mass insert limitation.

Hope this helps.

Best regards,

Sven

R
Rank
Userlevel 5
Badge +13

Hi, 

 

Unfortunately the baseline mass_update does not support inserts indeed. 

Its always a bit tricky to get around this through configuration. 

 

For several customers we are using a “generic” custom MPM for supporting mass_insert of which you can see an example below. 

 

<perform_mass_insert>

  <parameters>

    <select>

      <hierarchy_select>

        <primary_table>person</primary_table>

        <attrs>

          <attr>person.person_id</attr>

        </attrs>

        <from>

          <table>person</table>

        </from>

        <where />

      </hierarchy_select>

    </select>

    <update>

      <update_person_event>

        <person_event>

          <person_id xpath="person_id"></person_id>

          <event_type>NOTIFY</event_type>

          <insert_update is_initialized="False" />

        </person_event>

     </update_person_event>

    </update>

  </parameters>

</perform_mass_insert>

 

The MPM has two segment, a select and update. 

For each record returned by the select it will execute the update segment. 

The update segment can include any valid fsm xml api, so an insert \ update or possibly a mpm. 

 

However, you’ll need this particular customization installed in order to use the mpm above. 

 

I will ask RnD if they can include this in baseline, but until that time it can be solved easiest using this customization. 

 

Best Regards, 

 

Ruben

ksodhi
Rank
Badge +3

Hi @Rwjgoedhart ,

 

another option could be a combination of update and insert.

Use an user_def YOR field on the person. If a new place has been created, the hierarchial select will identify the person which needs to be updated.

Then create an insert for every person which has been updated. This can also be done over night via a scheduled process.

To make it very clear, you can create another update at the end based on the hierarchial select to clear the YOR field.

I did this very often due the mass insert limitation.

Hope this helps.

Best regards,

Sven

 

I have been noticing some inconsistent behaviour with this method at a client on 6u7.  Code that was working in 6u3 and 6u5, is now intermittent.

ksodhi
Rank
Badge +3

Hi, 

 

Unfortunately the baseline mass_update does not support inserts indeed. 

Its always a bit tricky to get around this through configuration. 

 

For several customers we are using a “generic” custom MPM for supporting mass_insert of which you can see an example below. 

 

<perform_mass_insert>

  <parameters>

    <select>

      <hierarchy_select>

        <primary_table>person</primary_table>

        <attrs>

          <attr>person.person_id</attr>

        </attrs>

        <from>

          <table>person</table>

        </from>

        <where />

      </hierarchy_select>

    </select>

    <update>

      <update_person_event>

        <person_event>

          <person_id xpath="person_id"></person_id>

          <event_type>NOTIFY</event_type>

          <insert_update is_initialized="False" />

        </person_event>

     </update_person_event>

    </update>

  </parameters>

</perform_mass_insert>

 

The MPM has two segment, a select and update. 

For each record returned by the select it will execute the update segment. 

The update segment can include any valid fsm xml api, so an insert \ update or possibly a mpm. 

 

However, you’ll need this particular customization installed in order to use the mpm above. 

 

I will ask RnD if they can include this in baseline, but until that time it can be solved easiest using this customization. 

 

Best Regards, 

 

Ruben


Hi Ruben

I think this is an excellent candidate for inclusion into the baseline product.  This functionality comes up time and time again at clients.

Thanks,
Kev

 

Terms & ConditionsCookie settings