Solved

Multiple invoices from single contract - script for manual control

Posted on 2006-11-13
28
366 Views
Last Modified: 2010-04-27
The database for creating multiple records from a single record (multiple invoices from a single contract: Q_21925856.html) has worked well, but the ad manager is now seeking a way to change from a date-controlled script into one that is all manually controlled - meaning, rather than select a date range from which to generate invoices for a month, to be able to generate an invoice from the contracts she sets up manually. i'm wondering if there is a way to combine the two functions without losing the date range functionality so that feature can remain intact. Basically, a two button option that would allow one invoice to be created from a contract and another that would allow for monthly invoices to be created if desired?

The database is the same as before (www.intertopia.org/upload)
0
Comment
Question by:mzurolo
  • 16
  • 12
28 Comments
 
LVL 28

Expert Comment

by:lesouef
Comment Utility
I don't remember exactly how it was, but you can extract from the existing script the part which actually makes the invoice, and create a script with this...
0
 

Author Comment

by:mzurolo
Comment Utility
Sorry, but replace the extracted part of the script with . . . . ?
0
 
LVL 28

Expert Comment

by:lesouef
Comment Utility
copy the make_invoices_for_a_month script, and in this copy which will be used for manual invoicing, just keep the middle of it, ie delete lines 1,3,4,5, then the last 4 lines. you still have to enter the ref date for the invoice to allow checking if it's not done yet, etc... but the question could be replaced by the current date if invoices are always for the current month
0
 

Author Comment

by:mzurolo
Comment Utility
I changed the script and it seems to work, though I am now experiencing a new problem: the invoicing from contracts is producing invoices for all dates when the script is run (either manual or first script). So that while the invoice period may span 6 months, invoices are being generated for all the contract lines as opposed to just the specified date. Could you take a look and see why this happens? Everything has stayed the same since the previous iterations. I went through the script, compared it to earlier versions and all seems correct?? The revised dbase is posted to:

http://www.intertopia.org/upload/

m
0
 
LVL 28

Expert Comment

by:lesouef
Comment Utility
Obviously the script you did is what I said, but this is different from what I have on my PC!
Did I correct it and forgot to tell you??
I have put my script in your file and posted the corrected file:
www.carina-tm.com/upload/Q_22058474_DB.zip
the new script for manual invoices is "make_invoices_manually", and I have attached it to your "manual" button
check this and let me know
0
 

Author Comment

by:mzurolo
Comment Utility
The script works, thank you.

I'm encountering a problem I can't get to the root of: in the "Invoices" layout there is a second line displaying the "price" and "total price" in the portal, but I can't find the cause. It shouldn't be there.  Since it's only pulling in information set in other layouts I can't understand why it appears. I had to change the fields because they now want to add "discounts" and "less paid" information on the contract, rather than the invoice, so that changed how I used the invoice_lines layout.

A need to simplify an element developed as I've been working on it:
Is there an easy way to simplify the invoicing period interface? For instance, there are essentially only six invoicing periods in the year for the magazine - Jan/Feb, Mar/Apr, May/June, July/Aug, Sept/Oct, Nov/Dec. The option to do it manually (one contract, one contract line at a time) is beneficial for the way invoicing works here. But I'd like to provide an easier interface so that the ad mgr. knows exactly what issue she is invoicing for. The current script does what it should, but on the user end I'd like to reduce a little. So on an invoicing period that runs from 1 Jan 2007 to 1 June 2007 (6 months, and thereby 3 invoices), a dialog coudl ask: "Create invoice for this issue" and provide a drop down to choose the specific issue (Jan/Feb, Mar/Apr, May/June) for which to generate an invoice. I want to be able to prevent someone confusing dates, and thereby tighten the controls for invoicing. I added the issue month/year info at the beginning of each contract line for this purpose; so you could see the information that is most often reference when creating an invoice which is the issue information. Am I making sense?

I reposted my additions and changes to your database here:
http://www.intertopia.org/upload/


0
 

Author Comment

