Mind Bender - Variable Fee Tables

[Lee J]

Greetings to All

I have been creating an Estimating system using a Portal to create the line items associated with the Estimate number.

It now seems that my users requrie some form of a fee table to be used rather than entering the line items each time.

My dilema is i have 5 differnet types of fee tables, associated across several differnet customer which may include different items.

so as an exmaple here is what might happen

New Estimate for Customer A preset costs from fee table 1

- Item A, Qty 1, $15.00

- Item B, Qty 1, $15.00

New Estimate for Customer B preet costs from fee table 2

- Item A, Qty 1, $10.00

- Item B, Qty 1, $10.00

If anyone has any ideas on how to tackle fee tables or Matrixes i would love to here from you.

Filemaker Version 5.5 (Windows)

[Ugo DI LUCA]

This one is a common practice, but it depends how and where these fees table are stored.

As an example, a discount policy by Customer could be set in the Customer file, then looked up in the line-item from the Customer_ID.

Then, the product price would be calculated (or scripted) along the lines of :

List Price * :Customer:discount.

Some special prizes could also be stored elsewhere in another table, and looked up in the line-item from the Product_ID.

The previous calc could become :

Case(IsValid(::SpecialPrizes::Product_ID), SpecialPrice,List Price * ::Customer:discount)

Later, some Net prizes by customer could be stored in another table, and looked up in the line-item from a concanation Product_ID&"-"&Customer_ID, so that your calc becomes :

Case(IsValid(::NetPrizes::Product_ID, NetPrice,

Case(IsValid(::SpecialPrizes::Product_ID), SpecialPrice,List Price * ::Customer:discount))

HTH

[Ugo DI LUCA]

One small correction to my answer above, just as to avoid any confusion.

I mentionned lookup fields, but all my calcs where referencing a relationship.

1. List Price * Customerdiscount._lookup

or

2. Case(not IsEmpty(SpecialPrice_lookup), SpecialPrice_lookup, List Price * Customerdiscount._lookup)

or

3.Case(not Is Empty(NetPrice_lookup), NetPrice_lookup,

Case(not IsEmpty(SpecialPrice_lookup), SpecialPrice_lookup, List Price * Customerdiscount._lookup))

would be my preferred way for the job...

[Lee J]

OK so where i'm currently doing a lookup via a portal i need to change the fields so they become calculations.

The Fee tables we need are fixed pricing based on a specific product type.

Example

To Mold a Plastic tub for Client A = $10

To Mold a Plastic tub for CLient B = $15

We also have a series of other sub functions which also carry variable values based on the Client.

I'll have a go at your suggestions and see how i get on.

Big thanks

[Lee J]

Still not sure, perhaps i don't have enough Databases to create the many-to-many relationship from within my Estimate portal

Currently i have the following db's.

estimate.fp5

inventory.fp5

customers.fp5

estimate_items.fp5

Do i need another table?

[Ugo DI LUCA]

Perhaps this example I made for you might help you.

It's based on an ordering process, with Products, Customers, and Quantity.

It has 6 files.

A well as a standard List Price, each Product may have :

- a Promotional Price

- a special Price defined for a given list of customers

- a standard Quantity Breaks Rebates

- a custom Quantity Breaks Rebates

By several lookups within the line items, the correct price is finally transfered back, according to the "preferences" set for this customer, this product, and eventually this quantity...

All intermediate lookups are shown in the portal. Start with the Order File, then if you want to change some settings, all is done in the Product File.

It's just a test file. I hope it's clear enough.

[Ugo DI LUCA]

Oops..

Attachment is here...

VariablePrizes.fp5.zip

[Lee J]

Cheers will take a look at the example and report back in the new year.

Enjoy