1 2 Previous Next 20 Replies Latest reply on Jul 14, 2017 10:30 AM by anand_gp

# How to compute 3rd field using values from 2 other fields in tabular form

Hi,

I need help in calculating value for 3rd field using values from other two fields (Apex 4.2.6 Master-Detail form, detail report region).

For example, calculate and assign value to AMOUNT (f04) using other two fields, QUANTITY  (f02) and PRICE (f03).

AMOUNT column will be read-only.  When user inputs or modifies QUANTITY  or PRICE value, the AMOUNT has to be updated.

Thanks,

-Anand

• ###### 1. Re: How to compute 3rd field using values from 2 other fields in tabular form

Hi Anand,

Write a DA - OnBlur on both Quantity and Price, I mean point it to the same function towards both the principle fields.

and then to compute and fill your f04(Amount) and also update as it changes.

Also you could define validations in your function which will be called OnBlur. first check the input. and then do math to update the third field.

Regards,

Zapp

• ###### 2. Re: How to compute 3rd field using values from 2 other fields in tabular form

Hi,

I was looking at similar kind of discussion but could not understand it clearly.   Calculated Column on Tabular Form

What do you mean by dynamic action - OnBlur.  Can u please clarify.

From the above thread, I assume, I should use below code.  But where and how?

```\$('input[name=f02],input[name=f03]').change( function(){
parent_row = \$(this).parents('tr:first');
qty   = ( parent_row.find('input[name=f02]').val() == ' ') ? 0 : parseFloat(parent_row.find('input[name=f02]').val() );
price = ( parent_row.find('input[name=f03]').val() == ' ') ? 0 : parseFloat(parent_row.find('input[name=f03]').val() );
amt = qty * price;
parent_row.find('input[name=f04]').val(amt);
});
```

Thanks,

-Anand

• ###### 3. Re: How to compute 3rd field using values from 2 other fields in tabular form

Hi Anand,

Yes you can use the above code in your Page header. with <script> tags.

My solution contains. creating a DA as below.

Also OnBlur means Lose Focus in Oracle APEX.

And then : Execute JavaScript

--> calling your function within the page to which you will pass, triggeringElement, this.

Or you can directly the code you have.

anand_gp wrote:

1. parent_row=\$(this).parents('tr:first');
2. qty=(parent_row.find('input[name=f02]').val()=='')?0:parseFloat(parent_row.find('input[name=f02]').val());
3. price=(parent_row.find('input[name=f03]').val()=='')?0:parseFloat(parent_row.find('input[name=f03]').val());
4. amt=qty*price;
5. parent_row.find('input[name=f04]').val(amt);

Regards,

Zapp

• ###### 4. Re: How to compute 3rd field using values from 2 other fields in tabular form

Hi Zapp,

I did this (and only this) -

Created dynamic action on the report region -

JavaScript code as (my column numbers are changed) -

```parent_row=\$(this).parents('tr:first');
qty=(parent_row.find('input[name=f06]').val()=='')?0:parseFloat(parent_row.find('input[name=f06]').val());
price=(parent_row.find('input[name=f07]').val()=='')?0:parseFloat(parent_row.find('input[name=f07]').val());
amt=qty*price;
parent_row.find('input[name=f12]').val(amt);
```

and changed Element Attribute of both QUANTITY & PRICE columns to -

```onblur="notNull(this);"
```

Is there anything else I am missing?  Because I don't see any effect of giving values to QUANTITY & PRICE.

Regards,

-Anand

• ###### 5. Re: How to compute 3rd field using values from 2 other fields in tabular form

I also tried this -

```<script type="text/javascript">
function sumItems(){
parent_row=\$(this).parents('tr:first');
qty=(parent_row.find('input[name=f06]').val()=='')?0:parseFloat(parent_row.find('input[name=f06]').val());
price=(parent_row.find('input[name=f07]').val()=='')?0:parseFloat(parent_row.find('input[name=f07]').val());
amt=qty*price;
parent_row.find('input[name=f12]').val(amt);
}
</script>
```

Updated Element Attribute of both QUANTITY & PRICE columns to -

```onchange="sumItems();"
```

But Amount column is not getting updated.

Regards,

-Anand

• ###### 6. Re: How to compute 3rd field using values from 2 other fields in tabular form

Hi Anand,

Did you try to debug it with console.log , or the call going in or not.

Try console logging from DA first.

Add before the execute JS. Check whether you are getting valid id's.

