Solved

Copy record including details

Tejo van de Bor
Rank
Userlevel 2
Badge +5

hi,

We would like to copy a record with 5 detail tabs, 100+ columns. Is there an option to do this without having to write a transaction and updating this every time a column has changed?

icon

Best answer by Mark Jongeling 1 May 2023, 12:21

View original
This topic has been closed for comments

12 replies

Mark Jongeling
Rank
Userlevel 7
Badge +23

Hi Tejo,

You can create a SQL-typed control procedure in which you dynamically assign templates and replace parameters of your control procedure template based on data within your model.

Your template could be something like this:

insert into [TAB]
(
    [COL][COMMA]
)
select 
    [SELECT_COL][SELECT_COMMA]
from [TAB]
where 1=1 -- Makes it easier ;)
  and [WHERE_CLAUSE]

Inside the Control procedure SQL code (Staged strategy), you can add records in #prog_object_item for each time you want to weave a template. Add records into #prog_object_item_parmtr  for each parameter you want to replace.

Most of the generated control procedures we distribute use this method. You can disable the Hide generated prefilter to see these control procedures.

Hope this gives some inspiration 😄

 

"Wake me up before you low-code"
Tejo van de Bor
Rank
Userlevel 2
Badge +5

ha, that’s inspiration allright 😳 I’m gonna spell it out word by word to understand and get back to you, thanks!

Mark Jongeling
Rank
Userlevel 7
Badge +23

Maybe this will give you a head start 😅 

insert into #prog_object_item
(
    prog_object_id,
    prog_object_item_id,
    order_no,
    template_id
)
select  
    'task_copy_data',
    'task_copy_data_' + t.tab_id,    
    10,
    'template_name'
from tab t
where t.model_id = @model_id 
  and t.branch_id = @branch_id
  and t.type_of_table = 0 -- Only tables  

insert into #prog_object_item_parmtr
(
    prog_object_id,
    prog_object_item_id,
    parmtr_id,
    parmtr_value,
    order_no,
    no_line_when_empty
)
select
    'task_copy_data',
    'task_copy_data_' + t.tab_id,  
    'COL',
    c.col_id,
    c.abs_order_no,
    0
from tab t
join col c
  on c.model_id = t.model_id 
 and c.branch_id = t.branch_id
 and c.tab_id = t.tab_id
where t.model_id = @model_id 
  and t.branch_id = @branch_id
  and t.type_of_table = 0 -- Only tables  
  and c.calulated_field_type = 0 -- Only non-calculated fields

insert into #prog_object_item_parmtr
(
    prog_object_id,
    prog_object_item_id,
    parmtr_id,
    parmtr_value,
    order_no,
    no_line_when_empty
)
select
    'task_copy_data',
    'task_copy_data_' + t.tab_id,  
    'COMMA',
    case max(c.abs_order_no) over (partition by c.tab_id)
       when c.abs_order_no then ' '
       else ', '
    end,
    c.abs_order_no,
    0
from tab t
join col c
  on c.model_id = t.model_id 
 and c.branch_id = t.branch_id
 and c.tab_id = t.tab_id
where t.model_id = @model_id 
  and t.branch_id = @branch_id
  and t.type_of_table = 0 -- Only tables  
  and c.calulated_field_type = 0 -- Only non-calculated fields

(This can indeed be quite complex, but this will make is dynamic. So if any table names or column names change, the procedure will be adjusted accordingly)

Note that you may need to look into the ref table to find the references from the object you want to start copying.

"Wake me up before you low-code"
Tejo van de Bor
Rank
Userlevel 2
Badge +5

thanks, I see now how the many task_copy procedures are created in SF! Some homework to do here.

Mark Jongeling
Rank
Userlevel 7
Badge +23

thanks, I see now how the many task_copy procedures are created in SF! Some homework to do here.

It's indeed crazy 😂

Just the SQL code of the Control procedure by itself that weaves in the template code for all the copy tasks is total 2067 lines of code (including comments and break lines). Using 12 different templates, and some other trickery for special situations, it's one of the more complex piece of code in the Software Factory. 

"Wake me up before you low-code"
Mark Jongeling
Rank
Userlevel 7
Badge +23

One last thing, you can utilize the table-valued function dbo.get_children in your code to determine all children of a particular table. You can use it like this:

select *
from dbo.get_children(@model_id, @branch_id, 'Table', 1 -- Pure references

It will return the references, tab_id and the depth of the references.

"Wake me up before you low-code"
Tejo van de Bor
Rank
Userlevel 2
Badge +5

ok, getting the vibe here! I added the c.include_in_copy=1 when getting the columns.

Tejo van de Bor
Rank
Userlevel 2
Badge +5

1 final question, how do you get the initial copy_to_id? in our case these are identity seeds, so when inserting the details, I have to use the main table’s id. Is there a Thinkwise ‘trick’ to get this one? Or just use the @@identity?

Mark Jongeling
Rank
Userlevel 7
Badge +23

We typically use scope_identity()

declare @new_id bigint = scope_identity()

 

"Wake me up before you low-code"
Tejo van de Bor
Rank
Userlevel 2
Badge +5

Hi @Mark Jongeling, any thoughts on the message “The MERGE statement conflicted with the FOREIGN KEY constraint "ref_prog_object_prog_object_item". The conflict occurred in database "SF", table "dbo.prog_object".” when generating definition?

Even with the default code when creating a new procedure.

I’ve added project_id and project_vrs_id (seen in examples with Delete strategy) but this doesn’t help

added task control procedure:

 

changed default code

using 2022.2 up to hotfix 20230313

Mark Jongeling
Rank
Userlevel 7
Badge +23

Hi Tejo,

It looks like the Prog object is not created yet. If you generate the code group "Tasks" that should fix it. If it doesn't, you may not have a task called test (object name task_test). Create that task, generate code group and the code should run successfully.

"Wake me up before you low-code"
Tejo van de Bor
Rank
Userlevel 2
Badge +5

jep, found it, typo in task name. Grazi!

Terms & ConditionsCookie settings