A whopper of a problem

[BPL5683]

Hello Everybody,

I am downright desperate.

I need some "Pro Bono" help regarding a solution I'm working on for my father's company.

He owns a Manufacturers' Representative firm, and I am having the hardest time building him a working solution

The main problem lies in tracking his rep's commissions, and having the ability to split them up if they share a sale.

I have created a work flow type of interface, but the data doesn't always seem go through the relationships.

Here is a summary:

[Customers]----<[Orders]------<[invoices]>--------[Commissions Recieved]--------<[Commissions Payable]

To walk you through the process

1.) A customer writes an order with us, that customer usually has a default rep by territory.

2.) Orders are sent to the Manufacturer ( this step is not necessary in the database)

a.) At this point it is determined if the commission will be split between two (or more) reps.

b.) Part of the commission is also given to the house, to pay for overhead, and expenses

3.) When the order is received by the Manufacturer, it is fulfilled, and sent to the customer.

4.) Once the order is shipped with an Invoice, we receive a copy of the Invoice (usually) and match the Order with the Invoice (Do I need a One to One link here since one order will only have one matched invoice?)

a.) I should mention we don't always receive an invoice for our order, or an order for an invoice (but we still get commission).

b.) We even receive some invoices scribbled on notebook paper.

5.) The invoices are then compiled by Manufacturer, Where we get a total for the commissions due.

a.) Sometimes they send commissions along with Invoice copies

b.) Other times only partial payments are made on commissions due.

6.) Reps are payed bi-weekly only what is shipped (the invoice total) to the customers, and only on what is received in commissions in those periods.

I have literally been working on this solution for months, and have been racking (possibly frying it) my brain.

Currently they rely on custom software that was given and tested on them back in 1999. So it was developed to work in OS 9.

Which means the newest OS it will run on is 10.4. thanks to OS 9 virtualization support.

Hence the application is very buggy and full of error messages, but somehow it works.

The developer had since abandoned the project and was actually hired on by Apple.

Any advice you'd be willing to share would be HUGELY appreciated.

This is my first database ever, and so far the experience has not been pleasant.

[Zcast]

First, don't get too discouraged using FM just yet, after all, you did say this was your first database, right? Seems like you have a few problems here, not just one big one. If I understood you correctly, the commission doesn't get paid out until the order is actually delivered and paid for? But yet, you determine whether it's split up or not at the time of order?

All your company does is forward the order to the Manufacturer, they fulfill the order, then send you a check for the commission due on that order, (only once they have been paid), but sometimes you get invoices, and sometimes not? OOOKKK? I can see about an order without an invoice, but how can you have an Invoice without an order? Why would the Manufacturer send an invoice if you never placed an order? Just trying to clarify things to give you the best dvice on how to set this up.

[BPL5683]

The commission is not paid out until they receive a check from the vendor for only what was shipped to the customer (in other words the invoice total).

The invoice total is sometimes different from the order total (depending on what the vendor is able to ship to the customer), however the actual commission split and the percentages each rep will get are determined when the order is written. At the point when the percentages are first determined the commission amount is only an estimation. However the percentages of the commission will stay on through to the pay out for the reps.

[David Jondreau]

If you're desperate and been banging your head for months, why not hire someone to help you? For a few hundred bucks, you can save yourself a lot of hassle.

[bruceR]

If you're desperate and been banging your head for months, why not hire someone to help you? For a few hundred bucks, you can save yourself a lot of hassle.

Second that motion. Critical business process and he's asking for "pro bono" help? On what justification? Is he going to come mow my yard for free?

[BPL5683]

Wow, Ok, I know some of you guys are professionals and you do this kinda stuff for money. But, if you aren't really interested in helping somebody out (who doesn't really have the money to spend on it) then why bother even responding or acknowledging my post?

I just figured with so many experts on here there would be plenty of people who have maybe seen this scenario, and would be willing to point me in the right direction. I'm not asking for a fully made, custom tailored solution, but maybe some simple pointers.

Thanks for at least reading through it.

And thanks Zcast for your help, I do appreciate it very much.

[BPL5683]

