FSM6 - Mass Insert from XML Hierarchy Select

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

Page 1 / 1

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

@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

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.

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

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>

<hierarchy_select>

<primary_table>person</primary_table>

<attrs>

<attr>person.person_id</attr>

</attrs>

<from>

<table>person</table>

</from>

</hierarchy_select>

</select>

<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