[ic] mv_metadata & derived (calculated) data

[interchange-users@icdevgroup.org (David Kelly)]

On Thu, 13 Jun 2002 11:04 am, Mike Heins wrote:
--snip
> >
> > 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.

Ok great! .... that is a good idea - never thought of coming at it from that 
angle.   The one question that I still have about this solution though is:

In reference to the previous example from above - the item_edit page uses the 
table editor tag to display the edit form on the page.   The table editor tag 
looks up the mv_metadata->products record and checks the "option" field for a 
list of table fields that are to be displayed by the table editor tag.   

How would I specify in the mv_metadata -> "products" record that to display 
the retail price it needs to use this new mv_metadata record "na:na"?  If I 
just list 'na' along with the other fields in the 'option' field then it just 
seems to ignore it?   

Do I need to alter the table editor tag so that when it comes across a entry 
under 'options' that is not a valid field in the database table it first 
checks the mv_metadata for a valid record for it before it just ignores it?

Hope that makes sense.

Cheers

-- 
David Kelly
CEO / Development Team Leader
Zeald Ltd