by:mzurolo
Comment Utility
On the walk home I was thinking about a way to simplify the invoicing process for the user. Would it be possible to just add a button that would use the portal rows for the contract lines in the contracts layout to pop up a dialog and ask the user which row they'd like to invoice?
0
 
LVL 28

Expert Comment

by:lesouef
Comment Utility
I'll have a look at your file later on.
About the 2nd post, I would say (without checking side effects), that a check box on each contract line would be easier than prompting the user for every line; if you have 10 lines, it's gonan be boring very soon.
0
 
LVL 28

Expert Comment

by:lesouef
Comment Utility
> I'm encountering a problem I can't get to the root of: in the "Invoices" layout there is a second line displaying the "price" and "total price" in the portal, but I can't find the cause
----------
before I have a look to the rest, I can say yr layout pb does not exist, there is a 2nd line invoice for invoice i281 in invoices_lines and all its fields are empty expect the price! to convince yrself, add invoice_lines::invoiceID in the portal line. Apart from that, you placed "rounded_price" twice, just to mention.
0
 
LVL 28

Expert Comment

by:lesouef
Comment Utility
And about the last thing, a few questions:
- why do you set an invoicing freq of 2 months, then create a contratc line for jan/fev then another for march/apr, etc... if you charge every 2 months, 1 contract line is enough since it will be repeated every 2 months, and the invoice_period will show anyway which period is concerned.
In the case of customer C006, who has a 6 months freq. 3 items is enough, 1st, 2nd and 3rd issue of the given 6 months bu you use 6 contract lines...
- of course, I could add a confirmation for each contract_line, but they are all supposed to be charged (in my mind) since I can't see any reason for contract_lines to be linked to a certain period, it is supposed to the items to be charged at every invoice.
If you intend to change this and put in contract_lines all issues to be invoiced during the contract, then we can establish:
- either a contract_line will have a time range and the invoicing date will determine whch contract lines should be picked up automatically.
- or we use a check box on every contract_line, and at every new invoice, the user has to select which lines should be charged regardless of any period indicated; but there any error can escape if the user selects the wrong lines.
A simple confirmation on the invoice period can also be done, but since the date is first asked to the user, what's is the advantage? Anyway, if you'd like to do this, simply replace the question about the date to charge for by a window with a layout where you show the choices.
Once we agree on what you'd like, I'll make the mod's but the checkbox on every contract_line is the easiest.
0
 

Author Comment

by:mzurolo
Comment Utility
- - - - - - - - - - -
And about the last thing, a few questions:
- why do you set an invoicing freq of 2 months, then create a contratc line for jan/fev then another for march/apr, etc... if you charge every 2 months, 1 contract line is enough since it will be repeated every 2 months, and the invoice_period will show anyway which period is concerned.
In the case of customer C006, who has a 6 months freq. 3 items is enough, 1st, 2nd and 3rd issue of the given 6 months bu you use 6 contract lines...
- - - - - - - - - - - -
Customer C006 was a testing entry. But in general the reason that for example, an invoice freq of 2 months is set, but 2 contract lines are created for jan/fev and another for march/april is that the contract must show all issues (max 6) for which a customer would purchase space. So a sample contract might have a customer buying an ad in the Jan/Feb, May/June, Nov/Dec issues in a given year and the contract must provide a line for each of those issues so the customer sees exactly which issues they will be running ads in. Also, discounts might be applied to just one of those issues so the flexibility to edit individual contract lines/issues is necessary. The ability to set one contract line but repeat the invoicing based on date is a great function and sometimes used, but more often than not the flexibility is needed.

In this method, I could imagine the "invoicing freq" number generates that many number of contract lines since there wouldn't ever be more than 6.

- - - - - - - - - - -
- of course, I could add a confirmation for each contract_line, but they are all supposed to be charged (in my mind) since I can't see any reason for contract_lines to be linked to a certain period, it is supposed to the items to be charged at every invoice.
- - - - - - - - - - -

No, you're correct. A confirmation seems redundant. The best solution is what you propose below.

