Using calc with field that has not been saved yet on the same form?

[gsudron]

Been searching but not found an answer.

I have a form for a user to book spaces at a dinner. The form has read only fields such as date, cost per head and venue. All good. These come from ___meetingdetails.

The form has a field for the number of guests and I'd like to have the total sum owed be calculated on the fly so that when the user enters say 2 for the umber of guests, the 'total owed' field changes to calculate (the number of guests +1) * cost per head.

I've been trying with a calc field using this code but not having much luck:

$db = JFactory::getDBO();
 
$query = $db->getQuery(true);
$query
->select('cost_per_head')
->from('fab_meetingdetails')
->where('id = {fab_membersbookings___meeting_id_raw}');
 
$db->setQuery($query);
$costperhead = $db->loadResult();
 
$query = $db->getQuery(true);
$query
->select('no_of_guests')
->from('fab_membersbookings')
->where('id = {rowid}');
 
$db->setQuery($query);
$totguests = $db->loadResult();
 
$owed = $costperhead*$totguests;
return $owed;

I'm guessing this isn't working because I'm referencing a field (no_of_guests) in a row that hasn't yet been saved. Is there a better way I can do this?

TIA.