ERD help - Order Entry system to track commissions

[jeanine]

Hello,

 

I'm a FileMaker newbie, attempting to build a database to track invoices & payments for a food broker. I can find plenty of information on straight invoicing, but here's my issue - because this is a brokerage, I have both prinicipals (the suppliers) and customers - we are the middle men. (We work with roughly a dozen principals and over a hundred customers) The principals invoice the customers directly, and we are paid a commission (usually monthly but this varies from principal to principal). What I am trying to create is a database where we can generate POs  (pulling from both a Principals and a Customers table), pull from Items table (description, cost, price, etc), and track to confirm that we are paid the correct commission (again, rates vary by principal) at the end of the month. So, an order entry system basically. I do not need to track inventory at all. I am stuck on even getting an ERD on paper, what I'm imagining looks something like: Customers -<PO>-Principals and PO-<LineItems>-Products. Does this seem right to you FileMaker gurus out there?

 

I first attempted modifying the Invoices Starter Solution in FM12 but can't seem to make that work.

 

Complicating matters is the fact that the cost of items changes almost weekly and customers are invoiced by the cost at the time they placed their order, however if I read the UserGuide correctly I should be able to achieve this by using a Lookup, correct?

 

I'm also trying to decide whether to have a seperate Contacts table with info and join that to both Principals and Customers, rather than duplicate a lot of fields such as Phone, City, etc.

 

Any help walking through this is much appreciated. Also my searches of this and other forums for ERD models for similar, broker-type situations has come up empty. If you're aware of any resources I'd very much appreciate a link or nudge in the right direction!

 

Many thanks,

 

Jeanine

 

 

[comment]

 I see something like this in your description:

 

 

which is not unlike what you suggested yourself.

 

 

the cost of items changes almost weekly and customers are invoiced by the cost at the time they placed their order, however if I read the UserGuide correctly I should be able to achieve this by using a Lookup, correct?

 

Correct. The missing part is how do you intend to update the prices as you go.

Edited September 30, 2013 by comment

[jeanine]

Thank you very much - this sort of visualization is exactly what my left-brain thinking needed.

I assumed pricing would have to be updated in the Products table as new pricelists come in - an ongoing data entry task. Can you please confirm - if I use a "Lookup" to relate pricing in LineItems table to Purchase Orders, that would keep whatever price is there when the PO is generated rather than updating as new prices are entered - is that correct?

Thanks again very much for you help with what I know was a VERY elementary question.

[comment]

if I use a "Lookup" to relate pricing in LineItems table to Purchase Orders, that would keep whatever price is there when the PO is generated rather than updating as new prices are entered - is that correct?

Ahm... if you meant "if I populate the price field in LineItems by looking up the current price from Products, that would keep whatever price is there when the PO is generated rather than updating as new prices are entered" then yes, that would be correct. The looked up price will update only if you modify the line item's ProductID or perform a relookup.

 

I assumed pricing would have to be updated in the Products table as new pricelists come in - an ongoing data entry task.

Well, hopefully you could get your suppliers to use some agreed upon SKUs. That would enable you to import their price lists, while updating existing records. Otherwise ouch.

Looking again at the ERD: I added the Payments as an afterthought, but depending upon your workflow vis-à-vis the suppliers it may require a bit more work. A simple method of comparing the sum of payments a supplier has made against the sum of commissions on that supplier's POs (which in turn depends on the sum of extended prices of the related line items) may turn out to be too slow as the number of orders grows.