Defining a relationship

FM8.5
I am having trouble understanding how to set up a relationship between
4 variables.

I want to create an automatic shipping estimate when I get an Order.

I have 1 ZIP code
The ZIP code determines a UPS ZONE - there are 7. The ZONE is one
variable in forming the shipping charge.
The second one is the weight.
The weight is determined by the PRODUCT table. There are seven
products with seven colours. Seven products over 49 skus or product
IDs. The there are 4 pricing structures, meaning the ID numbers grows
to 196.

I was thinking of creating a TABLE called UPS. There would be Zip and
Zone fields. I was thinking of creating a Product UPS field Zone 1, 2,
3, 4, .... But I am not sure there is not a more elegant way to create
this set up.

On Mar 15, 11:19 pm, buck.matthe...@yahoo.com wrote:
> FM8.5
> I am having trouble understanding how to set up a relationship between
> 4 variables.
>
> I want to create an automatic shipping estimate when I get an Order.
>
> I have 1 ZIP code
> The ZIP code determines a UPS ZONE - there are 7. The ZONE is one
> variable in forming the shipping charge.
> The second one is the weight.
> The weight is determined by the PRODUCT table. There are seven
> products with seven colours. Seven products over 49 skus or product
> IDs. The there are 4 pricing structures, meaning the ID numbers grows
> to 196.
>
> I was thinking of creating a TABLE called UPS. There would be Zip and
> Zone fields. I was thinking of creating a Product UPS field Zone 1, 2,
> 3, 4, .... But I am not sure there is not a more elegant way to create
> this set up.
>
> Any suggestions?

tables as follows:

1) zones { zipcode, upszone }
2) upspricelookup { upszone, minweight, maxweight, price}

first yourshipping estimate will lookup the ups zone based on the zip
code
then you will have a multiple criteria relationship defined against
the upspricelookup:

estimator::zone = upspricelookup::upszone
estimator::weight > upspricelookup ::minweight
estimator::weight <= upspricelookup::maxweight

with that relationship defined, you can simply pull in the ship price
from the lookup table.

Be careful when entering the weight ranges. I've set it up to
accomodate pricing defined like...

from 0 - 10, 1$
from 10 - 20, 2$
etc

You want 10 in both records (do not put 10 in one 11 in the next
otherwise 10.5 will have no match; and you want to ensure your
relationship matches the record where its cheaper. e.g. if weight is
10 you want to pay 1$ not 2$. This is why the relationship is defined
as <= on the max bound, and > on the minbound.

One sideeffect, is that if weight is '0' nothing will match. You can
either hack it by setting the lowest range from -1 to 10, or by having
it check for zero weight before doing the lookup and doing something
sensible with, because zero weight is probably an error anyway. But it
will occur is a product doesn't have a proper weight setup, or if
there are no products actually being shipped. [might come up if you
are just sending some paperwork with billing corrections, etc...]

cheers,

On Mar 16, 12:19 am, buck.matthe...@yahoo.com wrote:
> FM8.5
> I am having trouble understanding how to set up a relationship between
> 4 variables.
>
> I want to create an automatic shipping estimate when I get an Order.
>
> I have 1 ZIP code
> The ZIP code determines a UPS ZONE - there are 7. The ZONE is one
> variable in forming the shipping charge.
> The second one is the weight.
> The weight is determined by the PRODUCT table. There are seven
> products with seven colours. Seven products over 49 skus or product
> IDs. The there are 4 pricing structures, meaning the ID numbers grows
> to 196.
>
> I was thinking of creating a TABLE called UPS. There would be Zip and
> Zone fields. I was thinking of creating a Product UPS field Zone 1, 2,
> 3, 4, .... But I am not sure there is not a more elegant way to create
> this set up.
>
> Any suggestions?

It's the weight of the product and the zip code that will determine
the shipping charges, right? Does customers ever order 2 of your
product? Usually shipping isn't double, it's based on the combined
weight.  And what if UPS changes their zoning or weight
requirements?

I think a robust way of doing this would be to create a table of UPS
zones and weights.  Each Line Item would have a weight lookup from the
Products table and total that weight in the Invoice (or Order table).
That field would be used in a script named "Calculate Shipping" which
would simply set the Shipping Cost field in the Invoice table to the
related value in the UPS table.  The relationship would be based on