using approach as an invoicing tool i have set the form to enter sale in ie, qty,price each,discount vat,etc. i then joined it with another dbf for addresses so first field is drop down listin all account customers.i then made a columnar report which done the various calcs and added a title page . this was then my invoice. then on the "form" i added a macro to "find" all records for whichever customer. and /or date and this then went to that report and then into print preview.(hope its all clear so far)what i cannot do is get this report to sequentialy number ie as in invoice number? i added a field and told it to do this but no joy. heres hoping you can help me my brain is dying with trying. many tnx tom
Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
sequential numbering 2
- Thread starter catnip
- Start date
-
2
- #2
Hi Tom,
in which way have you told your Number field to generate a serial number?
To generate a serial number (customer number, invoice number etc) I usually do as follows:
1.
Generate a field in 'Field Definition' with DataType Numeric and the size you need.
2.
Open the 'Options' 'DefaultValue Tab'.
3.
Mark 'Serial number starting at', type in your start Value.
4.
In 'Incremented by' insert the value 1
Every time you generate a new record, a serial number will automaticly be inserted.
To prevent from duplicate serial numbers I set the field 'unique'. In your case you have to make sure, that there are no duplicate values in the serial number field of your existing records, otherwise you'll get an error.
hope that helps, Lutz
in which way have you told your Number field to generate a serial number?
To generate a serial number (customer number, invoice number etc) I usually do as follows:
1.
Generate a field in 'Field Definition' with DataType Numeric and the size you need.
2.
Open the 'Options' 'DefaultValue Tab'.
3.
Mark 'Serial number starting at', type in your start Value.
4.
In 'Incremented by' insert the value 1
Every time you generate a new record, a serial number will automaticly be inserted.
To prevent from duplicate serial numbers I set the field 'unique'. In your case you have to make sure, that there are no duplicate values in the serial number field of your existing records, otherwise you'll get an error.
hope that helps, Lutz
- Thread starter
- #3
lukni, hi,
many thanks for replying i apreciate that. the problem is that your answer works for "records" that i can do in the manner you state but its on the final report that i need to generate the number. it's not too easy to explain. the report is the result of a find in the macro on the form and is a culmination of a weeks or months purchases and is then printed as an invoice.as this is not realy a record this is where i get the problem.i tried to add a field to the report with the same properties as u suggested but when i activate the macro from the form to go to the report nothing happens , i can't seem to activate this field it just sits there as a dead field. i can't even tab to it to manualy enter a number. hope this helps a bit more .it's not easy without the program in front of you is it many thanks --tom
many thanks for replying i apreciate that. the problem is that your answer works for "records" that i can do in the manner you state but its on the final report that i need to generate the number. it's not too easy to explain. the report is the result of a find in the macro on the form and is a culmination of a weeks or months purchases and is then printed as an invoice.as this is not realy a record this is where i get the problem.i tried to add a field to the report with the same properties as u suggested but when i activate the macro from the form to go to the report nothing happens , i can't seem to activate this field it just sits there as a dead field. i can't even tab to it to manualy enter a number. hope this helps a bit more .it's not easy without the program in front of you is it many thanks --tom
Hi Tom,
I hope, i didn't misunderstood you again, else put my answer to the recycle bin.
I think I had a similar problem whith an article database and an offering tool. I had to choose articles from the article database to generate the offer as a Report.
The offer should have a unique serial number and has to be stored, so that it is possible to recall the original offer, even when the prices of one ore more included articles has changed in the meantime.
Because you are talking about invoices, I think you have the same necessity. If yes, you should do as follows:
Build a new database table InvoiceHeader which contains the field 'InvoiceNumber' (auto filled, unique) and perhaps additional fields like 'Printed' or 'InvoiceTye'. Build two new tables InvoiceArticle and InvoiceAddress which are similar to your original article and address tables, but have the additional field 'InvoiceNumber'.
Join the InvoiceHeader table whith the InvoiceArticle and the InvoiceAddress table via the 'InvoiceArticle' field. Define the relational options 'Insert if no records match the InvoiceHeader record, typing into a blank InvoiceAddress/InvoiceArticle filed inserts a new record.
Join the InvoiceAddress table to your address table (e.g. via a customer number field if existing).
Join the InvoiceArticle table to your article database (e.g. via an article number field). In both cases the relational options should all be set OFF.
Next generate a new form (Main Database InvoiceHeader)which contains the InvoiceHeader fields (InvoiceNumber read only), the fields from the InvoiceAddress table and a repeating panel containing the fields from the InvoiceArticle table.
Create a Macro which starts when the new form is entered and contains the command 'Records - create new record. When you start the panel a new invoice number will be generated.
Define the properties for the join field in the InvoiceAddress (e.g. CustomerNumber) as Drop-down-list. In the Define Drop-Down List chose 'create List automatically from field data' and 'Show drop-down arrow'. In Options 'Field to create list from' choose your address table.
Repeat this action with the join field (e.g. article number) of the InvoiceArticle table, but chose your article table in the 'Field to create list from' dialog.
Build a new SetAdress macro containing Set commands for each field of your address table which should be transferred to the InvoiceAddress record.
Build a new SetArticle macro containing Set commands for each field of your article table which should be transferred to the InvoiceArticle record.
Change the properties of the 'InvoiceAdress' join field (customer number). Define the SetAddress macro to start 'On Tab Out' of the field.
Change the properties of the 'InvoiceArticle' join field (article number). Define the SetArticle macro to start 'On Tab Out' of the field.
If you start your new form the following should happen:
A new invoice number should be generated.
You should be able to select an address from your address table. After you leave the field by clicking into another the data from the selected record should be transferred into the actual InvoiceAddress record.
You schould be able to select one ore more articles in the repeating panel and after leaving the article number field the data from the selected article record should be transferred to the actual InvoiceArticle record.
Now you are able to generate a report, which gets his input from the InvoiceHeader, InvoiceAddress and InvoiceArticle tables.
Lutz
I hope, i didn't misunderstood you again, else put my answer to the recycle bin.
I think I had a similar problem whith an article database and an offering tool. I had to choose articles from the article database to generate the offer as a Report.
The offer should have a unique serial number and has to be stored, so that it is possible to recall the original offer, even when the prices of one ore more included articles has changed in the meantime.
Because you are talking about invoices, I think you have the same necessity. If yes, you should do as follows:
Build a new database table InvoiceHeader which contains the field 'InvoiceNumber' (auto filled, unique) and perhaps additional fields like 'Printed' or 'InvoiceTye'. Build two new tables InvoiceArticle and InvoiceAddress which are similar to your original article and address tables, but have the additional field 'InvoiceNumber'.
Join the InvoiceHeader table whith the InvoiceArticle and the InvoiceAddress table via the 'InvoiceArticle' field. Define the relational options 'Insert if no records match the InvoiceHeader record, typing into a blank InvoiceAddress/InvoiceArticle filed inserts a new record.
Join the InvoiceAddress table to your address table (e.g. via a customer number field if existing).
Join the InvoiceArticle table to your article database (e.g. via an article number field). In both cases the relational options should all be set OFF.
Next generate a new form (Main Database InvoiceHeader)which contains the InvoiceHeader fields (InvoiceNumber read only), the fields from the InvoiceAddress table and a repeating panel containing the fields from the InvoiceArticle table.
Create a Macro which starts when the new form is entered and contains the command 'Records - create new record. When you start the panel a new invoice number will be generated.
Define the properties for the join field in the InvoiceAddress (e.g. CustomerNumber) as Drop-down-list. In the Define Drop-Down List chose 'create List automatically from field data' and 'Show drop-down arrow'. In Options 'Field to create list from' choose your address table.
Repeat this action with the join field (e.g. article number) of the InvoiceArticle table, but chose your article table in the 'Field to create list from' dialog.
Build a new SetAdress macro containing Set commands for each field of your address table which should be transferred to the InvoiceAddress record.
Build a new SetArticle macro containing Set commands for each field of your article table which should be transferred to the InvoiceArticle record.
Change the properties of the 'InvoiceAdress' join field (customer number). Define the SetAddress macro to start 'On Tab Out' of the field.
Change the properties of the 'InvoiceArticle' join field (article number). Define the SetArticle macro to start 'On Tab Out' of the field.
If you start your new form the following should happen:
A new invoice number should be generated.
You should be able to select an address from your address table. After you leave the field by clicking into another the data from the selected record should be transferred into the actual InvoiceAddress record.
You schould be able to select one ore more articles in the repeating panel and after leaving the article number field the data from the selected article record should be transferred to the actual InvoiceArticle record.
Now you are able to generate a report, which gets his input from the InvoiceHeader, InvoiceAddress and InvoiceArticle tables.
Lutz
- Thread starter
- #5
hi again lutz
wow! youve certainly given this some thought. it's going to take me a little while to action but it looks good i'll get back to you soon as. had a look at your prog odp but looks a little too sophisticated for the people who will be using it, switching on pc 9is hard enough . many thanks
tom
wow! youve certainly given this some thought. it's going to take me a little while to action but it looks good i'll get back to you soon as. had a look at your prog odp but looks a little too sophisticated for the people who will be using it, switching on pc 9is hard enough . many thanks
tom
- Status