Kumar A. ADempiere 3.6 Cookbook

Подождите немного. Документ загружается.

ADempiere Customization – Part I

Here, I have removed the participant column and added the ad_user_id column,

which has a foreign key relationship with the adempiere.ad_user table.

2. Log in as System/System with the System Administrator role.

3. Go to the Window Tab & Field window and look at the detail of the Minutes Of

Meeting window.

4. Select the Participants tab on the Tab tab and go to the Field tab.

5. Remove the Participant eld from the list.

6. Go to the Table and Column window and go to the c_mom_participantsline

table entry, as shown in the following screenshot:

Chapter 2

7. Click on the Create Columns from DB button. This will create the new column AD_

User_ID, as shown in the following screenshot:

8. Verify that there is no data in the c_mom_participantsline table and that the

Participant column is not referenced elsewhere and also remove Participant from

the Column list.

ADempiere Customization – Part I

9. Select the AD_User_ID column and specify the following values for the two important

elds, which are more relevant in this context (search widget):

 Reference: Select Search

 Reference Key: Select AD_User

10. Go to the Window, Tab, and Field window and look at the details of the Minutes Of

Meeting window.

Chapter 2

11. Select the Participants tab on the Tab tab and view its details in single row view. You

will see the Create Fields button, as shown in the following screenshot:

12. Click on Create Fields button so that the newly added User/Contact column is

added as a eld.

13. Change the sequence of the User/Contact eld such that it comes before Company.

14. Log out and log in as GardenAdmin/GardenAdmin with the GardenWorld Admin role.

15. Go to the Minutes Of Meeting window and go to the Participants tab. You will

notice that the User/Contact eld is populated, by default, with the current logged

in username and a search button appears next to the eld, as shown in the

following screenshot:

ADempiere Customization – Part I

16. Click on the search button. This will pop-up the Info User/Contact window

and will allow you to search and select the intended user, as shown in the

following screenshot:

17. Click on the tick button. You will see the selected user appearing in the User/Contact

eld. So, the Participants tab will look more connected with ADempiere, as shown in

the following screenshot:

Chapter 2

18. Make similar changes to the Chairperson eld on the MOM tab, and the Chairperson

eld appears as a search eld, as shown in the following screenshot:

19. Similarly, make changes to the Actioned By eld on the Discussion Detail tab, and

the Actioned By eld appears as a search eld, as shown in the following screenshot:

ADempiere Customization – Part I

Here is the nal schema:

CREATE TABLE adempiere.c_mom (

c_mom_id numeric(10,0) NOT NULL,

ad_client_id numeric(10,0) NOT NULL,

ad_org_id numeric(10,0) NOT NULL,

isactive character(1) DEFAULT 'Y'::bpchar NOT NULL,

created timestamp without time zone DEFAULT now() NOT NULL,

createdby numeric(10,0) NOT NULL,

updated timestamp without time zone DEFAULT now() NOT NULL,

updatedby numeric(10,0) NOT NULL,

value character varying(30) NOT NULL,

name character varying(255) NOT NULL,

start_date date NOT NULL,

start_time timestamp without time zone NOT NULL,

end_time timestamp without time zone NOT NULL,

ad_user_id numeric(10),

agenda character varying(4000),

CONSTRAINT c_mom_pkey PRIMARY KEY (c_mom_id),

CONSTRAINT cmom_aduser FOREIGN KEY (ad_user_id)

REFERENCES adempiere.ad_user (ad_user_id) MATCH SIMPLE

ON UPDATE NO ACTION ON DELETE NO ACTION DEFERRABLE INITIALLY

DEFERRED

);

CREATE TABLE adempiere.c_mom_discussionline (

c_mom_discussionline_id numeric(10,0) NOT NULL,

c_mom_id numeric(10,0) NOT NULL,

ad_client_id numeric(10,0) NOT NULL,

ad_org_id numeric(10,0) NOT NULL,

isactive character(1) DEFAULT 'Y'::bpchar NOT NULL,

created timestamp without time zone DEFAULT now() NOT NULL,

createdby numeric(10,0) NOT NULL,

updated timestamp without time zone DEFAULT now() NOT NULL,

updatedby numeric(10,0) NOT NULL,

item_nbr numeric (10,0) NOT NULL,

discussion_desc character varying(2000),

ad_user_id numeric(10),

status character varying(80),

CONSTRAINT cmom_cdiscussionline FOREIGN KEY (c_mom_id)

Chapter 2

REFERENCES adempiere.c_mom (c_mom_id) MATCH SIMPLE

ON UPDATE NO ACTION ON DELETE NO ACTION DEFERRABLE INITIALLY

DEFERRED,

CONSTRAINT cmomdl_aduser FOREIGN KEY (ad_user_id)

REFERENCES adempiere.ad_user (ad_user_id) MATCH SIMPLE

ON UPDATE NO ACTION ON DELETE NO ACTION DEFERRABLE INITIALLY

DEFERRED

);