Well, the way I understand it, every so often a customer will place the order directly with the vendor (if this happens it's almost always a re-order). Yet the vendor, will still give us credit, because we established the customer, and do the leg work.

To be more specific what ends up happening in these situations is the rep will go visit the customer sell them on our various lines, and leave them plenty of literature. Sometimes, the vendor catalogs will include the vendor's order forms, and the customers will send in the orders themselves. Luckily most if not all of our vendors are honest folks and will still credit the Rep with the sale because it was within their territory. Hence in this situation we will only get an invoice copy and not the original order.

Thanks again.

[RodSierra]

We handle commissions this way, simplified somewhat.

In a table named Sales Commission you have:

ContactId for salesman

ContactId for Customer

Percent Commission

This table is related to:

contactId:saleman to Contacts

contactId:Customer to Contacts

ContactId:salesman to DocumentHeaders

ContactId:customer to DocumentHeaders

Via these relationships you can view

all sales reps from a customer record in contacts

All customers for a sales rep in contacts

All sales for a sales rep in contacts from which you can:

generate a credit memo and reference the invoice doc headers and amounts in line items for the CM.

[David Jondreau]

You're the one who asked for "any advice". Bruce and I gave you some.

I'm not saying use a paid consultant for my own personal gain, but for yours.

You have never posted here before, asking for help on simple things. You describe yourself as "Entry Level". You've got a complex problem with highly specific business needs.

You're really asking for help and training beyond the scope of these forums.

Over the past few months, how much time have you put in this? How much time have your workers wasted on this OS 9 database crashing?

Assign a dollar amount to that time. Is it less or more than a few hundred dollars?

Finally, posting a sample file will give people who want to help you more information than trying to describe it in words.

[David McQueen]

Ok... I am working on your project right now. Not that it is the same database or requirements, but it is a client whose son is trying very hard to help out his father's business. So I have some observations.

1. If you get it to work, no one will recognize the time and suffering you have put in because it did not dent their wallet, so they will have absolutely no reservations on asking you to "New and Improve" it with further features.

2. If you don't get it to fully work, you may have it "Half Working" and have some very valuable data already in it.

3. In either case you could end up either hiring for some of the work or being tasked with further expansion of the system.Know now that your immediate goals are temporary.

4. Your father does not know what he knows.The sign that I would be correct is you do something and then he looks at it and says yup... except for this, this and this.And you end up adding it to the solution.Ask questions to the point where he is totaly exasperated with you.

5. Which leads to the observation that you better get the structure right in the most general terms right of the bat. Chances are you or someone else will have to build on what you started. You do not want to have to un-do things.

6. If you do not know what an Anchor and Buoy relationship diagram is. Find out now. It will stop you from building a snake's nest of relationships that some one else or you have to untie. This is really important.I just spent two weeks doing it before I could correct or add anything to the existing system without breaking stuff. This is one of the most important things for further develpment or handing off development.A fully integrated, minimal, entagled relationship diagram is very difficult to deal with because you coming back to it or some one else dealing with it will have great difficulty in changing anything without breaking something else.

7. Your current structure is deficient for true generality. You have invoices, but no line items. there is a similar deficiency at the receivables and commissions level.It may work for your immediate goals, but it will not work once people ask for reporting at a granular level.

8. Again with respect to reporting, people will want to match sales with receipts.So those items where only half of the procedure is done (eg: you receive a cheque from the manufacturer, but have no original order), you will have to build in provision to auto generate these orders and vice versa.You cannot do granular matched reporting without it.Be aware that his congruence iwll hve to take place at both the Invoice/Receipt and their respective line items levels.

9.Looking back at #7, get a comprehension of (1) Entities and Attibutes, (2) Normalization and (3) Entity-Relationships. You appear to have some understanding on #3, but I cannot tell where you are with respect to #'s 1 and 2.Again these go to the structure.

10. If possible, take your time. You can spend it now building it or spend it later tearing it apart...but time will be spent.

11. One poster commented about spending a couple of hundred bucks. My reading is that a couple of hundred bucks goes nowhere on this. In the end the requirement will be an integrated sales managment system.

12. Spend some time looking at interfaces.It can be the best system in the world, but if no one but you can run it, you just bought yourself another job. This is another one of those things that if you do it really well, you will get absolutely no credit because to them it "is obvious and only a dummy would try to do it any other way".

Some of these observations may seem pedantic and some will be useful. consider this as an observational data dump on your behalf.

And of course ... Take free advice for what you paid for it ;-)