Filling a form box with one table data, saving into another table's data.

Hey! so I am writing an Invoice form but I need one thing different from normal: I don’t want past invoices to change when I change item prices. But I do want customer info to change when I change that.

How I am doing this is I have 4 tables. Customer, Stock, Invoice and InvoiceLine.

  • Customer is customer info
  • Stock is all stock info
  • Invoice is for storing Invoice Number, Date, Customer and Total.
  • InvoiceLine is for storing the product codes connected to the Invoice number and their price and other information at the time, quantity, price and line total.

So I want an Invoice form and InvoiceLine sub-form. But when I type an item code in the Code section of the InvoiceLine field I want it to Auto-populate the price and info fields in the InvoiceLine subform

Similarly in the main form I want to be able to type a customer code into the customer code field, and Auto-populate a few other boxes with the customer info, but this will not be saved into the invoice table.

Lastly I want the InvoiceLine Total to equal Product x Quantity and Invoice Total field to automatically add all the InvoiceLine Total fields.

So how do I go about this? Mainly what I can’t seem to find how to do is populate a field with something and save it as something else.

Thanks for the help.

Also, I am aware this is bad Database practice, however I have done this with Paradox Windows for 20 years and it has worked perfectly. (but need to move to a software compatible with modern windows hence Libre). So I would like to stick to how I have done it, like this.

You should start by going over some of the documentation. There are many relevant examples with downloadable samples there. When you have specific problems this is certainly the place to ask. This LibreOffice documentation is located on the TDF post: click here.

This isn’t Paradox (have dealt with it in years past) so things are going to be accomplished differently in LO. Your last statement about “populate a field with something and save it as something else.” I can only relate what you are saying to a listbox where the display is one thing and when selected a corresponding value is stored.

DACM has given an informative tutorial about building an INVOICING application using Base on the Apache OpenOffice Forum - see HERE.

Getting the database design-schema right is very important! For example, your Invoice table might have columns id_in (integer, primary key), id_cu_fk (integer foreign key) linking to your Customer table, and date_inv (invoice date). As you already suggest, you will also need an InvoiceLine (junction) table to store the invoice numbers (id_in_fk) against the stock numbers (id_st_fk). In an application I built, I did all the totalling in a linked REPORT (invoice totals were not stored in the database). Please note: Building a RDBS invoicing application is not a trivial exercise.

This seems very useful! I will give it a look.

You say it is a tutorial but I can only find links to the finished invoices on that forum post? Is there an actual step-by-step tutorial there?

Yes, you’re right - it’s not really a step-by-step tutorial, rather demo .odb files showing some basic techniques. Still, it’s probably worthwhile to try out these demos and see if they are on the right track for what you want. It is likely you will build your understanding by studying the tables & form design of these files.

Right, but if I don’t know where these options are located how can I study the forms?