Dynfields

Creating Dynfields using PL/pgSQL

Example: Update Script

-- upgrade-4.0.0.0.7-4.0.0.0.8.sql
SELECT acs_log__debug('/packages/intranet-cust-company01/sql/postgresql/upgrade/upgrade-4.0.0.0.7-4.0.0.0.8.sql','');

create or replace function inline_0 ()
returns integer as $BODY$
declare
        v_category_id_1           integer;
        v_category_id_2           integer;

begin
        -- Note that the Category Type ('Intranet Brown Company Affiliation') does not ned to be created separately.

        select im_category_find_next_free_id_in_sequence(10000000,99999999) into v_category_id_1;
        PERFORM im_category_new (v_category_id_1,'Brown','Intranet Brown Company Affiliation',null);

        select im_category_find_next_free_id_in_sequence(10000000,99999999) into v_category_id_2;
        PERFORM im_category_new (v_category_id_2,'Brown Frankfurt','Intranet Brown Company Affiliation',null);

        return 1;
end;
$BODY$
language 'plpgsql';
select inline_0 ();
drop function inline_0 ();

-- --------------------------------------------------------------------------------------------------------------------------------

create or replace function inline_0 ()
returns integer as $BODY$
declare
        v_dynfield_id           integer;
begin

        PERFORM im_dynfield_widget__new (
                null,
                'im_dynfield_widget',
                now(),
                0,
                '0.0.0.0',
                null,
                'brown_affiliation',                                                    -- p_widget_name
                'Company Affiliation',                                                 -- p_pretty_name
                'Company Affiliation',                                                 -- p_pretty_plural
                10007,                                                                  -- p_storage_type_id (10007: Table Column, 10005: Multi-Select Mapping Table)
                'integer',                                                              -- p_acs_datatype
                'im_category_tree',                                                     -- p_widget
                'integer',                                                              -- p_sql_datatype
                '{custom {category_type "Intranet Brown Company Affiliation"}}'        -- p_parameters
        );

        return 1;

end;
$BODY$
language 'plpgsql';
select inline_0 ();
drop function inline_0 ();

-- --------------------------------------------------------------------------------------------------------------------------------

create or replace function inline_0 ()
returns integer as $BODY$
declare
        v_dynfield_id           integer;
begin

        -- function im_dynfield_attribute_new returns
        --      id of new dynfield when creation was successful
        --      '1' when dynfield already existed

        SELECT im_dynfield_attribute_new (
                'person',                               -- p_object_type
                'affiliation_id',                       -- p_column_name
                'Affiliation',                          -- p_pretty_name
                'brown_affiliation',                    -- p_widget_name
                'integer',                              -- p_datatype
                'f',                                    -- p_required_p
                 0,                                     -- p_pos_y
                'f',                                    -- p_also_hard_coded_p
                'im_employees'                          -- p_table_name
        ) into v_dynfield_id ;

        RAISE NOTICE 'upgrade-4.0.0.0.7-4.0.0.0.8.sql: Created Dynfield ''Affiliation'' -  v_dynfield_id: %',v_dynfield_id;

        -- 'Edit' Permissions are automaticllay granted by im_dynfield_attribute_new for all users, in some cases you might want to revoke them
        -- PERFORM acs_permission__revoke_permission(v_dynfield_id, (select group_id from groups where group_name='HR Managers'), 'write');

        -- Add table column manually
        begin
                alter table im_employees add column affiliation_id integer;
        exception when others then
                raise notice 'upgrade-4.0.0.0.7-4.0.0.0.8.sql: Could not create column affiliation_id';
        end;

        return 1;

end;$BODY$ language 'plpgsql';
select inline_0 ();
drop function inline_0 ();

Setting mode to 'edit' for all (active) Object-Subtypes

create or replace function inline_1 ()
returns integer as $BODY$
declare
        v_attribute_id          integer;
        v_ctr                   integer;
        r                       record;
        r_outer                 record;
begin
        FOR r_outer IN
                select
                     dyn.attribute_id
                from
                     acs_attributes acs,
                     im_dynfield_attributes dyn
                where
                      acs.object_type = 'im_company'
                      and
                          (
                                acs.pretty_name = 'Company Branch' OR
                                acs.pretty_name = 'Company Sector' OR
                                acs.pretty_name = 'Default Delivery Note Template'
                          )
                      and acs.attribute_id = dyn.acs_attribute_id
        LOOP

                FOR r IN select category_id from im_categories where category_type = 'Intranet Company Type' and enabled_p = 't'
                LOOP
                        select count(*) into v_ctr from im_dynfield_type_attribute_map where attribute_id = r_outer.attribute_id and object_type_id = r.category_id;
                        IF v_ctr = 0 THEN
                                 insert into im_dynfield_type_attribute_map (attribute_id, object_type_id, display_mode) values (r_outer.attribute_id, r.category_id, 'edit');
                        ELSE
                                 update im_dynfield_type_attribute_map set display_mode = 'edit' where attribute_id = r_outer.attribute_id and object_type_id = r.category_id;
                        END IF;
                END LOOP;
        END LOOP;
        return 0;

end;$BODY$ language 'plpgsql';
select inline_1 ();
drop function inline_1();

Adding Dynfields to a Form 

set field_cnt [im_dynfield::append_attributes_to_form \
    -object_subtype_id "" \
    -object_type "person" \
    -form_id $form_id \
    -object_id $employee_id \
]