Basic invoice (master-detail) in Paradox

[M.Emmanuel]

I am trying to do a super basic invoice system in Paradox MSDOS 4.5

I have the following tables:

TABLE CUSTOMER

[CUSTOMER  #] - N*

[VAT] - A20

[NAME] - A20

[ADDRESS] - A50

...

TABLE INV

[INV NUMBER #] - N*

[INV DATE] - D

[CUSTOMER #] - N

TABLE INVLINES

[INV NUMBER #] - A10*

[INV LINE] - N*

[QTY] - N

[DESCRIPTION] - A50

[PRICE] - $

With those three tables, how could I generate a report showing an invoice?

I know hot to link customer to inv (that is simple) and display the customer information.

But what about the invoice lines?

Is this still a tabular report on table INV ?

Thanks in advance for helps and hints,

[M.Emmanuel]

There is a typo, INV NUMBER # is N* in INVLINES:

M.Emmanuel

06:41 (10 minutes ago) 

to TheDBCommunity

I am trying to do a super basic invoice system in Paradox MSDOS 4.5

I have the following tables:

TABLE CUSTOMER

[CUSTOMER  #] - N*

[VAT] - A20

[NAME] - A20

[ADDRESS] - A50

...

TABLE INV

[INV NUMBER #] - N*

[INV DATE] - D

[CUSTOMER #] - N

TABLE INVLINES

[INV NUMBER #] - N*

[INV LINE] - N*

[QTY] - N

[DESCRIPTION] - A50

[PRICE] - $

[Steven Green]

at this point in time, why are you building new functions into a 35 year old, dead platform? at lease try to adapt to PdoxWIN, it might last longer

anyhow, depending on your knowledge, you could do the whole one-to one-to many in one query and filter accordingly in the report, or do the one-to-one and link the to the many in the report.. simple, common data relationship.. try it 

[M.Emmanuel]

I have implemented the following query and used a free form report to generate a invoice document and it works:

Query

 Inv | INV NUMBER #  | INV DATE | CUSTOMER # |
     | Check _INV    | Check    | _CUS       |

 Invlines | INV NUMBER #  | INV LINE |  QTY   | DESCRIPTION | AMOUNT |
          | _INV          | Check    | Check  | Check       | Check  |

 Invlines | CURRENCY |
          | Check    |

 Customer | CUSTOMER # |  VAT   |  NAME  | ADDRESS 1 | ADDRESS 2 |
          | _CUS       | Check  | Check  | Check     | Check     |

 Customer | ADDRESS 3 |  TOWN  | POSTAL CODE | REGION | COUNTRY |
          | Check     | Check  | Check       | Check  | Check   |

Endquery

Do_It!

However the query obviously captures all the pairs INV / INVLINE(s). And we would like to print/generate report for just ONE invoice.

So before the above query I ask for the invoice number.

But how can I translate the INVNO variable value to the above query so I filter further by just that INV NUMBER # while maintaining the relationship?

Clear                                      
@ 1,5 ?? "Specify Invoice Number: "
Accept "N" to INVNO
If RETVAL = False Then
        Return
EndIf

; WHAT IS MISSING HERE TO USE INVNO provided value in above query ??

[Steven Green]

 Inv | INV NUMBER #         | INV DATE | CUSTOMER # |

     | Check _INV, ~invno   | Check    | _CUS       |

[M.Emmanuel]

Thanks, that worked and now I understand it. 

On your previous question: I keep a couple of small databases in Paradox under DOSBox-X, and I enjoy seeing MS-DOS in action again.