Skip to main content
Solved

FSM6 - Mass Insert from XML Hierarchy Select

  • February 23, 2021
  • 7 replies
  • 724 views
R
Hero
Forum|alt.badge.img+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

 

Best answer by AdrianEgley

@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

View original
    Did this topic help you find an answer to your question?
    This topic has been closed for comments

    7 replies

    AdrianEgley
    Hero (Customer)
    Forum|alt.badge.img+14
    • AdrianEgley
    • Hero (Customer)
    • 169 replies
    • February 23, 2021

    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
    Hero (Customer)
    Forum|alt.badge.img+14
    • AdrianEgley
    • Hero (Customer)
    • 169 replies
    • Answer
    • February 23, 2021

    @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

    ksodhi
    1 person likes this
    • ksodhi
      ksodhi
    R
    Hero
    Forum|alt.badge.img+8
    • Rwjgoedhart
    • Author
    • Hero
    • 30 replies
    • February 23, 2021

    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.

     

    AdrianEgley
    1 person likes this
    • AdrianEgley
      AdrianEgley
    S
    Do Gooder (Employee)
    Forum|alt.badge.img+6
    • Sven Paul
    • Do Gooder (Employee)
    • 30 replies
    • March 1, 2021

    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
    A
    2 people like this
    • R
      Rwjgoedhart
    • A
      Abrar khan
    R
    Hero (Employee)
    Forum|alt.badge.img+13
    • ruben.maas
    • Hero (Employee)
    • 124 replies
    • March 3, 2021

    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

    jevflk
    S
    JuniSihombing
    3 people like this
    • jevflk
      jevflk
    • S
      Sven Paul
    • JuniSihombing
      JuniSihombing
    ksodhi
    Do Gooder (Partner)
    Forum|alt.badge.img+3
    • ksodhi
    • Do Gooder (Partner)
    • 5 replies
    • May 7, 2021
    Sven Paul wrote:

    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
    Do Gooder (Partner)
    Forum|alt.badge.img+3
    • ksodhi
    • Do Gooder (Partner)
    • 5 replies
    • May 7, 2021
    ruben.maas wrote:

    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

    Cookie policy

    We use cookies to enhance and personalize your experience. If you accept you agree to our full cookie policy. Learn more about our cookies.

     
    Cookie settings