locked
Proper practice developing a invoicing form. RRS feed

  • Question

  • Hi all;

    I am about to develop a fairly large invoicing form. I was wondering what is the proper way of storing the information relating to the invoice form.

    I was thinking of creating a header cursor and a detail cursor and storing the entered invoice info in these cursors. Is it a good idea, or are there better ideas out there?

     

    Thanks.

    Friday, May 13, 2011 2:24 AM

Answers

  • You've got two totally different reactions to your idea. Your thought is not totally wrong, but you're diving into details before making much more important general decisions, which is not leading you to your goal, you're lost in details fast.

    First of all you can't use a cursor to store info persistant, a cursor is temporary data. What is right is, that you use cursors or aliases to load data from the persistant database, eg from dbf tables. That could also be a remote database. Internally foxpro works best with cursors, that's true. 

    Tom is pointing you to the start of a process to create any form: You don't start with the form, you first decide in an abstract way about the data storage, eg what do you want to store and what tables you need for it. So you first decide about the database, then about a visualisation through a form.

    What wikipeadia has to say about this is, there are two completely opposite ways of doing database modelling, bottom-up or top-down. You can choose what suits you better:

    http://en.wikipedia.org/wiki/Data_modeling#Modeling_methodologies

    Bottom-up or top-down are both ideas you can follow, you can also bring this idea over to the process of creating a form. You can actually start with a form design to see what informations are entered, then you are doing your database design from that detail informations you order into groups of data belonging together in records of tables. That's the bottom-up design and it's ok, but if you start from scratch you better start with the top-down approach of first thinking in very general portions of your application and data.

    As a sample you would tink about the entity of invoices, which is the most general representation, the next level of details is that you have some head data and some detail data in each invoice. Then there is more sub structure, eg an adress on the invoice header may not store all the details of a postal adress, but simply refer to an adress table, which in itself is just a header for a structre of tables with the details of state, city etc.

    You go from general to details and on the way refine your design. You can think about form design in parallel, but you can also decide that later.

    The next natural step rather is to define functionality you want, eg adding an invoice, updating it, deleting it, sending it to a customer, setting it as paid, what ever you want and need to do with the invoices. And what you need to do this functionality are not visual forms in the first place, but code. Code reading an invoice into memory to work on it, no matter if that's variables or cursors, cursors are of course a natural, as you typically use SQL to query your tables and that results in cursors.

    All the functionality can be put into a class like custom, which is no visual thing at all, it just has the methods what to do with the invoice data.

    On top of that you build your visual representation, one or several forms that use the invoice class methods.

    If you do it that way you can also later decide to change to another database or to another frontend user interface, eg build a web application on top of the functionality. This is the typical three tier design, no matter how you do it in detail it has served programmers well, to seperate data from logic/functionality and seperate both of them from a visual representation.

    Bye, Olaf. 



    • Proposed as answer by Mike Feng Monday, May 16, 2011 10:36 AM
    • Edited by Olaf Doschke Monday, May 16, 2011 10:17 PM
    • Marked as answer by Mike Feng Saturday, May 21, 2011 10:41 AM
    Saturday, May 14, 2011 11:06 AM
  • Well, yes. Data binding is best done towards a cursor. But preferably one created by a view (local/remote) or better and more modern a cursoradapter.

    Controls can also bind to variables or form properties technically, but that would mean to query from the database into cursor and on top of that copy the cursor data into variables. Why would anybody do that? For controlling what data goes back to tables or not and when it should be saved, you have buffering.

    Bye, Olaf. 

    Edit: In contrast to the two types of cursors mentioned (views and cursoradapters) there are additional ways of creating cursors:

    1. sqlpassthrough (sqlexec)

    2. direct sql language, eg select into cursor

    3. create cursor

    those are ways not having a backlink to a table and therefore are not preferable to do data access two-ways, that means reading from tables, writing back to tables. sqlexec cursors can be turned into updatable cursors by a set of cursorsetprop() calls defining remote table, keyfields and more. It's much easier to get an updatable cursoradapter cursor or view cursor.

    No matter how cursors are created, they are the main working horses of querying data and binding controls to them, they are the layer between dbfs or other tables (also remote data) and foxpro controls, especially for the very useful grid.



    • Marked as answer by Mike Feng Saturday, May 21, 2011 10:41 AM
    Thursday, May 19, 2011 8:37 PM