- - - - - - - - - - -
- or we use a check box on every contract_line, and at every new invoice, the user has to select which lines should be charged regardless of any period indicated; but there any error can escape if the user selects the wrong lines.
- - - - - - - - - - -

The checkbox seems the best solution, retaining the invoicing date ranges and frequency, but, if I understand correctly, adding a checkbox which is what determines what contract line will be invoiced for when the "Create Invoice" button is clicked.
0
 
LVL 28

Expert Comment

by:lesouef
Comment Utility
Customer C006 was a testing entry. But in general the reason that for example, an invoice freq of 2 months is set, but 2 contract lines are created for jan/fev and another for march/april is that the contract must show all issues (max 6) for which a customer would purchase space. So a sample contract might have a customer buying an ad in the Jan/Feb, May/June, Nov/Dec issues in a given year and the contract must provide a line for each of those issues so the customer sees exactly which issues they will be running ads in. Also, discounts might be applied to just one of those issues so the flexibility to edit individual contract lines/issues is necessary. The ability to set one contract line but repeat the invoicing based on date is a great function and sometimes used, but more often than not the flexibility is needed.
------------
ding! light is on! contract_lines are not details of contracts but items that may be invoiced upon manual decision
in this case, as the user has to set which lines have to be charged manually, the automatic invoicing feature for a whole month looks useless; that's probably why you removed it from the layout.

The checkbox seems the best solution, retaining the invoicing date ranges and frequency, but, if I understand correctly, adding a checkbox which is what determines what contract line will be invoiced for when the "Create Invoice" button is clicked.
----------
correct.

ok, I'll work on this and be back later on
0
 
LVL 28

Expert Comment

by:lesouef
Comment Utility
ok, the checkbox stuff is done. now it charges only for contract lines that are checked.
now for the date question, this date is still here to check if the current invoice is not done yet but it's not related to the contract lines periods. Shall I remove it?
the current state of the art:
http://www.carina-tm.com/upload/Q_22058474_DB_r3.zip
0
 

Author Comment

by:mzurolo
Comment Utility
Yes! That's exactly the control needed.
For the date question, keeping the date function seems a good idea to me, a safeguard against invoicing the same contract line more than once, no? I think it's also helpful to be able to set and see the invoicing period, frequency and duration of contract.
0
Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

 

Author Comment

by:mzurolo
Comment Utility
Of course, the date dialog when you click "Create invoice" does seem redundant since you've already selected via the checkbox. But again, the dialog that detects whether the invoice has already been created is the key method for preventing error.
0
 
LVL 28

Expert Comment

by:lesouef
Comment Utility
unless we consider the date to be the current date, and don"t ask the user to enter it? does it make sense to always charge for the current period? are there situations where you may have to charge for a period which elapsed already, or in advance?
0
 

Author Comment

by:mzurolo
Comment Utility
The date is important because, yes, there is often a need to charge in advance. Customers often want to pay for an ad in a future fiscal year, etc. Stuff like that. The date function should probably stay, but I think it depends how the user interacts with it that could simplify - if the invoice function always runs just based on the checkbox, but the user gets a dialog if the date of the contract falls outside of the current date, or outside the range specified in invoicing range? Does that sound like it would work easily?
0
 
LVL 28

Expert Comment

by:lesouef
Comment Utility
yes, we can raise the date question only if the current date is outside the bounds of the invoicing period of the contrats.
it could even be done by contract-line if this table would have a date range too, but so far it only has a period which is not a date indication but text.
with is the question if outside the invoicing range:
http://www.carina-tm.com/upload/Q_22058474_DB_r4.zip
0
 

Author Comment

by:mzurolo
Comment Utility
Sorry for the delay in getting back about this. My computer was stolen a couple of days ago just after I had worked on this database. I added a date field in contract lines to better control the invoicing - so the checkbox for charging would be linked to that date. I have the display of the date only as "Month/Year" for space and because that is most often how issues are referred to, but that display won't change how you select a day within that month for the script right? Is the date question dialog then linked to that date and in which script does it attach to? Posted it at the URL below. Many thanks.

