HI Everyone,
We are using apex 4.2 and starting to rewrite some existing applications originally designed in 3.0. I was wondering if anyone could help me with the following scenario.
I have a multi-record tabular form based on a collection called 'species_collection'. The form allows fisherman the ability to create a electronic trip ticket which contains a species, quantity, price, total dollars along with other descriptive information on the species. I have created (with much help from this forum) a dynamic action to automatically update the collection when a field is changed.
I am now hoping to create a dynamic action that will automatically do the following:
- when quantity is changed, recalculate total dollars as quantity*price
- when price is changed, recalculate total dollars as quantity*price
- when dollars changed, recalculate price as quantity/total dollars.
- when total dollars is changed, recalculate the GRAND TOTAL
currently, I use embedded calls to javascript and then to application processes...but these are difficult to debug, and it seems that a dynamic action might be cleaner and simpler.
the current query is (I have only included the relevant fields as this is quite a large query):
SELECT
apex_item.text(1,seq_id,'','','id="f01_'||seq_id,'','') "DeleteRow",
seq_id,
seq_id display_seq_id,
.....
apex_item.text(10,TO_NUMBER(c010),5,null, 'onchange="setTotal('||seq_id||')"','f10_'||seq_id,'') Quantity,
apex_item.text(11,TO_NUMBER(c011),5,null,'onchange="getPriceBoundaries('||seq_id||')"','f11_'||seq_id,'') Price,
apex_item.text(12, TO_NUMBER(c012),5,null, 'onchange="changePrice
('||seq_id||')" onKeyDown="selectDollarsFocus('||seq_id||',event);"','f12_'||seq_id,'') Dollars
......
from apex_collections
where collection_name = 'SPECIES_COLLECTION' order by seq_id
each field, QUANTITY, PRICE, DOLLARS has an ONCHANGE which in turn call application processes which update the collection.
<script language="JavaScript1.1" type="text/javascript">
function setTotal(row)
{
//quantity was entered into form, get values
var price = $x('f11_'+row);
var total = $x('f12_'+row);
var quantity = $x('f10_'+row);
var nquantity = parseFloat(quantity.value);
var ntotal;
var nprice;
nquantity = nquantity.toFixed(3);
quantity.value = nquantity;
//if quantity and price both have values calculate total and save
if(quantity.value > 0 && price.value > 0)
{
ntotal = quantity.value * price.value;
total.value = ntotal.toFixed(2);
}
else
{
//if quantity and total both have values calculate price and save
if(quantity.value > 0 && total.value > 0)
{
nprice = total.value/quantity.value;
price.value = nprice.toFixed(6);
//check to see if the price entered falls within min/max for that species
var get = new htmldb_Get(null,&APP_ID.,'APPLICATION_PROCESS=PriceBoard',0);
get.add('SPECIESPRICE',price.value);
get.add('SEQUENCEID',row);
gReturn = get.get();
if ((gReturn == 'Price entered is too high') || (gReturn == 'Price entered is too low')){alert(gReturn);}
}
else if (quantity.value > 0)
total.value = '';
else
{
total.value = '';
quantity.value = '';
}
}
//saveQPD(row);
setOverallTotal();
}
function setOverallTotal()
{
var total = 0;
var nTotal;
for(i=1;i<=rowCount;i++)
{
if(parseFloat($x('f12_'+i).value) > 0)
{
total = total + parseFloat($x('f12_'+i).value);
}
}
ntotal = total.toFixed(2);
document.getElementById("P110_TOTAL").value = ntotal;
var get = new htmldb_Get(null,&APP_ID.,'APPLICATION_PROCESS=nullProcess',0);
get.add('P110_TOTAL',ntotal);
gReturn = get.get();
}
function getPriceBoundaries(row)
{
//price was entered into form get all values
var quantity = $x('f10_'+row);
var price = $x('f11_'+row);
var total = $x('f12_'+row);
var ntotal;
var nquantity;
var nprice = parseFloat(price.value);
nprice = nprice.toFixed(6);
price.value = nprice;
//check to see if the price entered falls within min/max for that species
var get = new htmldb_Get(null,&APP_ID.,'APPLICATION_PROCESS=PriceBoard',0);
get.add('SPECIESPRICE',price.value);
get.add('SEQUENCEID',row);
gReturn = get.get();
if ((gReturn == 'Price entered is too high') || (gReturn == 'Price entered is too low')){alert(gReturn);}
//if quantity and price both have a value calculate the total
if(quantity.value > 0 && price.value > 0)
{
ntotal = quantity.value * price.value;
total.value = ntotal.toFixed(2);
}
else
{
//if total and price both have a value calculate the quantity
if(total.value > 0 && price.value > 0)
{
nquantity = total.value/price.value;
quantity.value = nquantity.toFixed(3);
}
else
{
if(price.value > 0)
total.value = '';
else
{
total.value = '';
price.value = '';
}
}
}
saveQPD(row);
setOverallTotal();
}
function saveQPD(row)
{
var quantity = $x('f10_'+row).value;
var price = $x('f11_'+row).value;
var total = $x('f12_'+row).value;
//save quantity
var get = new htmldb_Get(null,&APP_ID.,'APPLICATION_PROCESS=setQuantity',0);
get.add('SETVALUE',quantity);
get.add('SEQUENCEID',row);
gReturn = get.get();
// alert(gReturn);
//save price
get = new htmldb_Get(null,&APP_ID.,'APPLICATION_PROCESS=setPrice',0);
get.add('SETVALUE',price);
get.add('SEQUENCEID',row);
gReturn = get.get();
// alert(gReturn);
//save total
var get = new htmldb_Get(null,&APP_ID.,'APPLICATION_PROCESS=setTotal',0);
get.add('SETVALUE',total);
get.add('SEQUENCEID',row);
gReturn = get.get();
// alert(gReturn);
}
function changePrice(row)
{
//total was entered get all rows
var quantity = $x('f10_'+row);
var price = $x('f11_'+row);
var total = $x('f12_'+row);
var ntotal = parseFloat(total.value);
var nprice;
var nquantity;
ntotal = ntotal.toFixed(2);
total.value = ntotal;
//if quantity and total were entered calculate price.
if (quantity.value > 0 && total.value > 0)
{
nprice = total.value / quantity.value;
price.value = nprice.toFixed(6);
var get = new htmldb_Get(null,&APP_ID.,'APPLICATION_PROCESS=PriceBoard',0);
get.add('SPECIESPRICE',price.value);
get.add('SEQUENCEID',row);
gReturn = get.get();
if ((gReturn == 'Price entered is too high') || (gReturn == 'Price entered is too low')){alert(gReturn);}
}
//if price and total were entered calculate quantity.
if (price.value > 0 && total.value > 0)
{
nquantity = total.value / price.value;
quantity.value = nquantity.toFixed(3);
}
if (price.value > 0 && quantity.value > 0)
{
ntotal = quantity.value * price.value;
total.value = ntotal.toFixed(2);
}
saveQPD(row);
setOverallTotal();
}
function selectDollarsFocus(pRow,event)
{
tabPress = 0;
KeyCheck(event);
if($x('f11_'+ pRow))
{
if(KeyID == 9)
{
$x('f14_'+ pRow).focus();
onFocusAreaFished(pRow);
tabPress = 1;
}
}
else
{
if($x('f18_'+ pRow))
{
if(KeyID == 9)
{
$x('f18_'+ pRow).focus();
tabPress = 1;
}
}
else
{
if(--pRow <= rowCount)
if(KeyID == 9)
{
$x('f08_'+ pRow).focus();
tabPress = 1;
}
}
}
}
</script>
I am not very familiar with javascript...but it seems like there must be a simpler way. Any thoughts on how I could approach this? thank you!