[ic] mv_metadata & derived (calculated) data

[interchange-users@icdevgroup.org (Mike Heins)]

Quoting David Kelly (david@zeald.com):
> On Thu, 13 Jun 2002 3:48 am, Ed LaFrance wrote:
> > At 10:34 AM 06/12/2002 -0400, you wrote:
> > >Quoting David Kelly (david@zeald.com):
> > > > Is it possible to put in mv_metadata an entry that will allow you to
> > >
> > > display a
> > >
> > > > entry with the table editor tag that is derived (calculated) from other
> > > > fields from the table?
> > > >
> > > > For Example:
> > > >
> > > > Under the 'products' entry in the mv_metadata.asc file I can set in the
> > > > 'options' field what fields I want the item_edit page to display.   Is
> > > > it possible to put an entry in there that is the sum of two other
> > > > fields
> > > >
> > > > e.g.
> > > > A
> > > > B
> > > > C=(A+B)
> > > >
> > > > I realise that I can simulate the effect of this by just writing my own
> > >
> > > custom
> > >
> > > > item edit page.   But I am trying to create a more generic and reusable
> > > > solution
> > >
> > >Hmm. Is this designed to allow custom edit pages based on product
> > >type? If it is, it would seem to be an admirable thing to be able
> > >to do, and something like that could be ginned into 4.9 (via table-editor,
> > >probably). I don't think it could be put into 4.8.
> > >
> >
> > How would something like that be handled when entering an new record -
> > javascript? Seems like it could get messy.
> >
>
> Not too sure if you quite understand what I am trying to do.  An example is 
> probably in order.
> 
> In my products table I do not store the retail price, instead the retail price 
> (X) is derived from other information such as the price I paid  the supplier 
> for the product (A) + the exchange rate for the currency (B) + my margin (C) 
> etc.  A, B, C are all values that are stored in the products table, X is 
> derived by applying a calculation to A,B and C.
> 
> When I edit data from the products table (using item_edit) I want to be able 
> to display the sell price (X), but *not* allow the user to edit it (widget 
> type=value).  At the same time I want to be able to display A, B and C so 
> that the user can edit them and so influence the retail price (X).
> 
> In this case we do not need to worry about anything fancy for entering a new 
> record as it just works like in the normal case.  I was hoping that I might 
> be able to do something like this using mv_metadata.  But from the sounds of 
> what Mike is saying, mv_metadata doesen't support anything like this yet.
> 
> Might be a good little project for me for 4.9 :-)

It sounds like a custom widget, which is easy to do in 4.9. Instead
of using the value widget, you would make a custom one which would
expect the "options" field to be set to the expression, or you could
make the expression in a lookup_query. With this:

mv_metadata record:

    code: na::na
    options: cost,exch_rate,margin
    type: calc_price

Somewhere in code/UserTag:

Widget calc_price addAttr
Widget calc_price Routine <<EOR
sub {
    my $opt = shift;
    my $fields = $opt->{options};
    my $key = $opt->{key};
    my $tab = $opt->{table};

    my $db = ::database_exists_ref($tab)
	or return "NO TABLE $tab!";

    my $ary = $db->query("select $fields from $tab where sku = '$key'");
    my $result = $ary->[0]
	    or return '';
    my ($cost, $exch, $margin) = @$result;

    if($margin <= 0 or $margin >= 1) {
	my $msg = 
	    errmsg("margin for %s must be between 0 and 1, is %s", $key, $margin);
	Log($msg);
	return 'ERROR';
    }
    
    return $cost / $margin * $exch;
}
EOR

That even covers multiple table lookups with a fields entry of
"cost,exchange.usd,inventory.margin" and an appropriate query.
You could get more elaborate and template the expression and
the lookup query.

-- 
Mike Heins
Perusion -- Expert Interchange Consulting
phone +1.513.523.7621      <mike@perusion.com>

For a successful technology, reality must take precedence over public
relations, for Nature cannot be fooled. -- Dick Feynman