All replies

  • What I have always done is store the header info in variables and the detail in a detail cursor. When it comes time to actually writting the info to your tables, I would copy the detail cursor to a detail file and write all the variables to a header file. A header cursor would be redundant as when would you update it? Just my 2 cents.

     


    Mike z
    • Proposed as answer by Mike Feng Monday, May 16, 2011 10:36 AM
    Friday, May 13, 2011 2:38 AM
  • Hi,

    it is a good idea, to firstly think about the information and data that is needed for the invoice.

    So you will have to take a closer look, which informations are already stored in your database and which additional infos are needed for the invoice.

    With that knowledge, you can normalize your new data (including reference ids to the already existing data and master data) and create a master and one or more detail tables.

    Keeping your invoice data in cursors is the way to go with VFP. And Michaels way to keep master data in variables is an option, too.

    So, your choice might depend on the way your other apps (and maybe framework) usually work with data.

     


    Gruss / Best regards -Tom 010101100100011001010000011110000101001001101111011000110110101101110011
    • Proposed as answer by Mike Feng Monday, May 16, 2011 10:36 AM
    Friday, May 13, 2011 5:52 AM
    Answerer
  • You've got two totally different reactions to your idea. Your thought is not totally wrong, but you're diving into details before making much more important general decisions, which is not leading you to your goal, you're lost in details fast.

    First of all you can't use a cursor to store info persistant, a cursor is temporary data. What is right is, that you use cursors or aliases to load data from the persistant database, eg from dbf tables. That could also be a remote database. Internally foxpro works best with cursors, that's true. 

    Tom is pointing you to the start of a process to create any form: You don't start with the form, you first decide in an abstract way about the data storage, eg what do you want to store and what tables you need for it. So you first decide about the database, then about a visualisation through a form.

    What wikipeadia has to say about this is, there are two completely opposite ways of doing database modelling, bottom-up or top-down. You can choose what suits you better:

    http://en.wikipedia.org/wiki/Data_modeling#Modeling_methodologies

    Bottom-up or top-down are both ideas you can follow, you can also bring this idea over to the process of creating a form. You can actually start with a form design to see what informations are entered, then you are doing your database design from that detail informations you order into groups of data belonging together in records of tables. That's the bottom-up design and it's ok, but if you start from scratch you better start with the top-down approach of first thinking in very general portions of your application and data.

    As a sample you would tink about the entity of invoices, which is the most general representation, the next level of details is that you have some head data and some detail data in each invoice. Then there is more sub structure, eg an adress on the invoice header may not store all the details of a postal adress, but simply refer to an adress table, which in itself is just a header for a structre of tables with the details of state, city etc.

    You go from general to details and on the way refine your design. You can think about form design in parallel, but you can also decide that later.

    The next natural step rather is to define functionality you want, eg adding an invoice, updating it, deleting it, sending it to a customer, setting it as paid, what ever you want and need to do with the invoices. And what you need to do this functionality are not visual forms in the first place, but code. Code reading an invoice into memory to work on it, no matter if that's variables or cursors, cursors are of course a natural, as you typically use SQL to query your tables and that results in cursors.

    All the functionality can be put into a class like custom, which is no visual thing at all, it just has the methods what to do with the invoice data.

    On top of that you build your visual representation, one or several forms that use the invoice class methods.

    If you do it that way you can also later decide to change to another database or to another frontend user interface, eg build a web application on top of the functionality. This is the typical three tier design, no matter how you do it in detail it has served programmers well, to seperate data from logic/functionality and seperate both of them from a visual representation.

    Bye, Olaf. 



    • Proposed as answer by Mike Feng Monday, May 16, 2011 10:36 AM
    • Edited by Olaf Doschke Monday, May 16, 2011 10:17 PM
    • Marked as answer by Mike Feng Saturday, May 21, 2011 10:41 AM
    Saturday, May 14, 2011 11:06 AM
  • Hello, in addition to te other responses : I would think of saving the invoice-printout in a pdf. We are using SQL-Server and store the pdf file content in a Varchar(max) for later review and printout. Maybe you can save the printout in a pdf file and just store the path/filename, but thats not so safe (or much more work with background processes) If you like this idea : Have a look on free bullzip pdf which allows you to create a pdf without userinteraction. If you use VFP9, have a look on foxite.com Cesars foxypreview which does the same without installing a printer. HTH Best regards tom
    Monday, May 16, 2011 8:03 AM
  • Olaf;

    Thank you so much for spending the time to write such a detailed answer. The problem is that unfortunately I think I didn't make myself clear.

    I was actually referring to the data entry control sources. In other words, just as an example, if I have a textbox on the form which stores the customer's name, what should be the control source for that? where should this info be stored: In a cursor, or should I use form properties? Or maybe there is a different idea alltogether?

    I think the answer is probably a cursor but I just want to make sure I'm not missing something I don't know about.

    Thanks.

    Tuesday, May 17, 2011 12:18 AM
  • Thanks. Although I do use a pdf creator (XFRX) I prefer not to save the pdf created but rather be able to regenerate the invoice at any time.

     

    Tuesday, May 17, 2011 12:19 AM
  • I thought a header cursor makes much more sense because it is easier to create and populate with the header data. I can create it simply by doing something like this:

    Select * from customers where .f. into cursor curBillHdr readwrite

    later I can do a scatter/gather to pupulate it with the customer data.

     

    I don't need to update the header cursor. But I can use it to update my invoice info much easier than form properties.

     

    Thanks.

     

    Tuesday, May 17, 2011 12:24 AM
  • Tom;

     

    Thanks, but I think I didn't explain myself right. See my answer to Olaf.

     

    Tuesday, May 17, 2011 12:25 AM
  • Well, yes. Data binding is best done towards a cursor. But preferably one created by a view (local/remote) or better and more modern a cursoradapter.

    Controls can also bind to variables or form properties technically, but that would mean to query from the database into cursor and on top of that copy the cursor data into variables. Why would anybody do that? For controlling what data goes back to tables or not and when it should be saved, you have buffering.

    Bye, Olaf. 

    Edit: In contrast to the two types of cursors mentioned (views and cursoradapters) there are additional ways of creating cursors:

    1. sqlpassthrough (sqlexec)

    2. direct sql language, eg select into cursor

    3. create cursor

    those are ways not having a backlink to a table and therefore are not preferable to do data access two-ways, that means reading from tables, writing back to tables. sqlexec cursors can be turned into updatable cursors by a set of cursorsetprop() calls defining remote table, keyfields and more. It's much easier to get an updatable cursoradapter cursor or view cursor.

    No matter how cursors are created, they are the main working horses of querying data and binding controls to them, they are the layer between dbfs or other tables (also remote data) and foxpro controls, especially for the very useful grid.



    • Marked as answer by Mike Feng Saturday, May 21, 2011 10:41 AM
    Thursday, May 19, 2011 8:37 PM