http://www.intertopia.org/upload/Q_22058474_DB_r5.fp7.zip
0
 
LVL 28

Expert Comment

by:lesouef
Comment Utility
> but that display won't change how you select a day within that month for the script right?
no, but we need one date per invoice, not per invoice line. so far the date you've been using is global, so its not relevant of a contract_line, but common to all...
if you want to do it this way, use my existing field invoice::invoicing_date instead of yours.

>Is the date question dialog then linked to that date and in which script does it attach to?
yes it is but only to check if the invoice is not done yet, not to select the contract lines according to their period
it is taken in account in the "make_invoices_manually" script.

0
 

Author Comment

by:mzurolo
Comment Utility
Got it.
When I replace the contractlines:date, with the invoice::invoicing_date field all those fields on a contract with multiple contract lines acquire the same date. In other words, I can't specify discreet dates for each contract line.

I understand the date question now. Where do I attach the dialog to alert the use if the date is out of range?
0
 
LVL 28

Expert Comment

by:lesouef
Comment Utility
true, but your was the same. so what? you want a date per contract_line, correct?
then I raise the date question to determine the invoicing period, then I compare it against every contract line which is checked for "charge" and raise a 2nd question if the contract line is out of the invoicing period specified by the contrat invoice frequency + the invoiving date. Do you agree with that before I do it?
0
 
LVL 28

Expert Comment

by:lesouef
Comment Utility
Due to the time shift in between us, you haven't answered yet, but I did what I said in the previous post, almost...
I don't raise any question except if the invoice date to be used is out of the invoicing period.
Then I take all contract lines of which the start and stop period (new fields) are within the contract invoicing period + flag to be charged = yes

http://www.carina-tm.com/upload/Q_22058474_DB_r6.zip

The last thing to debate is what do we do if the contract starts half way a contract line period, like say starts on dec 15th. is a nov/dec line valid in this case?
0
 

Author Comment

by:mzurolo
Comment Utility
Actually the time shift doesn't usually slow me down. Machine was stolen and it's taken a couple of days to get back up and running.

When I invoice in the current version I can check a line and invoice once. If I try and invoice a second line, check the charge box I get a dialog that says, "invoice is already done. delete if you want to do it again."

- - - - - - - - - - - - -
Your first question:
then I raise the date question to determine the invoicing period, then I compare it against every contract line which is checked for "charge" and raise a 2nd question if the contract line is out of the invoicing period specified by the contrat invoice frequency + the invoiving date. Do you agree with that before I do it?

Yes, that makes sense. Comparing the contract line date to the invoicing period makes sense to check range. Then if the contract line date is out of range, a dialog appears to ask if you want to proceed with invoicing even though it is out of the specified range. At least that is how I understand it.

- - - - - - - - - - - - -
The last thing to debate is what do we do if the contract starts half way a contract line period, like say starts on dec 15th. is a nov/dec line valid in this case?

Yes. The reason I made the selection for issue year/month in the contract line display as only month/year is that is always how the contracting period is considered. The contracts are built based on the issue in which they are selling - so always Jan/Fev, or Mar/Apr. The decision on a contract line would be based on month/year only, so even if 15 Dec was chosen as the date it would still be considered the nov/dec issue. That would be clear given the way the date displays (only month/year).
0
 
LVL 28

Expert Comment

by:lesouef
Comment Utility
When I invoice in the current version I can check a line and invoice once. If I try and invoice a second line, check the charge box I get a dialog that says, "invoice is already done. delete if you want to do it again."
------- yes normal, in my mind you do only 1 invoice per contract invoicing period.

Yes, that makes sense. Comparing the contract line date to the invoicing period makes sense to check range. Then if the contract line date is out of range, a dialog appears to ask if you want to proceed with invoicing even though it is out of the specified range. At least that is how I understand it.
-------- as you probably saw, this is something I gave up in my last version. in theory, you never charge a contract line out of the incoiving period. To do it, you're supposed to extend the contract duration, aren't you?

