creating invoices for billing periods

[sarahsmile]

I am trying to set up a system for my company's freelancers. I have a "Freelancers", "Projects" and "Invoice" table. What I want is automatically create an invoice on the 15th and last day of every month for all the freelancers who have done projects. Obviously only the projects done during that time period should in the invoice. Can anyone advise me on the best way to go about doing this? I'm a little lost (and pretty sleep deprived) so any help would be greatly appreciated. Thanks!

[eos]

Hi Sarah,

sleep deprivation is a b…!; hopefully you're not on a deadline.

Some more info would be helpful: how many people (freelancers) can be involved in one project, and what goes into an invoice? Do you store individual activities, like "Person A, 7 hrs on 06/01/2012 for Project B, rate: $25", or "Person C, 5 hrs on 06/02/2012 for Project D, rate: $20"?

[sarahsmile]

Thanks for responding! Sleep deprivation is pretty bad and of course I'm on a deadline haha.

To answer your question - only one freelancer per project. The projects are technically articles and they get paid per article. As of now, the rate for each article is $25. The article is marked as finished when they upload it into a container in the article table. A script then sets the date and time it was uploaded. That make sense?

[eos]

So the project itself *is* its own single activity. This sure makes life (and programming) easy.

I assume you'd like to have only one invoice per freelancer per 14-day period, but with all articles from that period.

Paperback Writer, 2 articles from 2012/06/01 - 2012/06/14, <list of articles>, total $50, something like this?

See question in my list below.

I'm a bit sleep-deprived myself, so here's quick hodge-podge of ideas:

• Have a startscript every day (or every database start) look at the date; if it's the 14th or the last of the month, check if there are new Projects to invoice.

“New” and “invoicable“ means they a ) have an upload date, and b ) no InvoiceID.

• Need a ) a summary invoice for all projects from one writer delivered in a given period, or b ) an invoice for every single project?

• Must go to Invoices and generate an Invoice record with an InvoiceID (plus the usual stuff like creation date etc.). Must make note of new InvoiceID.

• Must go back to Projects and write the new InvoiceID into a new InvoiceID_kf field in the Projects records, so each invoice knows its projects (and the projects “know” they have a related invoice.) When each invoice knows it's projects, we can use them on the invoice form, probably in a portal (should fit, since the time period is small).

• Print invoices. At this point, all existing Projects have been printed, and new ones haven't arrived yet. Time for a break!

Clear so far?

[sarahsmile]

You are amazing! The only thing I am having issue with (and this could something obvious I'm not realizing) is creating a new invoice for each freelancer. Also, I would only need one invoice per freelancer with all the projects in that time period.

I guess I could have the script search for each freelancer's name and a project without an invoice id, but it seems like there should be a better way, especially since I would have to edit the script every time we hired a new freelancer.

Either way, you have helped me get past a huge hurdle of just how to set it up, so thank you! I am now going to get some sleep and hopefully everything will make even more sense tomorrow!

[eos]

You are amazing! The only thing I am having issue with (and this could something obvious I'm not realizing) is creating a new invoice for each freelancer. Also, I would only need one invoice per freelancer with all the projects in that time period.

I guess I could have the script search for each freelancer's name and a project without an invoice id, but it seems like there should be a better way, especially since I would have to edit the script every time we hired a new freelancer.

Either way, you have helped me get past a huge hurdle of just how to set it up, so thank you! I am now going to get some sleep and hopefully everything will make even more sense tomorrow!

Hi Sarah,

you don't need to alter the script for new hires when searching for “new” projects to invoice; here's why:

When you want to invoice projects, you're searching for finished projects of the given time period, as per the uploadDate field, which is a ) not empty (project delivered) and b ) matches the time period in question (automatically calculated), and the InvoiceID field, which must be empty. As you can see, the FreelancerID doesn't appear as a search criterion.

As for the generation of invoices: I don't know how savvy you are with scripting. Let me know, or post a sanitized version of your DB, then I can help you set up a script.

[sarahsmile]

Hey,

I'm decent with scripting, but if you could steer me in the right direction, I'd be forever grateful. Also, how would I calculate the time period? I'm pretty good with calculations, but when it comes to calculation of dates, I have to admit, I get a little confused.

The only part I'm still really stuck on is making a new invoice for each freelancer each period.

[eos]

Calculating with date and time has its peculiarities & pitfalls, but this one's pretty straightforward, because you can add and subtract whole days from a given date, like in the second calculation, where we don't have to calculate the previous month (and maybe year).

If the script runs on either the 1st or the 15th of a month, you can calculate the date range you need to search for in the date field like this:

If [ Day ( Get ( CurrentDate ) <> 1 or Day ( Get ( CurrentDate ) <> 15 ]

Show Custom Dialog [ "Not a billing day!" ]

Exit Script [ ]

End If

Go to Layout [ Projects ]

Set Error Capture [On]

Set Variable [ $searchRange ; Let ( isToday = Get ( CurrentDate ) ; Case ( Day ( isToday ) = 1 ; Date ( month ( isToday ) - 1 ; 15 ; Year ( isToday ) - 1 ) & "..." & isToday - 1 ) ; Day ( isToday ) = 15 ; isToday - 14 & "..." & isToday - 1 ) ]

Enter Find Mode [ ]

Set Field [ uploadDate ; $searchRange ]

// Set Field [ InvoiceID_kf ; "=" ) ; not sure if is necessary

Perform Find [ ]

If [ Get ( FoundCount ) = 0 ]

// handle error (Show All Records, return to original layout etc.)

Else

// Generate Invoices (we'll be coming to that part soon)

End If

Using Set Error Capture is always a prudent choice if you don't know beforehand if you will find matching records (or maybe you do know, but better be safe and all that; also prepare for other users than yourself).

[comment]

Watch out for cases where the 1st or the 15th of the month is not a work day. It might be better to see how long has it been since the last billing.

[eos]

Watch out for cases where the 1st or the 15th of the month is not a work day. It might be better to see how long has it been since the last billing.

Spot-on, as usual. Need to decide on business rule for shifting search/billing dates vs fixed invoice dates (as printed).