How to Create Invoices...

[dbuffington]

Hi Folks!

I've been using FileMaker for, well, decades. However, most of my creations have been simple flat databases.

Now, for our small newspaper, I've created a FileMaker 8.5 database with three tables:

- Advertisers (listing advertiser number, name, address, et al)

- Ads (one record for each ad run by an advertiser)

- Payments (one record for each payment made by an advertiser)

Conceptually, what's the best way to combine this data into a end-of-month invoice?

Thanks!

Dave

[Søren Dyhr]

What if you tweak Comments template, into something in the vicinity of this shown in the image above?

Plucked from this template:

http://www.fmforums.com/forum/attachment.php?attid/12516/

The portal can be brought to show the entire sales in the upper, and how much the customer still owes you in the lower portal!

--sd

[comment]

Søren, please don't link directly to attachments - they should be studied in their original context.

Back to topic, I think we need clarifications regarding what is actually being invoiced at the end of the month: is it one invoice per ad or per advertiser, are payments specific to ads or general, what details need to be specified in an invoice, etc.

[Søren Dyhr]

Alright then, but the OP might not even have realized that tagging'ish behaviours might have a bearing here?? It originates from here:

http://www.fmforums.com/forum/showpost.php?post/275746/

is it one invoice per ad or per advertiser, are payments specific to ads or general, what details need to be specified in an invoice, etc.

Correct, and that why I havn't given any suggestions for this yet, byt merely how to distinguise paid from unpaid. What we here have is context, but the purpose is not lacking but only too vaguely described. Yes the overwheming danger is indeed this:

"Algorithms are the foundation of logic - and abstract thinking. Book

algorithms challenge students to think in a way that allows them to

represent real world situations inside the computer. Abstraction is good in

the hands of an educated and knowledgable person. In the hands of an

amateur it can give quick gratification, accompanied by a false sense of

power. If the student lacks the fundamental knowledge of how things link

together in the computer then all programs will simply seem magical, and

thus is born the cut and paste programmer who never understands a thing, but

simplely "messes" with code until it works. Then you get .. . well . . .the

quality of software that is very common today; I call it shitware.

Programming serves a double purpose - of course its innate use is obvious -

getting the computer to do what you want it to. However, learning to

program is an invaluable resource for learning more about the world and

about yourself. But don't misunderstand, I'm not trying to say it is the

only way."

Stumbled over here: http://www.velocityreviews.com/forums/t148991-p2-algorithms-and-pointers.html

--sd

[dbuffington]

I think we need clarifications regarding what is actually being invoiced at the end of the month: is it one invoice per ad or per advertiser, are payments specific to ads or general, what details need to be specified in an invoice, etc.

It is one invoice per _advertiser_.

And...

Each invoice needs to show the advertiser info (number, name, address), the details on the ads (date run, type, size, cost) placed that month plus the details on any payments (date made, amount) that month.

Thanks!

Dave

[Søren Dyhr]

placed that month plus the details on any payments

Still a bit unclear, you are avoiding Comment's question if they are general or specific to a single ad. What happens if two types of adverticements goes on in the month in question, where one is regular which have been going on for months and the other is a special campaign requiring to be shown or on a few times?

--sd

[LaRetta]

...the details on the ads

It seemed clear to me. Since it is sent monthly, it is a Customer Statement which summarizes all client activity for the month. At least that's my interpretation. :smirk:

[Søren Dyhr]

But what if the received payment covers something in the past standing "open" ... what kind of algorithmic approach is supposed to deal with the closure of open issues, oldest first or what ...even if the payment could be disputed?? The tough things to deal with is if payment not exactly fits one specific invoice but could be assumed to cover several older ones...

--sd

[LaRetta]

These are business rules, Soren. I've worked for businesses which apply payments to oldest invoice first and bring the balance forward if not specified; and some apply it only to an account. MOST businesses require that ANY payment be applied to a specific Invoice or the money can't even be received 'on account.'

Usually when you wish for a monthly customer billing, it encompasses many invoices (ads) charged during that month less all payments received during that month - activity is based upon the Post Date (post to GL). The payment line may say which invoice it is paying (or paying against) but it's not that difficult with a balance forward, the month's activities and an ending balance. Accounting will know what to pay, trust me. And incentives for quick pay usually keeps prior balances off the books.

Full Customer Ledgers are usually available in case of disputes but are rarely printed otherwise. Some businesses require all open transactions appear on their statements; others only want current months' activities. It is also not uncommon to have two or three statement types to meet the needs of different Customers. Aging remains the same regardless and AR functions the same no matter how the activities are displayed, disbursed and printed. We just don't know what is needed yet!

[LaRetta]

BTW, I wasn't meaning to step into this thread ... I was just squabbling with your statement that "you are avoiding Comment's question if they are general or specific to a single ad. "

I saw no such avoidance in Dave's response because he clearly said plural ads and specifically said 'during that month' on both ads and payments. :smirk:

[dbuffington]

Hi Folks!

Payments are applied to the account ... that is, against any outstanding balance, not against any specific invoices.

The issue of repeating ads (whether they repeat weekly, monthly or otherwise) is handled manually by creating one new record for each ad. It's really a pretty simple system.

Thanks!

Dave

[Søren Dyhr]

MOST businesses require that ANY payment be applied to a specific Invoice

True, but is enough to jump to conclusions? I would say a statement like this is pretty dissuading:

against any outstanding balance, not against any specific invoices.

Will regular ad's then be stopped, with missing payments? This will require a very short term of given credit, wouldn't it?

handled manually by creating one new record for each ad

...after it have been brought on the pages? But how about nationwide chainstores ... they wouldn't accept such short termed credits. Since their business model often would be based on refinancing the cashflow, instead of direct revenues on put on each piece of merchandise?

Another problem is to memorize the re-issuing of invoices on these long-termed. At least should the relational model reflect this!

--sd

[comment]

I will try and answer your original question first:

There is a technical issue here, how to produce a union report from two child tables. There are a number of possible solutions for this, none of them entirely satisfactory. However, I believe in this case you could simply report from Advertisers, with two sliding portals for ads and payments in the reported period.

For this you will need to add two global fields, gStartDate and gEndDate, to the Advertisers table and define two additional relationships from Advertisers to fresh occurrences of Ads and Payments, filtered by reported period.

The procedure for reporting would be roughly:

• Set gStartDate and gEndDate to the start and end of previous month;

• Find ads within the range;

• Go to Related Record [ from Advertisers, show related only, match found set ]

• Print

However, there are also some additional concerns here that deserve to be mentioned:

As LaRetta stated, this procedure does not really produce invoices. These are merely monthly statements. There will be no permanent record of these statements.

To have real invoices (with Invoice Number and everything), you will need to add a table for them. And either add another table for invoice's line items (where both ads and payments would be imported each month), or find a reliable way to link each invoice to the invoiced items in both child tables. Not a simple task at all.

[dbuffington]

And statements are really what I'm hunting for. (Sorry for the confusion I caused by saying "invoices.")

Thanks!

Dave

[Søren Dyhr]

It would be an interesting challenge though to automate the issuing of invoices this way as well?

--sd