Directions on setting up a form with some sub forms/tables...

[Tyler1]

I am looking for a good video/website on setting up forms sub forms and tables.

What I am looking to do is setup a form that depends on 3 different tables;

• orderform,
• items,
• orderform_x_items.

So the orderform table will have basic information on the order, while the item table has basic item information.

I am trying to setup the form so that I can add multiple items and amounts and store the data in the orderform_x_item table.

Ideally I would like to be able to have the form automatically calculate the price based on the amount by multiplying amount from the orderform_x_item table by the unit_cost field in the item table.

From the form I would like to see a list, like a table, of each item, amount, unit cost, and total.

All the examples I have found show the relationship as a series of pages for each relate; I am trying to do a list.
Anyone know where I can get information on doing the above?

Thanks;
Tyler

[EasyTrieve]

I put some line breaks in your question to make it easier for me to read. I guess what I’m trying to figure out is at what level of understanding you are. I think it might be good at this point to create a more formal table description for your project, including the exact fields in each table, with names, and types, and a description of what each field is and how it’s or will be used. This description is independent of which data base tools you will be using. Please update your question.

[Ratslinger]

Hello,

For videos, this seems to be the site most refer to → TheFrugalComputerGuy.

You question does present some issues. First thought is a missing table - Customer, the main character. A customer can have multiple orders and an order can have multiple items. The Order is linked to the customer & the order items are linked to the order. Your 'Items" table (depending upon size and complexity) is simply used for items in a list box in the ordered items section.

Calculations (or totals) are typically not part of capturing data. This can be done later with SQL. If absolutely necessary and instant results are required, special macro(s) need to be written for this purpose. If only needed once the data has been completed, then SQL can work. Also certain databases (Firebird for one) has computed fields available. The field data will be a result of a calculation from other data. Again, the result is only available once the initial data is entered and is meant for a specific line item and not a total of all lines in the order.

So for you example you would have selection of a customer, creation of an order record, then entry of items on the order :

MainForm - Customer
    Subform - Order (linked to Customer)
        SubSubform - OrderItems (linked to Order)

Edit 2018-10-30:

@Tyler1 Hopefully this may help you understand what was presented above. Attached is a sample loosely based upon your provided information. This sample does not use macros. The sample contains two forms. The Customer form contains a selection for a customer. After selecting from the listbox, select Select Customer button. This lists all orders for that customer in a table grid. By selecting any order in that grid, the items for the order is displayed in the table grid below it. You can add new orders for a customer and new items or even modify existing orders. You can see much of how the tables are linked by viewing the internal form properties.

With form Customer1, you can view any order with the calculated results of each line item and the order total. This is done through SQL queries.

Again as stated above, if you are absolutely requiring instant calculated results (data on Customer1 available on Customer) as you enter data, you need to use specialized macro code for your application. A sample of this type can be found in my answer here → Base Form data uneditable. Please realize macros are not something you grasp in a week or two. It takes time.

Sample ------- OrderForm.odb

[Tyler1]

Sorry, I didn’t add the customer table because I got that working rather easily. What I am having trouble is compiling the list. I figured the unit prices for items would be stored in the items table and the linking table would have the amounts (number of items not total cost) between those 2 values the total cost of each item line and the total of all item lines together could be calculated and displayed in the form somewhere. In my mind I was picturing a table like subform.

[Tyler1]

Ideally I am trying to build a section where I click add, make a selection from a list, enter a number of items and have it appear as a list item in a table structure on the main form.

[Ratslinger]

@Tyler1 In addition to the edit in my answer, you can find more generalities in the LO Base documentation → LibreOffice Base Handbook. See Chapter 4 - Forms, section on Main forms and subforms.