8 Replies Latest reply on Feb 15, 2013 2:05 PM by user18951

# Lookup Failing

Probably a basic question, but:

Table Quotes:

pkQuoteID

fkConstructionID

Table Construction:

pkConstructionID

Description

Table UnitPrice:

pkUnitPriceID

fkConstructionID

Quantity

PricePerThousand

Table QuoteQuantityPrice:

fkQuoteID

Quantity

UnitPrice

Extended Price

blah

blah

Each Quote has one construction and can have multiple prices.

The problem is this pricing was set up on a matrix. In the UnitPrice table, there may be a quantity for 500, 1000, and 2500. Each with a seperate per thousand price.

I need to get the unit price into the QuoteQuantityPrice table, but the amount quoted might be 1250 or 2000, and I need to lookup the price for the lower quantity.

I've tried lookups, but I'm not getting anywhere.

Any advice would be appreciated.

• ###### 1. Re: Lookup Failing

Create a separate Table Occurence for UnitPrice called UnitPrice_lookup.

Ensure that there is a record for a quantity of 1 to catch unit prices for quantities before the first price break. (For example, if your first price break is at 500, but only 300 are ordered, you need to have a price for 1-499 -- if your mimumum order of 500, then you'll need other validation in place).

Create a relationship where QuoteQuatityPrice::Quantity >= UnitPrice_lookup::Quantity, and sort UnitPrice_lookup by Quantity DECENDING.  This will find the biggest price break met by the order.

Then from the context of QouteQuantityPrice, you can set the QuoteQuantityPrice::UnitPrice to UnitPrice_lookup::PricePerThousand / 1000

hth,

Mike

• ###### 2. Re: Lookup Failing

Mike -

Another attempt at this this morning.  Read you advice with clearer eyes:

"Create a separate Table Occurence for UnitPrice called UnitPrice_lookup."

"Ensure that there is a record for a quantity of 1 to catch unit prices for quantities before the first price break. (For example, if your first price break is at 500, but only 300 are ordered, you need to have a price for 1-499 -- if your mimumum order of 500, then you'll need other validation in place)."

I will also add other validation later to ensure this doesn't get used.

"Create a relationship where QuoteQuatityPrice::Quantity >= UnitPrice_lookup::Quantity, and sort UnitPrice_lookup by Quantity DECENDING.  This will find the biggest price break met by the order."

Done.   I'm unclear if I'm sorting this right.  I opened a layout based on the lookup TO, which appears to have 55 blank records (??), and applied a sort.

I also sorted the original table in it's layout.

When I re-open it, it no longer appears to be sorted.  I'm doing something wrong here.

"Then from the context of QouteQuantityPrice, you can set the QuoteQuantityPrice::UnitPrice to UnitPrice_lookup::PricePerThousand / 1000"

I'm Just trying to pull the price for this quantity from the table at this point, for troubleshooting.

No matter what construction or quantity Set in my Layout, I keep getting 999 for the price.

Not sure which way to go now.

Any guidance is appreciated.

Dave

• ###### 3. Re: Lookup Failing

Why don't you try a different approach? Instead of populating the field by Lookup, try Autoenter by calculation. Your calculation can then be set to reference the appropriate price, or the prices for different quantities can be set within the calc itself---I assume your logic is some sort of % discount off the base price based on the quantity purchased.

• ###### 4. Re: Lookup Failing

Based on input, that's what I'm now attempting ( I think ). Filemaker is actually more confusing in this aspect than MySQL.

David Goodnature

President & General Manager

Sterling Business Forms, Inc.

(541) 779-3173 tel - fax (541) 857-4128

www.sbfnet.com

• ###### 5. Re: Lookup Failing

Here is a file that does what I suggest. Enter any number in the quantity on the order table and the price that comes through is adjusted as per the autoenter calculation.

• ###### 6. Re: Lookup Failing

I appreciate that input!

Unfortunantly, I the quantity breaks differ based on the construction.

One item might break at 250, 500, 1000 while another may be 600,1200,1800, etc.

They want to be able to alter the price breaks based on what they enter into the construction table with the unitprice portal.

I have much to learn about filemaker!

• ###### 7. Re: Lookup Failing

My test file was merely a starting point. The principle is the same though, but your formula will need to be more complex.

• ###### 8. Re: Lookup Failing

Thank you.  I'll keep trying.