Console.log(this.triggeringElement.id);

Then add alert and same console.log with appropriate parameter to debug the flow.

you can add console.log(parent_row); and check whether its correct or not.

Let me know, where are you stuck. Also pass 'this' to sumItems function.

Regards,

Zapp

• ###### 7. Re: How to compute 3rd field using values from 2 other fields in tabular form

I changed my DA javascript to add console log -

```Console.log(this.triggeringElement.id);
parent_row=\$(this).parents('tr:first');
qty=(parent_row.find('input[name=f06]').val()=='')?0:parseFloat(parent_row.find('input[name=f06]').val());
price=(parent_row.find('input[name=f07]').val()=='')?0:parseFloat(parent_row.find('input[name=f07]').val());
amt=qty*price;
parent_row.find('input[name=f12]').val(amt);
```

and Quantity and price element attribute to -

```onblur="notNull(this);"
```

while running on debug, I changed values of quantity and price and if I look at "View Debut", I don't see anything is being processed.  There's no log of values changed in quantity or price.  Looks like there is some silly mistake happening.  Can you cross verify if my approach as DA and the next one with HTML Header is correct?  And I am not missing anything else?

• ###### 8. Re: How to compute 3rd field using values from 2 other fields in tabular form

Hi Anand,

Not the debug in developer toolbar,

Open Developer Console and go to console.

On browser, right click on web page and inspect element then switch to console tab.

Let me know.

• ###### 9. Re: How to compute 3rd field using values from 2 other fields in tabular form

Hi Zapp,

As soon as I added " alert(this.triggeringElement.Id);  " started getting below pop-up window -

It repeates for some 5 to 6 clicks.

And below is what I see in console -

Regards,

-Anand

• ###### 10. Re: How to compute 3rd field using values from 2 other fields in tabular form

Hi Anand,

I tried it myself, wrote my own code.

var a = this.triggeringElement.id;

var len = a.length;

var b = a.substr(4, len);

var qty_id = 'f01_'+b;

var amt_id = 'f02_'+b;

var total = 'f03_'+b;

var qtyVal = document.getElementById(qty_id).value;

var amtVal = document.getElementById(amt_id).value;

var sum = parseFloat(qtyVal) + parseFloat(amtVal);

\$s(total,sum);

Regards,

Zapp

• ###### 11. Re: How to compute 3rd field using values from 2 other fields in tabular form

Hello Zapp,

Yes, it works.  Thanks.  But one issue.  If I make TOTAL field "Display as Text (saves state)", it shows calculated value during entry / edit. but doesn't save to DB on submit.  Since it is calculated field, I don't wnat to keept TOTAL open for edit.  Is there a way?

Thanks,

-Anand

• ###### 12. Re: How to compute 3rd field using values from 2 other fields in tabular form

Hi Anand,

If you are using APEX_ITEM, you should use its p_attributes and  assign 'DISABLED'.

apex_item.text(

p_idx => 3,

p_value => MRP * QTY, -- calculated field.

p_attributes => 'disabled',

p_item_id => 'f03_00#ROWNUM#') "Gross",

)

You could use a number of other attributes to control your input element.

E.g. Checkbox has Checked or unchecked properties.

Regards,

Zapp

• ###### 13. Re: How to compute 3rd field using values from 2 other fields in tabular form

Hi Zapp,

I don't use APEX_ITEM.  I use simple Master-Detail form created using wizard.  The calculation is at Detail Region (report).  And all three columns QTY,  PRICE & TOTAL are DB columns.

Is there a way to set it as read-only ?

One more issue I noticed that, for new-row, the calculation is not happening.  I created one more DA for new row but no luck -

Name:  Calculate Amount New Row

Event:  Click

Selection type:  Button

True Action:   Execute JavaScript Code

Code:  <the same code you gave>

Thanks,

-Anand

• ###### 14. Re: How to compute 3rd field using values from 2 other fields in tabular form

Hi Anand,

So you can just inside the Execute JavaScript when page loads, In the page properties.

\$(function() {}

);

One more issue I noticed that, for new-row, the calculation is not happening. I created one more DA for new row but no luck -

Name: Calculate Amount New Row

Event: Click

Selection type: Button

True Action: Execute JavaScript Code

Code: <the same code you gave>

The Second thing, you cannot use the same code on a button click. the event should register the current object. in your design its only when you loose focus from the selectors you had selected.

Regards,

Zapp

1 2 Previous Next