CREATE TABLE adempiere.c_mom_participantsline (

c_mom_participantsline_id numeric(10,0) NOT NULL,

c_mom_id numeric(10,0) NOT NULL,

ad_client_id numeric(10,0) NOT NULL,

ad_org_id numeric(10,0) NOT NULL,

created timestamp without time zone DEFAULT now() NOT NULL,

createdby numeric(10,0) NOT NULL,

updated timestamp without time zone DEFAULT now() NOT NULL,

updatedby numeric(10,0) NOT NULL,

ad_user_id numeric(10),

company character varying(80) NOT NULL,

CONSTRAINT cmom_cparticipantslinemom FOREIGN KEY (c_mom_id)

REFERENCES adempiere.c_mom (c_mom_id) MATCH SIMPLE

ON UPDATE NO ACTION ON DELETE NO ACTION DEFERRABLE INITIALLY

DEFERRED,

CONSTRAINT cmom_aduser FOREIGN KEY (ad_user_id)

REFERENCES adempiere.ad_user (ad_user_id) MATCH SIMPLE

ON UPDATE NO ACTION ON DELETE NO ACTION DEFERRABLE INITIALLY

DEFERRED

);

How it works...

In this recipe, we replaced the free text user columns in the c_mom_participantsline and

c_mom_discussionline tables with ad_user_id, which is having a foreign key reference

to the AD_User table. The AD_User table contains the ADempiere user detail. This is not a

mandatory step. However, from a good database design perspective, it is advisable that we

save the ID of an entity, which is referred to in a table and also has the constraint (foreign key)

in place.

ADempiere Customization – Part I

In step 9, we had to set the Reference to Search and Reference Key to AD_User for the

User/Contact eld on the Participants tab, Actioned By on the Discussion Detail tab, and

Chairperson on the MOM tab. ADempiere interprets the Reference—Search—and displays a

search button ( ) next to the eld. By clicking on it, ADempiere takes care of showing the

Info User/Contact pop-up to allow the user to search for a user and select a user from the

search result.

Populating the combo-box list

There is a Status eld on the Discussion Detail tab. Currently, this eld accepts free text.

For various practical reasons, such as, to maintain the consistency in communication, to

support project guidelines, to have clean data for further analysis and reporting, and so on,

it makes sense to convert the Status eld into a combo-box and the possible values are listed

for the user selection. In this recipe, we will follow the steps to convert the Status eld into a

combo-box and congure it in such a way that it is populated with values.

Getting ready

Drop the status column by executing the following SQL:

ALTER TABLE c_mom_discussionline DROP COLUMN status;

How to do it...

1. Create a new table, c_momstatus, to capture the list of status values by executing

the following SQL:

CREATE TABLE adempiere.c_momstatus

(

c_momstatus_id numeric(10) NOT NULL,

ad_client_id numeric(10) NOT NULL,

ad_org_id numeric(10) NOT NULL,

isactive character(1) NOT NULL DEFAULT 'Y'::bpchar,

created timestamp without time zone NOT NULL DEFAULT now(),

createdby numeric(10) NOT NULL,

updated timestamp without time zone NOT NULL DEFAULT now(),

updatedby numeric(10) NOT NULL,

"name" character varying(60) NOT NULL,

description character varying(255),

isdefault character(1) NOT NULL DEFAULT 'N'::bpchar,

"value" character varying(40) NOT NULL,

CONSTRAINT c_momstatus_pkey PRIMARY KEY (c_momstatus_id),

CONSTRAINT c_momstatus_isactive_check CHECK (isactive = ANY

(ARRAY['Y'::bpchar, 'N'::bpchar]))

);

Chapter 2

2. Modify the c_mom_discussionline table to add a new column c_momstatus_id,

which has a foreign key relationship with the c_momstatus table, by executing the

following SQLs:

ALTER TABLE adempiere.c_mom_discussionline ADD COLUMN c_momstatus_

id numeric(10);

ALTER TABLE adempiere.c_mom_discussionline ADD CONSTRAINT cmomdl_

cmomstatus FOREIGN KEY (c_momstatus_id) REFERENCES adempiere.c_

momstatus (c_momstatus_id) MATCH SIMPLE ON UPDATE NO ACTION ON

DELETE NO ACTION DEFERRABLE INITIALLY DEFERRED

3. Log in as System/System with the System Administrator role.

4. Set up the table and column detail for the c_momstatus table using the Table and

Column window.

5. Set up the window with a tab and the elds for the c_momstatus table using the

Window, Tab & Field window.

6. Click on Application Dictionary | Reference. This will show the Reference window

and will allow the user to create a new one. Fill in the detail on the Reference tab

and click on the save icon to create a reference for our MOM status, as shown in the

following screenshot:

Since we had selected Table Validation as the Validation Type, the Table Validation

tab is enabled (red colored cross disappears).