"Special" Calcfield

fufikk

Hi,

is it possible to have a calcfield in a table which would require to lookup a value from another table and multiply the result with value from source table?

For example: a vendor table has a field with value used for transport cost calculation (tcc), then an item table should contain a field with value of tcc*average item cost. Vendor Code for item is defined in the item table.

2tje

Have you tried to work this out in Data -scheme?
Sounds like a weird issue.
Have you tried to use the Method Average in the flowfields property?

Joe_Little

No, it is not possible to do this as a calcfield.

David_Cox

The only way I can see it being done is using 2 fields on the Item Table, "Vendor Transport Cost" a flowfield to the Vendor table "Transport Cost" where No. = "Vendor No."

The other the Item Tables Normal field "Item Transport Cost"

Then on a form or report you can just do the calculation on the controls source expression.

"Item Transport Cost"*"Vendor Transport Cost"

Remember to use calcfields onAfterGetRecord.

Joe_Little

That would work. You probably should answer the question "is this data needed by an external application". If so, things become a little more tricky.

Othwerwise, I encourage developers to create a function to return the value which insures that there would only ever be a single definition of this within the system.

If placed in the item table, this can be a paramaterless function which returns the decimal value and can be treated on forms, reports, etc. as though it were a field.

Adding functions to tables is just slightly more difficult to upgrade than having the the function in a custom code unit, but a custom code unit would require a param and wouldn't be as nice to deal with, so it is a weighed decision.

fufikk

Thanks for your replies. I decided to use function in a table to get what I needed.