Hi everyone,

This is my first post here and I'm hoping someone can help. I'm trying to teach myself how to create FM databases (I've used them a little before).

I've created 3 databases for my husband's small business.

1. Clients (names, addresses, phone, fax, etc.)

2. Products (productID, Cost price and 5 different sale prices (depend on quantity))

3. Invoices

So far, when I enter a client phone number in the Invoices DB, the client's information appears. When I enter a productID #, the product description appears. My trouble is this: I would like the Unit Price of the product to appear in the Invoice depending on the quantity entered. I'm trying to understand the Case function. I've used the following as a Clculated value in the AutoEnter section:

Case( ProdQty ? 5 , GetField(ProductID::SalePrice1) , 5 < ProdQty ? 10, GetField(ProductID::SalePrice2) , GetField(ProductID::SalePrice3))

but when I enter a quantity and a product ID, the unit price remains blank. What am I doing wrong?

Thanks for your help!

I assume those ? are actually <=, then your Case would look like:

Case( ProdQty <= 5 , GetField(ProductID::SalePrice1) , ProdQty <= 10, GetField(ProductID::SalePrice2) , GetField(ProductID::SalePrice3))

Notice the "5 < ProdQty <= 10" is not correct, but you don't need to specify "5 < ProdQty" in the example anyway, since the first part of the Case() would have fired if that was true.

Also, do SalePrice1 etc actually contain field names? I would doubt it - so what you really want is:

Case( ProdQty <= 5 , ProductID::SalePrice1, ProdQty <= 10, ProductID::SalePrice2, ProductID::SalePrice3)

You don't need to use Get Field. When you enter the field, the calculation will assume you mean to get that field's contents.

Try:

Choose( ProdQty / 5 , ProductID::SalePrice1, ProductID::SalePrice2, ProductID::SalePrice3)

Also remember you need to exit the field before it will show.

I've edited mine to mention the GetField() problem also - good catch.

Thank you for such prompt responses. Your help is very much appreciated! I'm doing my best to wrap my head around this challenge.

I've tried it, and it works! Yay!

The only glitch is because I am using a repeating field, when I enter a quantity in the second line, it uses the saleprice from the first item, not from the second item (which is usually a different price). Any suggestions?

On a different note, I would also like to create a fourth DB with our purchases, and then find some way to add up the quantities sold of each product and subtract them from the quantities purchased of each product, thus giving us an idea of inventory. Could someone please point me in the right direction?

Many, many thanks for your insights!

Repeating fields are probably not the right structure here, you should have a portal to a related table that is your invoice. Each line in the portal would do a new lookup based on that portal row's itemID.

Choose( ProdQty / 5 , ProductID::SalePrice1, ProductID::SalePrice2, ProductID::SalePrice3) seems to lend itself to a nice simplification, namely

GetField( "ProductID::SalePrice" & (ProdQty + 5)/5 ), not that I've tested it or anything.

Agreed. Relationships would better suits your need.

I've made a few sampler that compares both methods.

No comparisons in fact.

RepsvsRel

ConditionalRepeatingfields

Wow! I hadn't get gotten to portals yet, and I tried to understand them all day. The attachment "Reps vs. Rel" has really made a huge difference. My most appreciative thanks!!!

Without seeing this example, I would have been tempted to link the portal in "OrderRel" directly to "Catalogue" rather than going through another file called "Line items". I assume this would have been a beginner's error, and I will follow your example. I'm not quite sure I understand it completely though (the use of "line items"). I have a feeling that it will help for eventual calculation of inventory...perhaps?

A great big thank you to everyone who has contributed their expertise to my question.

I'm sure I'll be back soon with more questions of a different nature. I'm an elementary school teacher in Montreal, Quebec, and our school is redesigning our report card. We'd like to be able to select the competencies addressed from the curriculum, and select comments from a bank of comments, as well as add our own observations in a format which expands to fit the amount written by the teacher (not quite sure how to do that yet!). Phew! It'll be quite the challenge, but I'm convinced FileMaker will be a great tool!

Ok, I'm going to overstep my welcome and trouble you for another question. I've pretty much followed the Reps vs Rel model, and created a "line items" file. There is a portal in the "invoices" where the fields from a "Line Items" file are inserted:

-ProductID

-Description

-Quantity

-UnitPrice

-LineAmount

So far so good. (I also learned something new about value lists from the popup lists from source files, thanks!)

HOWEVER, In the "line items" file (which gets the product descriptions and prices from a "products catalogue" file), I'm back to my original question:

The "UnitPrice" still doesn't change according to quantity. I've entered:

Case( Quantity <= 5 , ProductID::SalePrice1, Quantity <= 10, ProductID::SalePrice2, Quantity <=100, ProductID::SalePrice3, Quantity <=500, ProductID::SalePrice4, ProductID::SalePrice5)

but only SalePrice1 ever appears. (incidentally, SalePrice1,2,etc. are stored in the "Productcatalog" file, which is related to the "LineItems" file through a ProductID relationship.) Hmmm... sounds about as clear as mud.

Am I missing something that should be very, very obvious?

Thanks again (and again!)

Hi,

Glad these set of files could help.

Here's a modified version, with your repeating price.

But you'd better investigate other options, because for sure repeating field won't fit your database evolvement.

See this post

Variable fees

RepsVsRel.zip

Forgive me for not thanking you sooner, I was away on vacation. I will be looking at the variable price file and (hopefully) learn a great deal (of course, being an elementary school teacher, concepts such as quantity break points may take me a while as they're not my usual area of expertise!).

Just wanted to thank you for your kind help! Merci, cher ami!