mzurolo
asked on
Creating Multiple invoices from a single contract (multiple records from a single record)
I wrote in a while back about a client contract/invoice database. The advice previously was excellent and the database works very well. However, there is now a need for added functionality that is counter to how I'd built the database initially. The database works on a contract/invoice structure where there is a 1-to-1 relationship between contracts and invoices. A contract is generated and there is also an invoice generated. The system worked very well. Now, however, there is a need to be able to insert multiple items in the contract that need separate invoices, so the 1-to-1 relationship no longer works. For example, a contract would include advertisements for Jan, Feb and Mar issues. All three would appear on one contract but individual invoices need to be generated for each month, along with the functionality to be able to generate those invoices in their respective months rather than all at once.
I've tried a number of scripting solutions, but none create the desired effect.
Any advice for a fix?
Many thanks!
m
I've tried a number of scripting solutions, but none create the desired effect.
Any advice for a fix?
Many thanks!
m
ASKER
The database example is here:
http://intertopia.org/upload/
THe start/stop date set up makes sense. The invoice/contract numbers could be revised to incorporate the date within them. Each invoice needs to specify which issue from the contract it is associated with. In the original database this was pretty easy given the one-to-one relationship of contract to invoice, but since customers are invoiced per issue, rather than per contract the need has arisen to incorporate separate invoices on one contract. There could be as many as six invoices on one contract, and seldom more, but I was trying to find an option whereby if a customer wants to add to their existing contract at any point in between the start and stop dates a new invoice could be generated for that add-on.
Many thanks for taking a look at this.
m
http://intertopia.org/upload/
THe start/stop date set up makes sense. The invoice/contract numbers could be revised to incorporate the date within them. Each invoice needs to specify which issue from the contract it is associated with. In the original database this was pretty easy given the one-to-one relationship of contract to invoice, but since customers are invoiced per issue, rather than per contract the need has arisen to incorporate separate invoices on one contract. There could be as many as six invoices on one contract, and seldom more, but I was trying to find an option whereby if a customer wants to add to their existing contract at any point in between the start and stop dates a new invoice could be generated for that add-on.
Many thanks for taking a look at this.
m
I can't put any add-on on this now, as it has a problem: you mixed contracts and invoices.
if you look at the contract table, it's empty apart from its number; how can I create an invoice if the contract is empty?
you need to have contracts which contain the stuff to invoice, and then (basically yr existing invoices)
you will be able to create an invoice upon info contained in the contract.
I could modify your file, but you may not find your way in it afterwards, it is probably better if you do this 1st yourself.
Or I can build an example from scratch maybe? let me know
if you look at the contract table, it's empty apart from its number; how can I create an invoice if the contract is empty?
you need to have contracts which contain the stuff to invoice, and then (basically yr existing invoices)
you will be able to create an invoice upon info contained in the contract.
I could modify your file, but you may not find your way in it afterwards, it is probably better if you do this 1st yourself.
Or I can build an example from scratch maybe? let me know
ASKER
I'm not sure I understand. The contracts table I'm looking at isn't empty. It has 20 fields defined in it, unless that's not what you're referring to. Since I initially wanted a parallel relationship between contracts and invoices I create layouts that contained the same related fields. Sorry if I'm missing the point on this one. Maybe a simple example would help clarify this for me?
m
m
The contracts table certainly has 20 fields, and all records are empty except the contractID.
The confusion comes from the fact that the contracts layout refers to the invoices table, not the contracts one.
and I'll try to build a tiny example tonight for you, I have to go now.
The confusion comes from the fact that the contracts layout refers to the invoices table, not the contracts one.
and I'll try to build a tiny example tonight for you, I have to go now.
http://www.lesouef.net/files/cci.fp7.zip
is the example.
this is very rough, but you should have the right structure to do what you need.
and the invoicing script. it is meant to invoice every month, you must improve it to invoice every 2 months if necessary.
and the price-list should be replaced by yr existing system.
let me know if any question.
is the example.
this is very rough, but you should have the right structure to do what you need.
and the invoicing script. it is meant to invoice every month, you must improve it to invoice every 2 months if necessary.
and the price-list should be replaced by yr existing system.
let me know if any question.
I've done another example for an automatic invoicing every 2 months. let me know if this is of some interest for you
ASKER
I've been studying your example for the monthly invoicing script. Much more sophisticated than my approach would have been. There is a <FunctionMissing> line in both scripts for create contract lines and make invoices for month. Tried to determine the function that is missing, but can't. The add contract lines button doesn't function it seems because of it. Would this be a difference in our Filemaker versions?
I love the automatic invoice generation feature, but i'm wondering if there is a script that could generate an invoice on the fly. For example I could manually search for all the open contracts in a date range then selectively invoice the ones I want? I'd be interested in the bi-monthly example, but I'm also wondering how I can override both altogether if need be. What I was trying to do in the database was on the contract page create a button script that would say "Create Invoice" then ask me for which Issue date I wanted the invoice for, based on the Issue date criteria I have assigned in the Contract. It ended up being a lot of script steps that never got me what I wanted which was to bring some of the contract information into an invoice created for a specific date.
Another question is that the script as is responds to a direct date series - 7/1/06 to 9/1/06. But since the issue contract coverage may actually be more like "January, July and September" and only invoiced in those months wouldn't the current script have to take a date range for those months like 1/1/06 to 9/30/06 and thereby be generate 9 invoices when actually the contract specifies only the need for 3 (1/1/06, 7/1/06 and 9/1/06)?
thanks again.
I love the automatic invoice generation feature, but i'm wondering if there is a script that could generate an invoice on the fly. For example I could manually search for all the open contracts in a date range then selectively invoice the ones I want? I'd be interested in the bi-monthly example, but I'm also wondering how I can override both altogether if need be. What I was trying to do in the database was on the contract page create a button script that would say "Create Invoice" then ask me for which Issue date I wanted the invoice for, based on the Issue date criteria I have assigned in the Contract. It ended up being a lot of script steps that never got me what I wanted which was to bring some of the contract information into an invoice created for a specific date.
Another question is that the script as is responds to a direct date series - 7/1/06 to 9/1/06. But since the issue contract coverage may actually be more like "January, July and September" and only invoiced in those months wouldn't the current script have to take a date range for those months like 1/1/06 to 9/30/06 and thereby be generate 9 invoices when actually the contract specifies only the need for 3 (1/1/06, 7/1/06 and 9/1/06)?
thanks again.
Would this be a difference in our Filemaker versions?
-----------
yes, I am using v8, and the get(scriptparameter) functin to pass arguments to scripts.
I love the automatic invoice generation feature, but i'm wondering if there is a script that could generate an invoice on the fly.
------------------
yes, there is one, it takes the stuff to charge from the contract details.
For example I could manually search for all the open contracts in a date range then selectively invoice the ones I want? I'd be interested in the bi-monthly example,
------------
in between this has become a N-month frequency invoicing system, and every contract can have a different frequency (in months)
but I'm also wondering how I can override both altogether if need be.
------------
not implemented yet, but why not? nothing prevents you from generating an invoice manually even if the user interface is not done for it yet.
What I was trying to do in the database was on the contract page create a button script that would say "Create Invoice" then ask me for which Issue date I wanted the invoice for, based on the Issue date criteria I have assigned in the Contract. It ended up being a lot of script steps that never got me what I wanted which was to bring some of the contract information into an invoice created for a specific date.
--------------
my current system is supposed to be used like this:
at least once a month, you execute the invoicing script. it checks if the invoicing date is in between start/stop dates of contracts, without existing invoices for the given period (just in case you would run it twice), and automatically generates an invoice; contracts with a 3 months invoicing freq for instance are charged the 1st month of the given period only.
Another question is that the script as is responds to a direct date series - 7/1/06 to 9/1/06. But since the issue contract coverage may actually be more like "January, July and September" and only invoiced in those months wouldn't the current script have to take a date range for those months like 1/1/06 to 9/30/06 and thereby be generate 9 invoices when actually the contract specifies only the need for 3 (1/1/06, 7/1/06 and 9/1/06)?
------------
that is taken into account in my last example, at least if I got yr point right. it also refuses start/stop dates which do not match the requested invoicing rythm.
try it and let me know, but it seems ok to me for recursive maintenance contracts invoicing for instance.
get it there:
http://www.lesouef.net/files/cci_Nmonth.fp7.zip
-----------
yes, I am using v8, and the get(scriptparameter) functin to pass arguments to scripts.
I love the automatic invoice generation feature, but i'm wondering if there is a script that could generate an invoice on the fly.
------------------
yes, there is one, it takes the stuff to charge from the contract details.
For example I could manually search for all the open contracts in a date range then selectively invoice the ones I want? I'd be interested in the bi-monthly example,
------------
in between this has become a N-month frequency invoicing system, and every contract can have a different frequency (in months)
but I'm also wondering how I can override both altogether if need be.
------------
not implemented yet, but why not? nothing prevents you from generating an invoice manually even if the user interface is not done for it yet.
What I was trying to do in the database was on the contract page create a button script that would say "Create Invoice" then ask me for which Issue date I wanted the invoice for, based on the Issue date criteria I have assigned in the Contract. It ended up being a lot of script steps that never got me what I wanted which was to bring some of the contract information into an invoice created for a specific date.
--------------
my current system is supposed to be used like this:
at least once a month, you execute the invoicing script. it checks if the invoicing date is in between start/stop dates of contracts, without existing invoices for the given period (just in case you would run it twice), and automatically generates an invoice; contracts with a 3 months invoicing freq for instance are charged the 1st month of the given period only.
Another question is that the script as is responds to a direct date series - 7/1/06 to 9/1/06. But since the issue contract coverage may actually be more like "January, July and September" and only invoiced in those months wouldn't the current script have to take a date range for those months like 1/1/06 to 9/30/06 and thereby be generate 9 invoices when actually the contract specifies only the need for 3 (1/1/06, 7/1/06 and 9/1/06)?
------------
that is taken into account in my last example, at least if I got yr point right. it also refuses start/stop dates which do not match the requested invoicing rythm.
try it and let me know, but it seems ok to me for recursive maintenance contracts invoicing for instance.
get it there:
http://www.lesouef.net/files/cci_Nmonth.fp7.zip
forgot to mention that it won't work with v7 as I use variables a lot.(not supported by v8)
this could be modified to replace all variables by global fields, but there are also some other functions for window positions, etc...
if you'd like to test, it it would be quicker to compile a runtime for me. just let me know if you are pc or mac.
this could be modified to replace all variables by global fields, but there are also some other functions for window positions, etc...
if you'd like to test, it it would be quicker to compile a runtime for me. just let me know if you are pc or mac.
ASKER
Lesouef.
The help you've provided is great. I've been off the site for awhile because we were in the process of acquiring v8 to try and upgrade the dbase you've helped me with. Unfortunately, I'm trying to transfer and combine the various suggestions and scripts that your offered and I'm running into big confusions. I've reposted the databases that I am trying to combine into one here
http://www.intertopia.org/upload/dbase01_dbase02.zip
What I am trying to achieve is split between the two databases and I'm unable to find the best method for combining the functions. I need to retain the features in the dbase_01 (format, color, frequency, ratecard ratios determine pricing, rounding05 function (I don't have FPAdvanced so I need to keep dbase01 to keep that custom function), but bring in the separated contracts and invoices setup as well as the dated invoicing function you showed me in dbase_02.
My problem is creating this system cohesively. Each time I achieve one function it disconnects another. Can you help by providing some advice?
I am on a mac (if that matters)
thank you.
m
The help you've provided is great. I've been off the site for awhile because we were in the process of acquiring v8 to try and upgrade the dbase you've helped me with. Unfortunately, I'm trying to transfer and combine the various suggestions and scripts that your offered and I'm running into big confusions. I've reposted the databases that I am trying to combine into one here
http://www.intertopia.org/upload/dbase01_dbase02.zip
What I am trying to achieve is split between the two databases and I'm unable to find the best method for combining the functions. I need to retain the features in the dbase_01 (format, color, frequency, ratecard ratios determine pricing, rounding05 function (I don't have FPAdvanced so I need to keep dbase01 to keep that custom function), but bring in the separated contracts and invoices setup as well as the dated invoicing function you showed me in dbase_02.
My problem is creating this system cohesively. Each time I achieve one function it disconnects another. Can you help by providing some advice?
I am on a mac (if that matters)
thank you.
m
ASKER
Ignore my last post.
I worked in it some more and figured out my problems, though two smaller ones did arise:
(1) I imported my old pricing functions (color, frequency, ratecard, format) into the new database, but can't figure out why it won't generate a price. The equation to calculate the price based on these attributes is the same as the old database, everything seems correctly relational, the numbers are right, but the price equation won't calculate the price. Help?
(2) the "rounding05" function from the old database which rounded numbers to the nearest 5 or zero was a custom function which I can't seem to import since i'm not in Filemaker Pro Advanced. Any help to replace it in this database so the numbers come out correct?
The edited database is here, and the layouts are the "contracts copy" and "invoices copy" which I've edited to contain the attributes I need.
http://www.intertopia.org/upload/cci_Nmonth Copy_YAM-MZ edit.fp7.zip
thanks.
I worked in it some more and figured out my problems, though two smaller ones did arise:
(1) I imported my old pricing functions (color, frequency, ratecard, format) into the new database, but can't figure out why it won't generate a price. The equation to calculate the price based on these attributes is the same as the old database, everything seems correctly relational, the numbers are right, but the price equation won't calculate the price. Help?
(2) the "rounding05" function from the old database which rounded numbers to the nearest 5 or zero was a custom function which I can't seem to import since i'm not in Filemaker Pro Advanced. Any help to replace it in this database so the numbers come out correct?
The edited database is here, and the layouts are the "contracts copy" and "invoices copy" which I've edited to contain the attributes I need.
http://www.intertopia.org/upload/cci_Nmonth Copy_YAM-MZ edit.fp7.zip
thanks.
ok, I have no time for now, I'll try later on to day or too-morrow.
the custom function rounding05 is easy to replace, I'll put it here strait away:
Case (
Mod (arg;10) < 2.5 or Mod (arg;10) >= 7.5 ; Round (arg;-1);
Mod (arg;10) < 5 ; Round (arg;-1) + 5 ;
Mod (arg;10) < 7.5 ; Round (arg;-1) - 5 ;
0 )
where arg is the value to be processed.
the custom function rounding05 is easy to replace, I'll put it here strait away:
Case (
Mod (arg;10) < 2.5 or Mod (arg;10) >= 7.5 ; Round (arg;-1);
Mod (arg;10) < 5 ; Round (arg;-1) + 5 ;
Mod (arg;10) < 7.5 ; Round (arg;-1) - 5 ;
0 )
where arg is the value to be processed.
ASKER
Thanks for the function. Works.
I've revised the database even more and the questions in the previous post are answered but as always more arise:
In the database version at the below URL I'm not encountering a problem where the "invoice lines" don't correspond with the "contract lines". The information is there, but it is appearing in the wrong fields?
Also, something changed in the invoicing script where now instead of an invoice it creates a new contract.
I added a issue_month and issue_period field so a user could select the issue and also see the date range of it and then set the invoice start/stop period accurately.
Any tips for solving the problems and improving it overall would be appreciated.
thanks.
m
http://www.intertopia.org/upload/database_090106.fp7.zip
I've revised the database even more and the questions in the previous post are answered but as always more arise:
In the database version at the below URL I'm not encountering a problem where the "invoice lines" don't correspond with the "contract lines". The information is there, but it is appearing in the wrong fields?
Also, something changed in the invoicing script where now instead of an invoice it creates a new contract.
I added a issue_month and issue_period field so a user could select the issue and also see the date range of it and then set the invoice start/stop period accurately.
Any tips for solving the problems and improving it overall would be appreciated.
thanks.
m
http://www.intertopia.org/upload/database_090106.fp7.zip
> In the database version at the below URL I'm not encountering a problem where the "invoice lines" don't correspond with the "contract lines". The information is there, but it is appearing in the wrong fields?
---------
you changed the fields in the table, probably been deleting then re-creating or some similar game, therefore the import order is wrong.
edit the script, goto the import line, change the file ref. temporarily to import from the database itself instead of $file (to view the fields match on screen), re-set the fields match for import, then restore $file as the file source and save the script change. let me know if you can't do this, I'll do it for you.
> Also, something changed in the invoicing script where now instead of an invoice it creates a new contract.
----------
you deleted a layout (contracts) which was used in it, therefore there is a "new record" command which is not executed in the right layout, thefore in the wrong table. remember that if you delete a layout used in a script, it is deleted from the script; but re-creating it won't correct the script back again! so restore the contracts layout where necessary
The thing still looks weird to me, but I did not take enough time to understand yr new contract contents: why does it change across the year, both for items and duration? Check the previous 2 pbs and let me know what's left, but obvisouly your new contract type won't work with my stuff which is meant to use simpler parameters.
---------
you changed the fields in the table, probably been deleting then re-creating or some similar game, therefore the import order is wrong.
edit the script, goto the import line, change the file ref. temporarily to import from the database itself instead of $file (to view the fields match on screen), re-set the fields match for import, then restore $file as the file source and save the script change. let me know if you can't do this, I'll do it for you.
> Also, something changed in the invoicing script where now instead of an invoice it creates a new contract.
----------
you deleted a layout (contracts) which was used in it, therefore there is a "new record" command which is not executed in the right layout, thefore in the wrong table. remember that if you delete a layout used in a script, it is deleted from the script; but re-creating it won't correct the script back again! so restore the contracts layout where necessary
The thing still looks weird to me, but I did not take enough time to understand yr new contract contents: why does it change across the year, both for items and duration? Check the previous 2 pbs and let me know what's left, but obvisouly your new contract type won't work with my stuff which is meant to use simpler parameters.
ASKER
> you deleted a layout (contracts) which was used in it, therefore there is a "new record" command which is not executed in the right layout, thefore in the wrong table. remember that if you delete a layout used in a script, it is deleted from the script; but re-creating it won't correct the script back again! so restore the contracts layout where necessary
Makes sense. Fixed the script and the invoicing appears to be functioning correctly again.
> you changed the fields in the table, probably been deleting then re-creating or some similar game, therefore the import order is wrong.
edit the script, goto the import line, change the file ref. temporarily to import from the database itself instead of $file (to view the fields match on screen), re-set the fields match for import, then restore $file as the file source and save the script change. let me know if you can't do this, I'll do it for you.
Have tried to fix this script and think I understand the problem, but despite editing the script as you indicate i'm still not getting the result I expect (fields correctly matching up). Can you provide the xample?
> The thing still looks weird to me, but I did not take enough time to understand yr new contract contents: why does it change across the year, both for items and duration? Check the previous 2 pbs and let me know what's left, but obvisouly your new contract type won't work with my stuff which is meant to use simpler parameters.
What I changed was to have the totals for invoices calculated by the original calculations in the database from a few months ago - where the price is detemined by variables (format, rate, color, frequency) against a reference price. That part of the translation which I did seems to be working fine.
My understanding of your example was that you created a price list which pulled in the value for a given item, right?
Is there a different or advantage to one method or the other?
The appearance of other dates/months in the contract/invoice lines, are merely referential for the contract. They aren't driving any data, but are serving as information that can be selected to identify the specific issue/date the person is buying for.
Posted my fixes to:
http://www.intertopia.org/upload/YAM_AD_database_090306.fp7.zip
Makes sense. Fixed the script and the invoicing appears to be functioning correctly again.
> you changed the fields in the table, probably been deleting then re-creating or some similar game, therefore the import order is wrong.
edit the script, goto the import line, change the file ref. temporarily to import from the database itself instead of $file (to view the fields match on screen), re-set the fields match for import, then restore $file as the file source and save the script change. let me know if you can't do this, I'll do it for you.
Have tried to fix this script and think I understand the problem, but despite editing the script as you indicate i'm still not getting the result I expect (fields correctly matching up). Can you provide the xample?
> The thing still looks weird to me, but I did not take enough time to understand yr new contract contents: why does it change across the year, both for items and duration? Check the previous 2 pbs and let me know what's left, but obvisouly your new contract type won't work with my stuff which is meant to use simpler parameters.
What I changed was to have the totals for invoices calculated by the original calculations in the database from a few months ago - where the price is detemined by variables (format, rate, color, frequency) against a reference price. That part of the translation which I did seems to be working fine.
My understanding of your example was that you created a price list which pulled in the value for a given item, right?
Is there a different or advantage to one method or the other?
The appearance of other dates/months in the contract/invoice lines, are merely referential for the contract. They aren't driving any data, but are serving as information that can be selected to identify the specific issue/date the person is buying for.
Posted my fixes to:
http://www.intertopia.org/upload/YAM_AD_database_090306.fp7.zip
ASKER
Was able to fix the script so that both problems seem now to be working correctly.
The contract and invoice lines now correspond, having edited the script at the import line and resetting the fields match for import.
Restored it to $file and all seems good.
However, I am now encountering a problem with creating invoices from a contract.
I'm able to create an invoice for the first item in the contract line, but can't for any subsequent lines in the contract.
But when I look at the invoices page, there are BOTH items from the contract lines where there should be only one for the specified period.
For example, I have 2 contracts for a period from 01/01/06 (jan) to 06/30/06 (june).
There is a contract for January and a second for May.
I can invoice for January an invoice appears below on the contracts page and an invoice is created.
When I try and invoice for May, it doesn't do anything, not even create a blank contract as before when the script was incorrect.
I know it must be something in the script that I've missed, but I can't find it.
Advice?
many many thanks.
The most up to date fix is at the same URL as the previous post:
http://www.intertopia.org/upload/YAM_AD_database_090306.fp7.zip
The contract and invoice lines now correspond, having edited the script at the import line and resetting the fields match for import.
Restored it to $file and all seems good.
However, I am now encountering a problem with creating invoices from a contract.
I'm able to create an invoice for the first item in the contract line, but can't for any subsequent lines in the contract.
But when I look at the invoices page, there are BOTH items from the contract lines where there should be only one for the specified period.
For example, I have 2 contracts for a period from 01/01/06 (jan) to 06/30/06 (june).
There is a contract for January and a second for May.
I can invoice for January an invoice appears below on the contracts page and an invoice is created.
When I try and invoice for May, it doesn't do anything, not even create a blank contract as before when the script was incorrect.
I know it must be something in the script that I've missed, but I can't find it.
Advice?
many many thanks.
The most up to date fix is at the same URL as the previous post:
http://www.intertopia.org/upload/YAM_AD_database_090306.fp7.zip
ok, I'll check this.
the remaining pb is pretty normal since the start and stop dates used to be in contracts and are now in contracts lines.
the whole date trigger thing has to be modified for this, but its getting late, I'll see that 2morrow if you don't mind!
the remaining pb is pretty normal since the start and stop dates used to be in contracts and are now in contracts lines.
the whole date trigger thing has to be modified for this, but its getting late, I'll see that 2morrow if you don't mind!
ASKER
Actually, the start and stop dates should still be in contracts.
The contract lines date don't (shouldn't) affect anything.
The contract needs to retain the global start/stop date.
The contract lines dates are purely to indicate the specifics of issue and issue date that are being contracted.
Since one contract is created for a range of issues/dates, the main contract start/stop needs to work as it is in your example.
The contract lines dates are there to indicate on the invoice specifically which issue the invoice covers.
Not sure I'm being clear, but the start/stop dates as is work correctly.
The contract lines date don't (shouldn't) affect anything.
The contract needs to retain the global start/stop date.
The contract lines dates are purely to indicate the specifics of issue and issue date that are being contracted.
Since one contract is created for a range of issues/dates, the main contract start/stop needs to work as it is in your example.
The contract lines dates are there to indicate on the invoice specifically which issue the invoice covers.
Not sure I'm being clear, but the start/stop dates as is work correctly.
I got it I think. need more time to correct, but basically, yr price is based on the 1st contract_line; so if the contract has 3 lines, they will be charges 3 times the price of the first. I'll finish tonight hopefully
http://www.lesouef.net/files/YAM_AD_database_090306sl.zip
is yr stuff modified to get the invoices lines to reflect correctly the contract lines.
Also, I removed a few fields in the contracts layout / invoices portal where you were using the wrong fields: the invoices portal should display invoices fields, not invoices_lines fields. If you click on the invoice, you can now see the invoices lines (to be improved by you for the appearance, but that was not the goal)
And let me know what's next!
is yr stuff modified to get the invoices lines to reflect correctly the contract lines.
Also, I removed a few fields in the contracts layout / invoices portal where you were using the wrong fields: the invoices portal should display invoices fields, not invoices_lines fields. If you click on the invoice, you can now see the invoices lines (to be improved by you for the appearance, but that was not the goal)
And let me know what's next!
ASKER
Yes, got it.
The problem I'm having, though, is that the contracts and invoices are synching up correctly.
If you look at the contract in the database you posted, there are two contract lines for different issues and costing different amounts. However, the invoices that are created (i269 and i270), which appear below the contract lines, reflect two separate invoices, but for the same amount each.
Shouldn't there be a separate invoice for each contract line, rather than an invoice that totals all the contract lines?
In the database posted, ct001 has two contract lines which should produce one invoice for Jan/Feb for $21,600 and a second invoice for May/June for $3015, shouldn't it? I thought that was how your first database example worked - each contract line was able to generate its own unique invoice.
That's the functionality I've been trying to bring back with the script, but can't get there.
The problem I'm having, though, is that the contracts and invoices are synching up correctly.
If you look at the contract in the database you posted, there are two contract lines for different issues and costing different amounts. However, the invoices that are created (i269 and i270), which appear below the contract lines, reflect two separate invoices, but for the same amount each.
Shouldn't there be a separate invoice for each contract line, rather than an invoice that totals all the contract lines?
In the database posted, ct001 has two contract lines which should produce one invoice for Jan/Feb for $21,600 and a second invoice for May/June for $3015, shouldn't it? I thought that was how your first database example worked - each contract line was able to generate its own unique invoice.
That's the functionality I've been trying to bring back with the script, but can't get there.
> Shouldn't there be a separate invoice for each contract line, rather than an invoice that totals all the contract lines?
that's your decision, both can be done. which one makes more sense in yr business?
I can modify to get 2 invoices, in our case, that will be 4 invoices then, correct?
that's your decision, both can be done. which one makes more sense in yr business?
I can modify to get 2 invoices, in our case, that will be 4 invoices then, correct?
btw, if you need 1 invoice per contract line, there is no need for invoices_lines as every invoice will have only 1 line.
so are you sure that 1 invoice will always match 1 contract line?
so are you sure that 1 invoice will always match 1 contract line?
also, I checked my example, and all contract lines were on the same invoice.
You may have been confused by the fact that it had several contracts for different customers invoiced at the same time.
You may have been confused by the fact that it had several contracts for different customers invoiced at the same time.
ASKER
Yes, must have been that now that I look at it again.
So if it is invoice per contract line, I can delete invoices_lines altogether?
So if it is invoice per contract line, I can delete invoices_lines altogether?
I now have something which generates 1 invoice per contract line, but I kept the invoices lines table too, just in case you would still need to charge 2 items within the same invoice in the future.
let me know if OK, and I'll post the file
let me know if OK, and I'll post the file
our posts got crossed...
see my previous post first.
invoices_lines table can be removed, but its fields must be created in invoices first. which I have not done yet for the reason mentionned before
see my previous post first.
invoices_lines table can be removed, but its fields must be created in invoices first. which I have not done yet for the reason mentionned before
ASKER
Yes, that sounds perfect!
thanks.
thanks.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Question about the file you posted:
In the Contracts view, after I make an invoice for a contract line, six invoices appeared below in the "Invoices" view each with a unique invoice ID.
I had two contract lines one with a date of 1/1/2006 and the other 5/1/2006.
I made an invoice for 1/1/2006, but got invoices for both dates as well as a third one which was a $0.00 total for each of the dates 1/1/2006 and 5/1/2006.
I'm going through the versions of the make invoice script in the database, but don't see a line that would be causing this. Advice?
In the Contracts view, after I make an invoice for a contract line, six invoices appeared below in the "Invoices" view each with a unique invoice ID.
I had two contract lines one with a date of 1/1/2006 and the other 5/1/2006.
I made an invoice for 1/1/2006, but got invoices for both dates as well as a third one which was a $0.00 total for each of the dates 1/1/2006 and 5/1/2006.
I'm going through the versions of the make invoice script in the database, but don't see a line that would be causing this. Advice?
When I do the same, I only get 2 invoices (as expected).
Delete all invoices and do it again to test, there may be a record left over from a previous test??
Delete all invoices and do it again to test, there may be a record left over from a previous test??
ASKER
That worked, I only got one invoice for the one contract line in there.
But it didn't provide a total cost in the invoice (it does appear in the contract line).
I suspect is has something to do with the file reference as I get an error dialog that says "The file 'YAM_AD_database_090306sl.
I click "OK" then navigate to the file (even though I'm IN the file its asking for) and it completes the process, though in this case, not providing a cost on the invoice
Advice?
thanks.
But it didn't provide a total cost in the invoice (it does appear in the contract line).
I suspect is has something to do with the file reference as I get an error dialog that says "The file 'YAM_AD_database_090306sl.
I click "OK" then navigate to the file (even though I'm IN the file its asking for) and it completes the process, though in this case, not providing a cost on the invoice
Advice?
thanks.
I don't have a clue.
I have re-posted my file with the invoices already made to show you that the sum is done correctly
please re-download it and counter-check.
and the only place where the file is needed, is the import command in my script, but it uses a variable, so it should work even if you rename it
I have re-posted my file with the invoices already made to show you that the sum is done correctly
please re-download it and counter-check.
and the only place where the file is needed, is the import command in my script, but it uses a variable, so it should work even if you rename it
the main idea is to enter a start and stop date for the contract, then have an invoicing script which loops though all contracts, and if the current date is in between start and stop contract dates, create one.
do you need an example, or would it be better to do it on yr existing file to avoid you to re-create the stuff in yr file afterwards?