Special pricing for certain parts for certain customers. Brain exploding.

[OG School]

Hello,

I've been using FMP since 1999, although in a very limited way (single table databases only). I've enjoyed just jumping in and trying to make things work, and have been fine with the fact that my solutions are not elegant, but they work.

I'm up against it now, though, as I've been tasked with the following situation:

A business has about 400 products

• Many can come in a variety of colors.

• some items can be sold as a unit or sold by the foot or both

• each product can be manufactured by up to 4 different suppliers

• they pay each supplier a different amount, and therefore 1 part number can cost them 4 different amounts

• they give their customers individualized prices; God forbid it should be something easy like a percentage off a general price. No, each customer gets a special price on each individual part number that they are apt to buy. This may be 4 or 5 parts out of the 400 available, but each regular customer gets a special price on at least a few items

• they need to keep track of how much each unit (or each foot) costs them, compare it with the customer price, and show what the profit would be on that item. They can then change the supplier to see the difference in profit (and what they have in stock)

• they need make purchase orders and release forms and tie it into an inventory

• the inventory can be in up to 5 places (their own warehouse and the other 4 suppliers warehouses)

I feel like I have a significant amount of this solved, even though this is my first attempt at a relational database. The custom pricing for each customer is killing me, though.

I've set the DB up like this (I am designing it in FMP 9 on a Mac):

Purchase Orders table - most of the interfacing takes place here

Customer table - related to invoice table by customer ID

Inventory table - related to invoice table by a combination of part number, supplier ID and inventory location

Products sold table - related to Inventory table by part number. This table is a list of all the products sold along with general public pricing by unit & by foot. To handle color variants, I gave each one a separate part number (101-blk, 101-blu, 101-r, 101-y, etc).

Supplier table - related to Inventory table by supplier ID

I've attached a pic of the relationships.

I have it so that when the user creates a new invoice record, they select the customer via a value list. All pertinent info is put into the invoice. The user selects the 1st part number ("item 1 part number), and the description of the item appears. When they put a quantity into the units field, item 1 price should appear. If they put a quantity into the per foot field, item 1 foot price appears. Out of printing range to the right, the user can choose a different supplier to check their inventory levels and profit per items.

So far, no problem. If you saw it, you may cringe, but the DB is working. Here's where I crash and burn. I don't know how to say something like:

"if item 1 part number is 101-blk, then make item 1 price whatever we indicate this customer can have on part 101. We're not going to type in special prices for color variants, so whether the part they want is 101-blk, 101-blu, 101-r or 101-y, make item 1 price the customized price. Oh, and if we DON'T have a special price for this customer on this particular item number, make item 1 price whatever the general pricing is from the Products Sold table."

If he just gave his customers 3% off of everything, this would be easy, but having special prices for different part numbers for different customers has me stumped.

Please excuse the rambling. I don't post many places, and I'm never quite sure how much or what to write.

Thank you for any help, advice, or clarifying questions.

-Dave

[comment]

First thing, before even considering your pricing issue: replace the numbered fields in your Purchase Orders table with a LineItems table. See an example here:

http://attic.fmforums.com/forum/showpost.php?post/309136/

BTW, you are using "invoices" and "purchase orders" as if they were the same thing.

[OG School]

you are using "invoices" and "purchase orders" as if they were the same thing.

Yeah, sorry about that. The client has a big "PURCHASE ORDER" across his invoices. Dunno why, but I wound up calling them the same thing. I've fixed that, so I now have these tables:

Invoices

Customers

Suppliers

Products

Inventory

LineItems

before even considering your pricing issue: replace the numbered fields in your Purchase Orders table with a LineItems table. See an example here:

http://attic.fmforums.com/forum/showpost.php?post/309136/

I totally get you...this is infinitely better. One problem, though, is that I don't see how the example DB you clued me into works with regards to creating a new line item. In the demo, the user can select a ProductID (which is a pull down menu from the LineItems table), and once a productID is selected, a new record is created in the LineItem table. Back in the invoice table, the user is able to select another ProductID in the next line of the portal. Again, only when the ProductID is selected does the LineItems table create another record. When I try this in my own DB, I can never select a ProductID for the second or third line of the portal. Can you tell me how it is that the demo allows us to select a productID from the LineItems portal BEFORE the record even exits?

In any event, my temporary workaround is to have a script that creates a new invoice, creates a new InvoiceNumber based on a calculation that the client wants (a combination of date and some other data), then creates 12 new records in the LineItems table, inserting the InvoiceNumber value into each one of them. I picked 12 because I can squeeze 12 line items into the invoice layout. The demo is cooler since there are only as many line items as you need.

So now I need to contemplate getting the custom pricing calculation to work (and a lot of other things that broke by getting rid of the numbered fields to make way for LineItems.

Any thoughts on the original question?

"if item 1 part number is 101-blk, then make item 1 price whatever we indicate this customer can have on part 101. We're not going to type in special prices for color variants, so whether the part they want is 101-blk, 101-blu, 101-r or 101-y, make item 1 price the customized price. Oh, and if we DON'T have a special price for this customer on this particular item number, make item 1 price whatever the general pricing is from the Products Sold table."

Thank you!!!

-Dave

[comment]

Can you tell me how it is that the demo allows us to select a productID from the LineItems portal BEFORE the record even exits?

Look at how the relationship is defined with regard to allowing creation.

a new InvoiceNumber based on a calculation that the client wants (a combination of date and some other data)

Not a good idea. The client can have any number they want - but you should not use it as a matchfield for relationships.

Any thoughts on the original question?

I hesitate here because frankly, you are struggling with basic issues - and this one is not. And it's further complicated by a bug in Filemaker.

Anyway, have a look at the attached.

if item 1 part number is 101-blk

If an item part number is "101-blk", then you have a problem. The part number should be "101". The color should be "blk". And you should very likely have two tables, one for the products and one for product variations.

CustomerPrices.zip

[OG School]

I appreciate your help. My gig finds me doing everything from setting up sound systems to searching email archives and everything in between. This generally means that my skill set is really wide, but really narrow, too. It's clear that I am trying to make a leap into waters that are too deep. Let me learn to swim a little, study the basics, and I will reply at that time.

BTW, are you open to consultation/one on one training? I am getting our school to rely on FMP more and more, and I will have funds available. Obviously this project is not school related, but I took it on thinking that if I could get THIS to work, I should be able to handle pretty much any other task we'd need at the school.

Thanks,

Dave

[Vaughan]

And it's further complicated by a bug in Filemaker.

Can you elaborate on that, Comment.

[comment]

It's explained in the file. See also:

http://fmforums.com/forum/index.php?showtopic=46893&view=findpost&p=218845

http://fmforums.com/forum/topic/40445-lookups-in-portal-no-related-value-prior-to-commiting-record/page__p__189375#entry189375

[Vaughan]

OK thanks.