Yes. The reason I made the selection for issue year/month in the contract line display as only month/year is that is always how the contracting period is considered. The contracts are built based on the issue in which they are selling - so always Jan/Fev, or Mar/Apr. The decision on a contract line would be based on month/year only, so even if 15 Dec was chosen as the date it would still be considered the nov/dec issue. That would be clear given the way the date displays (only month/year).
------------
so I have to modify a bit to enable this properly
0
 

Author Comment

by:mzurolo
Comment Utility
When I invoice in the current version I can check a line and invoice once. If I try and invoice a second line, check the charge box I get a dialog that says, "invoice is already done. delete if you want to do it again."
------- yes normal, in my mind you do only 1 invoice per contract invoicing period.
- - - - - - True, but there are cases in which a customer might pay their entire contract at once, which still means generating a separate invoice for each contract line (so if they ran six issues they'd get 6 invoices at once, a little backwards, but that is the system at work presently). So the ability to check each box and invoice, even ones that are out of the current period would be an advantage.

Yes, that makes sense. Comparing the contract line date to the invoicing period makes sense to check range. Then if the contract line date is out of range, a dialog appears to ask if you want to proceed with invoicing even though it is out of the specified range. At least that is how I understand it.
-------- as you probably saw, this is something I gave up in my last version. in theory, you never charge a contract line out of the incoiving period. To do it, you're supposed to extend the contract duration, aren't you?
- - - - - - Technically, yes. But I think the dialog to simply ask if it's okay to go beyond the duration would be the appropriate override. The thing that I didn't understand was, even though I'm still within the same invoicing period - 11/1/2006 to 10/31/2007, shouldn't I still be able to invoice any contract line since they all exist within that invoicing period?

Yes. The reason I made the selection for issue year/month in the contract line display as only month/year is that is always how the contracting period is considered. The contracts are built based on the issue in which they are selling - so always Jan/Fev, or Mar/Apr. The decision on a contract line would be based on month/year only, so even if 15 Dec was chosen as the date it would still be considered the nov/dec issue. That would be clear given the way the date displays (only month/year).
------------
so I have to modify a bit to enable this properly
0
 
LVL 28

Expert Comment

by:lesouef
Comment Utility
- - - - - - True, but there are cases in which a customer might pay their entire contract at once, which still means generating a separate invoice for each contract line (so if they ran six issues they'd get 6 invoices at once, a little backwards, but that is the system at work presently). So the ability to check each box and invoice, even ones that are out of the current period would be an advantage.
--------- that's a pb then... or I must remove the verification of any existing invoice for the same period...

- - - - - -The thing that I didn't understand was, even though I'm still within the same invoicing period - 11/1/2006 to 10/31/2007, shouldn't I still be able to invoice any contract line since they all exist within that invoicing period?
-------- I currently check if there is one invoice done for the contract duration, and from yr previous paragraph, this has to be changed. I thlink I'll simply mark contract_lines which have been charged and forget all other verifications...
0
 
LVL 28

Accepted Solution

by:
lesouef earned 500 total points
Comment Utility
http://www.carina-tm.com/upload/Q_22058474_DB_r7.zip
is the last brand;
- there is now a "invoiced" field in contract lines to avoid to charge twice the same
- the invoicing date is just the date of the invoice if you don't want to use the current date, but checks nothing else
- the contract period still limits the contract lines that can be charged. there the user cannot cheat, contract lines must be within the contract range, at leat 1 single day, ie nov/dev is ok if contract start on the last day of the given period, ie on 12/31th. this is achieved though the link "contract_lines_to_be_charged".
and let me know...
 
0

Featured Post

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

Pop up windows can be a useful feature of any Filemaker database.  Though best used sparingly, they can be employed in a multitude of different ways, for example;  as a splash screen at login, during scripted processes to control user input, as pick…
Problem: You have a hosted FileMaker database and users are tired of having to use Open Remote or Open Recent to access the database. They say, "can't you just give us something to double-click on rather than have to go through those dialogs?" An…
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …
This video explains how to create simple products associated to Magento configurable product and offers fast way of their generation with Store Manager for Magento tool.

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now