Help with complex invoicing (File attached) Please:)

[Drew Sanderson]

I have attached a sample file to work with in an effort to help with my question.

The problem at hand is consignment / invoicing.

Here is the scenario:

Each item sold is unique and will never be repeated. With this in mind this is a many to one relationship to the invoice.

The selling process involves:

Item shipped to the dealer.

Each item is listed on a “memo” which set up exactly like an invoice.

The dealer has 30 days to sell the item or it must be returned.

Payment is not made on the item unless the dealer sells the item.

Payment is due 30 days from when it is sent out and not from the date they sold it.

The dealer is invoiced when they declare it is has been sold.

Since each item may be sent out multiple times to be sold by different dealers there is a many to many relationship between the items and the memo table.

Workflow:

The workflow that I would like setup is for the user to bring up the client information.

Click on a button that would start a new memo. I imagine this could be done with a global field.

Next the user would need to be able to search through about 40,000 unique items in stock using a combination of about 4 to 5 criteria.

From this list the user would need to be click on a button to add the item to the newly opened memo.

When an item from that memo is sold I would like there to be a button to add that item to a new invoice.

If the item is returned I would like there to be a button to have it returned to inventory.

I need to track the percentage of time the dealer buys (is invoiced for) the items that were memoed to them.

Commissions:

Each item is sold to the dealers with representatives.

I need to track how many memos each representative sends out.

I need to calculate their commission based on items that are invoiced and paid for by the dealer.

I most need help with figuring out how to copy selected items from the find to the memo but I would love answers the rest as well.

I hope my explanation is clear. If not please do not hesitate to ask. Thank you so much for all your help :

Drew

MemoandInvoice.fp7.zip

[Drew Sanderson]

Even if you dont have an answer, any feedback on my post or ideas to get a conversation towards the solution would be great.

Thanks,

Drew

[Søren Dyhr]

I'm getting to it tomorrow I'm at GMT +1, so witnin 8-10 hours from now.

--sd

[Drew Sanderson]

That is awesome!!! Thanks so much. I am looking forward to it You were so helpful before.

[Søren Dyhr]

Since you not overwhelm us with Achor Bouy'ish graphings, is it nessersary for me to draw my own one inside my head. I guess that it's better to listen to (well read) the description of the data, than guessing what's going on in the graph. The issue is that most developers do not copy principles undigested into their solutions, but merely the gist of the principles used.

I have read all the other of your threads and guess that they seems to, by and large to be about the same topic?

One thing that make me wonder, is hey that's an awful lot of invovies, one single for eah item??? It requires the shop who have your stuff in consignation phones you a lot??

Now it isn't a good habit to let the software wrap the business rules to it's likings - it should definately be the other way round, that must even I recognize! However was my thought, why not make the invoice on the diff. That is deducting the returned stuff from the stuff send out. There seems to be a time limit however?

Now with the present description is an invoice identical to one line in the memo, and instead of copying, could it be boolean numberfield that gives an invoice to write to the consignee, it's a multicriteria (two) relation in the opposite direction (a new thread in Anchor Bouy) that make the view from each itemlines a complete invoice.

Alright I threw a few looks at your layout, and wonder why you have chosen to make this gynaechologist or letterbox view of alternative adresses. There is a lot of nice-to-know but hardly need-to-know in them???

But as it is, are there still too many loose ends which prevent's me to go ahead with a suggestion.

--sd

[comment]

with the present description is an invoice identical to one line in the memo

It seems to me an invoice is identical to an item - i.e. if an item is sold, it is also an invoice.

[Drew Sanderson]

Error

Edited September 14, 2006 by Guest

[Drew Sanderson]

Since you not overwhelm us with Achor Bouy'ish graphings, is it nessersary for me to draw my own one inside my head. I guess that it's better to listen to (well read) the description of the data, than guessing what's going on in the graph. The issue is that most developers do not copy principles undigested into their solutions, but merely the gist of the principles used.

I have read all the other of your threads and guess that they seems to, by and large to be about the same topic?

[color:blue]Yes that is true.

One thing that make me wonder, is hey that's an awful lot of invovies, one single for eah item???

[color:blue]If multiple items off of a memo are purchased at the same time then each of those would go on one invoice and not separate. However if the dealer were to call at different times then there would be multiple invoices. This is not my system but the people who I am helping develop this for.

It requires the shop who have your stuff in consignation phones you a lot??

[color:blue]Yes the business is very telephone driven.

Now it isn't a good habit to let the software wrap the business rules to it's likings - it should definately be the other way round, that must even I recognize!

[color:blue]See above

However was my thought, why not make the invoice on the diff. That is deducting the returned stuff from the stuff send out. There seems to be a time limit however?

[color:blue]They want it on different invoices since they print out invoices and file them. They do not want to have to pull out the old one and replace it.

Now with the present description is an invoice identical to one line in the memo,

[color:blue]A line item is needed in the many to many of the memo but not the many to one of the invoice.

and instead of copying, could it be boolean numberfield that gives an invoice to write to the consignee, it's a multicriteria (two) relation in the opposite direction (a new thread in Anchor Bouy) that make the view from each itemlines a complete invoice.

[color:blue]I dont follow that last bit.

Alright I threw a few looks at your layout, and wonder why you have chosen to make this gynaechologist or letterbox view of alternative adresses. There is a lot of nice-to-know but hardly need-to-know in them???

[color:blue]This can easily be removed but some dealers have multiple locations to ship to and instead of repeating fields for each company I made it related.

But as it is, are there still too many loose ends which prevent's me to go ahead with a suggestion.

[color:blue]Hopefully I have tied them up.

--sd

Edited September 14, 2006 by Guest

[Drew Sanderson]

