Conditional layout with dynamic model

In which format should the color be?

HI,

You can use the following code to obtain the correct number to use in the Dynamic model code:

declare @colorToConvert VARCHAR(20) = '1FFFFF'
set @colorToConvert = '0X'+@colorToConvert
select -CONVERT(INT, CONVERT(VARBINARY, @colorToConvert, 1))

Input the color in Hex (without #), output is the negative number you can use in your code.

With the Delete strategy, the Software Factory should delete all present objects that this Dynamic model code created. Can you confirm the Strategy is set to “Deleted”?

Otherwise you can add a "Not exists” clause to ensure no duplicate objects are created. This des come with the downside that any changes to this code will not be applied on already existing objects.

Are you using the "Execute control procedure” button? This button does not remove the generated objects; that only happens during the Generate definition process.

In that case it is more handy to add a “Not exists” clause or converting the code to use Merge statements and use the Fully managed strategy.

Example:

-- Merge conditional_layout
;with current_conditional_layout as 
(
    select *
    from conditional_layout
    where project_id = @project_id
      and project_vrs_id = @project_vrs_id
      and generated_by_control_proc_id = @control_proc_id
)
merge current_conditional_layout t
using (select t.project_id                   as project_id,
              t.project_vrs_id               as project_vrs_id,
              t.tab_id                       as tab_id,
              t.conditional_layout_id        as conditional_layout_id,
              [rest]
              @control_proc_id               as generated_by_control_proc_id
       from tab t) s
   on s.project_id = t.project_id
  and s.project_vrs_id = t.project_vrs_id
  and s.tab_id = t.tab_id 
  and s.conditional_layout_id = t.conditional_layout_id
when matched and (t.show_conditional_layout <> s.show_conditional_layout
               or t.col_id <> s.col_id
               or [rest of columns that might change]) then
    update
    set show_conditional_layout = s.show_conditional_layout,
        col_id = s.col_id,
        [more column]
when not matched by target then
    insert 
    (
        [columns]
    )
    values
    (
        s.project_id,            -- project_id
        s.project_vrs_id,        -- project_vrs_id
        [more columns]
    )
when not matched by source then
    delete;

Rest of columns can be obtained from the table in SQL Server 😀 Hope it helps!