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?
Solved
Copy record including details
+5- Captain
- 60 replies
Best answer by Mark Jongeling
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.
This topic has been closed for comments
+23Hi 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 š
+5- Captain
- 60 replies
ha, thatās inspiration allright š³ Iām gonna spell it out word by word to understand and get back to you, thanks!
+23Maybe 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.
+5- Captain
- 60 replies
thanks, I see now how the many task_copy procedures are created in SF! Some homework to do here.
1 person likes this
+23Tejo van de Bor wrote:
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.
+23One 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 referencesIt will return the references, tab_id and the depth of the references.
+5- Captain
- 60 replies
ok, getting the vibe here! I added the c.include_in_copy=1 when getting the columns.
1 person likes this
+5- Captain
- 60 replies
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?
+23We typically use scope_identity()
declare @new_id bigint = scope_identity()
"Wake me up before you low-code"
1 person likes this
+5- Captain
- 60 replies
Hi
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
+23Hi 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.
+5- Captain
- 60 replies
jep, found it, typo in task name. Grazi!
1 person likes this
Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.