Need

In some business processes, users must select a value from a reference entity when creating or editing a record in the UI.


However, situations may arise where:

  • The required reference value does not yet exist.
  • The user needs to create this new value immediately.
  • The newly created value must then be used in the current record.


By default, the UI expects the reference value to already exist.

Therefore, the challenge is: how can we allow users to create a parent record dynamically and immediately use it in a referencing record?


Summarized Solution

A practical workaround is to:

  • Create a database function that inserts the missing parent record if it does not already exist.
  • Call this function using a SQL Enricher.
  • Add the enricher to the stepper of the referencing entity.


This approach allows a new parent record to be created automatically while the user is working within the child record, ensuring a seamless UI experience.


Detailed Solution

1. Concept overview

The solution works as follows:

  • The user enters a value that may not exist in the reference entity.
  • A custom function checks whether the value already exists.
  • If it does not exist, the function inserts it into the appropriate source table.
  • An enricher calls this function during the stepper execution.
  • The newly created parent value becomes available for reference.

This avoids requiring the user to leave the current screen to manually create the missing reference.


2. Create the database function

Below is a sample PostgreSQL function:

CREATE OR REPLACE FUNCTION dev5_nee_mdm.usr_create_new_part_new(in_part text, in_manufacturer text, in_loadid numeric)
 RETURNS text
 LANGUAGE plpgsql
AS $function$

begin

insert into dev5_nee_mdm.sa_manufacturer_part
    (b_loadid, b_classname, 
    manufacturer_part,
    f_manufacturer 
    )
select     distinct 
    in_loadid ,
    'ManufacturerPart',
    in_part ,
    in_manufacturer 
from dev5_nee_mdm.gd_manufacturer_part gmp     
where 
    not exists 
    ( select manufacturer_part 
        from dev5_nee_mdm.gd_manufacturer_part smp
        where smp.manufacturer_part= in_part 
    ) and 
    not exists 
    ( select manufacturer_part 
        from dev5_nee_mdm.sa_manufacturer_part sap
        where sap.manufacturer_part= in_part 
    );
return in_part;
    END;

$function$
;

What this function does:

  • Checks if the manufacturer_part already exists:
    • In the Golden Data table (gd_manufacturer_part)
    • In the Source Authoring table (sa_manufacturer_part)
  • If the value does not exist in either table, inserts a new record into the SA table.
  • Returns the input value for reuse.

This ensures no duplicate master values are created.


3. Create the SQL enricher

  • Create a SQL Enricher.
  • Configure it to call the function: pass the required parameters (here NewPartName, FID_Manufacturer, LoadID).
  • Ensure the enricher executes before validation of the reference field (enrichment scope set to "Pre-consolidation only").


4. Add the enricher to the stepper

  • Add the enricher to the stepper of the referencing entity.
  • Ensure it executes at the correct step in the workflow.


5. Check the job used in the stepper

  • As the stepper only contains the child step, the native authoring job won't contain a task for the parent entity.
  • So you'll need to define a dedicated job in the stepper, that will contain both the parent and child entity in the job tasks.


When the user submits the record:

  • The enricher calls the function.
  • If the reference value does not exist, it is created.
  • The current record can then safely reference it.

This effectively enables creating a new parent record "from within" a child record.