There is a Status field on the Discussion Detail tab. Currently, this field 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 field 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 field into a combo-box and configure it in such a way that it is populated with values.
Drop the status column by executing the following SQL:
ALTER TABLE c_mom_discussionline DROP COLUMN status;
- 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])) );
- Modify the
c_mom_discussionlinetable to add a new columnc_momstatus_id, which has a foreign key relationship with thec_momstatustable, 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
- Log in as System/System with the System Administrator role.
- Set up the table and column detail for the
c_momstatustable using the Table and Column window. - Set up the window with a tab and the fields for the
c_momstatustable using the Window, Tab & Field window. - 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).
- Enter the Table, Key column, and Display column, besides other details, on the Table Validation tab, as shown in the following screenshot:
- In the Sql ORDER BY, I have mentioned the ORDER BY clause to sort the values when they appear in the drop down. I am sorting them based on their name.
- Log out and log in again as System/System with the System Administrator role.
- On the Table and Column window, look at the detail of the
c_mom_discussionlinetable and go to thec_momstatus_IDcolumn detail on the Column tab. Enter the following field values:- Reference: Select Table
- Reference Key: Select MOM_Status (this is the Reference that we have created)
The following screenshot shows the fields and values entered:
- Create a Menu by name MOM Status as we created the one in the Create a new window recipe.
- Log out and log in as GardenAdmin/GardenAdmin with the GardenWorld Admin role.
- Go to the MOM Status window and create different statuses. For example, Let us say that Open is marked as the default status, as shown in the following screenshot:
- Go to the Minutes Of Meeting window and go to the Discussion Detail tab. The Status field is now a combo-box and all the statuses, which we created in the previous step, are available in the drop-down list. As Open is set as default, when we create a new discussion line item, the Status field will be populated with Open, by default, as shown in the following screenshot:
In step 1, we created a new table c_momstatus where the MOM statuses will be saved. The status column in c_mom_dicussionline has been modified in step 2 so that the column is now linked with the c_momstatus. In the steps 3 through to 8, we created a Reference MOM_Status that gets the records from the c_momstatus table. This reference is then used on the Status column on the discussion detail table in the dictionary for which the Reference is set to Table and the Reference Key is set to MOM_Status. This way, the Status field is rendered as a combo-box and the values are populated from the entries in the c_momstatus table.