"Base" Pull data from table when value or field matches?

[netherworldforest]

I use windows 7 Home Edition

Hello,

First just want to say, thank you for anyone who takes the time to help me with this. I just searching the forums can't figure out this question. I'm think I am not asking the right information. Here it goes.

For some background. I work in retail and we do customer returns. I get the returned product and or damage items I need to call, fax or email each company and make sure what their return terms are. Their 300 something suppliers. The suppliers all have different return terms and how they want you to contact them and what info they want.

I posted a pic of my offline return report. We use MICROSOFT DYNAMICS RMS. Dynamics RMS is a POS. If anyone cares.

So what I am trying to do, Make a Table (1) that has the fields: Supplier, Reorder No, Item Lookup code , Description, Quantity Reason Code, Date ,and Cost.

Then have a Table (2) with Suppliers info: Supplier name, Phone number, Fax , Contact person, more stuff.

The goal would be run a report that "if supplier is present with a return
ie "Avanti" has a return item
Supplier/Reorder No./ Item Code/ Description/ Quantity/ Reason/ Date
Avanti / 702270 / 253214 / Ugly x-mas Sweater/ 1 / damage / 1/19

Then the return report would pull: The return terms, who to contact, Phone number. From table (2)

I hope this makes scents.

[UnklDonald418]

A relatively simple query joining the two tables would fetch the results you need for your report. But for that to work the tables must be properly designed and data entry forms need to be created. Relational database design is not intuitive, there is a definite learning curve

There is a Table Design Wizard with a few predefined templates, but they are rarely helpful, so use Create Table in Design View. Once you have defined all the fields for your table and try to save it you should get a message Should a primary key be created now? answer Yes. Another field will be added to your table named "ID" and it will be designated as the Primary Key. OO Base requires each table to have a Primary Key field, a table without one will be read only, you can't add or edit any data in the table. A Primary Key value is used by the HSQL database engine to uniquely identify a particular record.
To simplify data entry into those tables right click on each of the tables and select Edit, to reopen the table design GUI. Select the "ID" field, then toward the bottom of the window change Auto Value to Yes. Save your changes and your tables will be ready to accept data. When a new record is added to the table a value for "ID" will be automatically generated.

A beginner would see the common fields relating the tables as "Supplier" in Table1 and "Supplier name" in Table2. So, a query something like

Code: Select all

SELECT  "Supplier name",
        "Reorder No",
        "Item Lookup code,
        "Description",
        "Quantity Reason Code",
        "Date",
        "Cost",
        "Return terms",
        "Contact",
        "Phone number"
 FROM "Table1", "Table2"
WHERE "Table1"."Supplier" = "Table2"."Supplier name" 
AND "Table2"."Supplier name" LIKE 'Avanti%'

should deliver the results needed for your report. The connection between the 2 tables is in the Where clause of the query, specifically WHERE "Table1"."Supplier" = "Table2"."Supplier name"
However, when entering a new record in Table1 the user must type the supplier name, but if there is the slightest misspelling the query will fail to deliver the proper results.
The solution to that problem lies in the Primary Key value in Table2. As mentioned earlier that value is unique, each supplier has a different value in the "ID" field that can also be used to identify that supplier. Change the "Supplier" field in Table1 an Integer type and it can store the "ID" value for the matching supplier from Table2. That is what is referred to as a foreign key.
Then a more reliable query would be something like

Code: Select all

SELECT  "Supplier name",
        "Reorder No",
        "Item Lookup code,
        "Description",
        "Quantity Reason Code",
        "Date",
        "Cost",
        "Return terms",
        "Contact",
        "Phone number"
 FROM "Table1", "Table2"
WHERE "Table1"."Supplier" = "Table2"."ID" 
AND "Table2"."Supplier name" LIKE  'Avanti%';

This version the connection is made in WHERE "Table1"."Supplier" = "Table2"."ID"
However, asking the user to remember the "ID" value for all the suppliers isn't realistic.
That is where Base forms enter the picture. A list box control on a Base form allows the user to select a supplier name from a drop down list and store the "ID" value in the "Supplier" field in Table1.

The Base Handbook covers creating forms in the design view
https://documentation.libreoffice.org/e ... tion/base/
But Use Wizard to Create Form can streamline creating the simple forms that you might need for data entry.
There is a video tutorial on adding a list box to and existing form.
https://www.youtube.com/watch?v=FDQYvc3VYVw