It seems to me an invoice is identical to an item - i.e. if an item is sold, it is also an invoice.

[color:blue]Multiple item can be on one invoice so this is not true. However items from multiple memos will not be on one invoice but rather mutiple ones since they want to be able to referece an invoice to a single memo.

Lets say there are three items on the memo one and three items on memo 2. The dealer calls and says I have sold two items from memo one today and one item from memo two. At that point an invoice with those two items from memo one is generated and a separate invoice for the one item from memo two is generated. He says he will be sending all the item back.

From this I want FM to show me that the dealer on average sells 50% of the it items that are sent to him. I also want to calculate and credit the selling representative with percentage of sale for the three items. I also want to track that the representative had sent out two memos to that dealer.

Hope this adds some clarity.

Edited September 14, 2006 by Guest

[comment]

In view of your clarifications, I don't think this can be done by relationships alone. It will have to be scripted.

Consider a memo with 3 items. The dealer calls to say he has sold item #1. An invoice needs to be generated for this item. A week later, he calls to say he has sold item #2. A NEW invoice needs to be generated.

OTOH, if he calls to say he has sold items #1 and #2, only ONE invoice needs to be generated. But there's no way you can mark two items as sold at once. A difference of a second is the same as a difference of a week. This means we need to mark the two items as sold, and ASK for an invoice to be generated for all items sold, but not yet invoiced.

It also means that the scripting needs to be done VERY carefully to avoid errors. If user marks the wrong item, and an invoice has been generated, it can very easily become a mess. Similarly, user cannot be permitted to mark an item as sold, then NOT generate an invoice.

On the bright side, I believe the structure can be rather simple, at least for the basics. LineItems can be shared between Memos and Invoices. A line item needs to be marked as sold, then all the sold & uninvoiced items of the current memo are isolated in a found set. A new invoice is created, and its InvoiceID is filled in for all those line items.

[Drew Sanderson]

Comment,

Sounds like I must me making sense because you evaluation seems right on.

Any chance you could comment on specific implementation or even work with the attached file to better explain what is happening?

Thanks,

Drew

[comment]

I am afraid the most I can do is a basic sketch of the key relationships and the invoicing script. See the attached - note that I haven't implemented almost any safeguards against user error.

You have quite a project ahead of you - certainly not at novice level. The next problem you will face is how to determine which items are in inventory (i.e. not sold and not out on consignment) and can be included in an outgoing memo. That's not going to be simple either.

Dealing with commissions, etc. might be the easiest part here, but that too is a lot of work. I don't mean to sound discouraging, but I feel you should know what you're getting into.

Let's see if perhaps Søren has a different point of view to offer.

Consign.fp7.zip

[Drew Sanderson]

Thank you!!! That helped me form the ideas of implementation a bit. The implementation challenges you mentioned I can see after looking at the file you sent and I really appreciate it. At this point unless I receive some more good advise to get this moving I think I will get the many of the layouts done in a effort to clearly communicate our goals and then hire a consultant.

Edited September 15, 2006 by Guest

[Inky Phil]

EDIT- I was typing this while Drew was replying so I did not see his last post.

This is not in reply to anyone in particular but is simply my 2 penn'orth.

Drew, I have developed a system for my own business that has an invoicing system not too dissimilar in complexity to this.

It took me about 18 months from scratch (almost complete newb to dbase design) to do the whole system, the invoicing section of which was only a part.

I was successful enough to get the system up and running and then my new found knowledge was enough for me to realise that,because of my early naiivety, I should run what I had and start again.

This I have now done, learning 7 and 8 along the way and I am proud of what I have achieved. Indeed it is intended that the solution will be released for sale to the industry and that brings along a further set of issues.

So, I had the luxury of developing all of this for an industry that I know inside out, for my own business and without the pressure of a time schedule. Without this experience and freedom I would have had some very embarrasing moments along the way... Hell, I had a few of those as it was!

I mention all of this because you say somewhere that you are developing this for someone else and they seem to be a business that runs at a fairly frantic pace.

I only wish to echo comments caveat.

There is nothing here that you cannot achieve given enough time and the help of the good people here in the forum but if you are a complete newb have a good hard think about what you are about to undertake and be ruthlessly realistic both with yourself and with the end user about what to expect

I hope that my advice comes across and is taken in the spirit in which it is intended - that of sharing experience

Good Luck

Phil

Edited September 15, 2006 by Guest

[Drew Sanderson]

Thanks for the words phil... I hear the good spirited tone in your writing. Seems like good advise.

[Søren Dyhr]

I've havn't been present before now, my brother and a partner an opening reception - They've managed to break out of the company they worked in, started their own ...doing exactly the same. The thing took all day ...I'll catch up tomorrow!

--sd

[Søren Dyhr]

Let's see if perhaps Søren has a different point of view to offer.

No not at first sight, and indeed the real struggle here is to deal with errors, where an item first is marked sold but the user discover that it's one from an earlier delivery with a differnt sales price, and have to roll back.

Although something like this could be solved with recursive relation, is the very nature of an invoice as a historic document drawing a line in the time preventing us from it.

I found myself in similar problems when I made this template:

http://www.fmforums.com/forum/showtopic.php?tid/180260/post/221594/hl//

What if the invoicer in my template hit's the limit or a sold out and desided otherwise, plucking an complementary item instead. The scripting became a little clunky in order to put the unused items back again before making list of items for a new lookup. I could perhaps come up with somthing in the vicinity - but I have to think further!

--sd

[Drew Sanderson]

Just wanted to let you all know that I am on the road to success.

The sample file showing the relationship helped to form things properly in my mind.

I figured out how to track staus using the Max function, Case statements, and IsValid or IsEmpty functions.

Thanks for all the help.