Sat. Feb 27th, 2021

Requirement: I have an Interactive Grid. In that Interactive Grid I have 4 columns: say METAL_COSTMETAL_WEIGHTIS_METAL_FLAGTOTAL_COST

Now, when the User enter values for both the METAL_COST, and METAL_WEIGHT columns, then It should set the IS_METAL_FLAG column value as Y, and also set the TOTAL_COST as METAL_COST * METAL_WEIGHT

In case, if either of the METAL_COST or the METAL_WEIGHT columns has NULL or blank , then It should set the IS_METAL_FLAG column value as N, and should not calculate the TOTAL_COST.

Also, when the IS_METAL_FLAG column value as N, it should Enable the TOTAL_COST field, and when the IS_METAL_FLAG column value as Y is should Disable the TOTAL_COST field.

Sample screen is shown below:

Solution: For solution of the above requirement follow the steps as shown below:

Step 1: Let us first create a MATERIALS table by using the below CREATE Table statement.

CREATE TABLE materials (
    material_id    NUMBER PRIMARY KEY,
    material_name  VARCHAR2(20),
    metal_cost     NUMBER,
    metal_weight   NUMBER,
    is_metal_flag  VARCHAR2(1),
    total_cost     NUMBER
);

Initially, the MATERIALS table will not have any data.

Step 2: We will assume that we already have the Interactive Report displaying the MATERIALS data. Now, when we ran the application, we will have the Materials Interactive Grid displayed as shown below:

Step 3: Now, let us go into the APEX Developer instance.

Select the Region (Materials Interactive Grid) > Expand Columns > Right click METAL_COST > Click Create Dynamic Action

Step 4: Set the following properties for the Dynamic Action.

  • Name as metalCostDA
  • Event as Change
  • Selection Type as Column(s)
  • Region as Materials Interactive Grid
  • Column(s) as METAL_COST

Step 5: Inside True Event Set the below properties:

  • Action as Set Value
  • Set Type as PL/SQL Function Body
  • PL/SQL Function Body as shown below
BEGIN
    IF (:METAL_COST IS NOT NULL AND :METAL_WEIGHT IS NOT NULL) THEN
      RETURN 'Y';
    ELSE
      RETURN 'N';
    END IF;
END;
  • Items to Submit as METAL_COST,METAL_WEIGHT
  • Selection Type under Affected Elements as Column(s)
  • Column(s) as IS_METAL_FLAG

Step 6: Again, Select the Region (Materials Interactive Grid) > Expand Columns > Right click METAL_WEIGHT > Click Create Dynamic Action

Step 7: Set the following properties for the Dynamic Action.

  • Name as metalWeightDA
  • Event as Change
  • Selection Type as Column(s)
  • Region as Materials Interactive Grid
  • Column(s) as METAL_WEIGHT

Step 8: Inside True Event Set the below properties:

  • Action as Set Value
  • Set Type as PL/SQL Function Body
  • PL/SQL Function Body as shown below
BEGIN
    IF (:METAL_COST IS NOT NULL AND :METAL_WEIGHT IS NOT NULL) THEN
      RETURN 'Y';
    ELSE
      RETURN 'N';
    END IF;
END;
  • Items to Submit as METAL_COST,METAL_WEIGHT
  • Selection Type under Affected Elements as Column(s)
  • Column(s) as IS_METAL_FLAG
  • Save the changes

Step 9: Let me set the IS_METAL_FLAG column default value as N.

For that, we will set the Default Type as Static, and the Static Value as N. Save the changes.

Step 10: Now, we will create a Dynamic Action on the IS_METAL_FLAG column.

Let me set the following properties for the Dynamic Action.

  • Name as metalWeightDA
  • Event as Change
  • Selection Type as Column(s)
  • Region as Materials Interactive Grid
  • Column(s) as METAL_WEIGHT
  • Client-Side Condition Type as Item / Column = Value
  • Component Type as Column
  • Column as IS_METAL_FLAG
  • Value as Y

Now , we have to set the formula to calculate the TOTAL_COST as METAL_COST * METAL_WEIGHT.

So, in the True outcome of the isMetalFlagDA, we will set the below properties:

  • Action as Set Value
  • Set Type as PL/SQL Expression
  • PL/SQL Expression as :METAL_COST * :METAL_WEIGHT
  • Items to Submit as METAL_COST,METAL_WEIGHT
  • Selection Type under Affected Elements as Column(s)
  • Column(s) as TOTAL_COST
  • Save the Changes

Next, we need to disable the TOTAL_COST field when the corresponding IS_METAL_FLAG value is Y in that row. For that we have to do the below changes.

  • Create one more True Action for the isMetalFlagDA Dynamic Action
  • Set the Action as Disable
  • Selection Type under Affected Elements as Column(s)
  • Column(s) as TOTAL_COST
  • Save the Changes

Now, we need to enable the TOTAL_COST field when the corresponding IS_METAL_FLAG value is N in that row. For that we have to do the below changes.

  • Create False Action for the isMetalFlagDA Dynamic Action
  • Set the Action as Enable
  • Selection Type under Affected Elements as Column(s)
  • Column(s) as TOTAL_COST
  • Save the Changes

Step 11: Save and Run the application. Thus the ran application is shown below.

Thus, when we left the Metal Cost and Metal Weight values as blank we can see the Is Metal Flag value is N and also the Total Cost will not be calculated. The value of Total Cost as 350 is what we have provided manually. Also, now the Total Cost cell is Enabled.

But, when we provided the Metal Cost value as 200 and Metal Weight value as 1000, then it set the Is Metal Flag value is Y and also calculated the Total Cost as 200000. Also, now the Total Cost cell is Disabled and we cannot modify the Total Cost value for that row.

Hence, the solution to our requirement.

If you like the post please comment, share, and do join me on Facebook. Please subscribe to my YouTube Channel for video tutorials.

Thanks & Regards,
Susanto Paul

 644 total views,  3 views today

By Susanto Paul

Susanto is an Oracle ACE, a qualified MCA, MBA, and a highly-skilled Senior Oracle Specialist. He is an enthusiastic Blogger and YouTuber who helps learners to solve their complex problems more efficiently. He has 9+ years of experience in multiple technologies like AWS, Oracle ADF, Oracle APEX, Oracle JET, Oracle VBCS, Oracle IDCS, Oracle PL/SQL, Oracle Integration Cloud, Java, JDBC, Servlets, JSP, Spring, Hibernate, HTML5, CSS3, JavaScript, TypeScript, NodesJS, Angular, MySQL, Oracle WebLogic Server, JUnit, JMeter, Selenium Web Driver, etc. He is a certified: Oracle Certified Professional Java SE 6 Programmer, Oracle ADF 11g Certified Implementation Specialist, Oracle Cloud Platform Application Integration 2020 Certified Specialist, Oracle Cloud Infrastructure Foundations 2020 Certified Associate, and Oracle Cloud Infrastructure Developer 2020 Certified Associate

2 thoughts on “Calculate the column value based on the other column values of the Interactive Grid in Oracle APEX”
    1. Hi Sir,
      Thank you for your valuable suggestions. I will prepare the solution of the above requirement using the Computations features as suggested by you and will share the link with the community.
      Please do provide your values suggestions and guidance in future also, so that it help us to learn more.
      Thanks & Regards,
      Susanto

Leave a Reply to Susanto Paul Cancel reply

Your email address will not be published. Required fields are marked *