Take new input and insert into new table

Creating and using forms
Post Reply
mouseandcat
Posts: 7
Joined: Sun Jul 03, 2016 4:41 am

Take new input and insert into new table

Post by mouseandcat »

Hi, I'm curious to know if anyone can help me out with a problem. I can't seem to find any analogs to help me.

I have a OOBase table (TABLE1) of particular US addresses:
Address, City, State, Zip
- Suppose "State" values are unique
- I have a form set up using a "filter" table so that it will display certain addresses given the "State" that a user types in.

Now, I want a user to be able to create a record in a new table (TABLE2): existing address + new name:
Address, City, State, Zip, Name

I want a a user to be able to enter a name, find the address based on State and then take all that information and insert it into a new table. Ideally the user can select which of the tables to insert the new record into.

Perhaps someone has an example or analog that I can deconstruct? I just really am at an impasse. I am certainly a user of different flavors of SQL databases but never created one.

Thanks!
WIN 7 X64
OpenOffice 4.1.2 AOO412m3
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Take new input and insert into new table

Post by Villeroy »

Wrong database design. Learn the basics.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
UnklDonald418
Volunteer
Posts: 1544
Joined: Wed Jun 24, 2015 12:56 am
Location: Colorado, USA

Re: Take new input and insert into new table

Post by UnklDonald418 »

It appears you don't really understand database design. Here is a link to a tutorial that teaches the design process
https://wiki.documentfoundation.org/ima ... torial.pdf
Database design isn't for those seeking instant gratification. The tutorial won't give you a direct solution to your problem, but as you work your way through the tutorial think about how your data fits into the process. If you apply what is being taught the solution to your problem should be obvious.
If your problem has been solved, please edit this topic's initial post and add "[Solved]" to the beginning of the subject line
Apache OpenOffice 4.1.14 & LibreOffice 7.6.2.1 (x86_64) - Windows 10 Professional- Windows 11
mouseandcat
Posts: 7
Joined: Sun Jul 03, 2016 4:41 am

Re: Take new input and insert into new table

Post by mouseandcat »

unkl, vill, thanks for the tips.

i thought Base would be appropriate for the end-user I am thinking about (small non-profit for which i volunteer) because it does not require setting up a *sql database to maintain separately. and it comes in a user-friendly form (without my having to design it all myself). but perhaps Base may not be as flexible as i am thinking? i haven't created anything in Base/MS Acccess prior to this weekend and thought it was a combination of ux+sql in a self-contained package.

i thought i could combine Base forms/interface + programmatic aspects of sql to create a customized solution. i understand what i am asking, which may not be comprehensible, may not have a good or even possible solution. spreadsheets aren't ideal b/c the table of addresses is rather long (100K records). i can't make the time to do this in full stack python so i turned to Base.

but if what both of you are saying is there is no analog available without reading full-on tutorials, i will have to continue seeking answers. i still feel that Base is going to be the most straightforward solution.
WIN 7 X64
OpenOffice 4.1.2 AOO412m3
UnklDonald418
Volunteer
Posts: 1544
Joined: Wed Jun 24, 2015 12:56 am
Location: Colorado, USA

Re: Take new input and insert into new table

Post by UnklDonald418 »

Yes,
Base is going to be the most straightforward solution
Base is a very flexible and powerful tool. But like any powerful tool you need to learn to use it before striking out on your own. No, you won't lose a finger or hand, but you can end up with a bunch of nightmarish tables, so I guess you could lose your head.
Base isn't going to provide instant gratification, there aren't many shortcuts. Actually the tutorial is a rare one ( there is a semester or two of college courses packed into that tutorial). That knowledge will allow you to design easier to maintain tables, as well as logical queries and forms. In the long run you will save frustration and thus time. In the beginning you might be able to skim/skip over the chapters concerned with Reports.
table (TABLE1) of particular US addresses:
Address, City, State, Zip
sounds like a reasonable table, but not so much
a record in a new table (TABLE2): existing address + new name:
Address, City, State, Zip, Name
It isn't clear what you really need in TABLE2 but it shouldn't duplicate the data that is already in TABLE1. It should contain a link to a Key Field in TABLE1 eliminating the duplication and the possibility of conflicting data. A properly designed Query on a properly designed Form can display all that information. Properly designed Forms will also simplify data entry.
Part of the problem is that it isn't clear what you are really trying to accomplish
I want a a user to be able to enter a name, find the address based on State and then take all that information and insert it into a new table. Ideally the user can select which of the tables to insert the new record into.
Whenever someone says
take all that information and insert it into a new table.
it raises a red flag. That sort of thing often results in a difficult to maintain database with conflicting data in different tables.
For specific help you might consider uploading a sample of what you currently have along with a better description of what you are really trying to achieve.
If your problem has been solved, please edit this topic's initial post and add "[Solved]" to the beginning of the subject line
Apache OpenOffice 4.1.14 & LibreOffice 7.6.2.1 (x86_64) - Windows 10 Professional- Windows 11
mouseandcat
Posts: 7
Joined: Sun Jul 03, 2016 4:41 am

Re: Take new input and insert into new table

Post by mouseandcat »

unkl, thanks for the response. and apologies for the lack of clarity, i should have been more explicit.

let me take a third stab at this: i want to take existing information in a table and join that with data entered by a user; take all that information and insert that new record into a new table (or view; whichever will allow Base to export the table)

suppose i have an existing "reference" table ("Table Pop") with three columns: rank, city, population (see below):
Image


suppose i went out and collected data not in the existing table ("new data"): name, city (see below):
Image


given the new data i collected, i want to create a new table ("full table") that will join data from the existing table with "new data", such that:
Image

i will periodically go out and collect new data (i.e. name and city) and need maintain the identity of each "full table." easiest way i can see would be to create separate tables, each with a deliberate set of records. another approach could be to append a "master" table with a identifier column. i prefer separate tables.

i think the huge advantage of Base is the forms and a simple way to create a GUI and manage the workflow, which minimizes the probability of error. i suppose in my mind it's creating a table of new data then creating another new table left joining existing table. but it needs to be as easy as possible for a naive user to interact with.

of course there are many contingencies i want to prepare for but i can get to that once i get over this crucial hump, such as:
- record that does not already exist in Table Pop and ability to add that new record: rank, city, and population into Table Pop while the record is added to the "full table"
- control over "full table" name, how many new records go into each "full table"
- export "full table" as a particular file type (e.g. csv, tab-delimited; for scipy/r)

TLDR; export a new table created from existing table joined with new data entered someone.
WIN 7 X64
OpenOffice 4.1.2 AOO412m3
UnklDonald418
Volunteer
Posts: 1544
Joined: Wed Jun 24, 2015 12:56 am
Location: Colorado, USA

Re: Take new input and insert into new table

Post by UnklDonald418 »

I've been struggling with what to say about your last post. I'm not sure how far we'll get but I will ask some questions so that I can better understand what you are really trying to accomplish.
i want to create a new table ("full table") that will join data from the existing table with "new data"
I'm assuming the existing table refers to “Table Pop”.
For each entry in “Table Pop” is there only one entry in "new data" or can there be more than one?
Likewise for each entry in "new data" is there only one entry in “Table Pop” or can there be more than one?
Can there be an entry in "new data" but no matching entry in “Table Pop”?
I think I know the answer to this but I will ask it anyway. Can there be entries in “Table Pop” but no matching entry in "new data"?
When you enter data into "new data" will you find the matching entry(s) in “Table Pop” at the same time?
need maintain the identity of each "full table."
Indicates that there will be multiple instances of "full table". If that is true then what really constitutes a "full table" and how do you know when to switch from one "full table" to another?
How many instances of "full table" do you expect to need?
Do you need to save copies of "full table" for archival purposes, or will you want to delete them at some point in time?
Will some entries in one instance of "full table" be duplicated in other instances of "full table"?

There will be more questions but for now that will do.
If your problem has been solved, please edit this topic's initial post and add "[Solved]" to the beginning of the subject line
Apache OpenOffice 4.1.14 & LibreOffice 7.6.2.1 (x86_64) - Windows 10 Professional- Windows 11
mouseandcat
Posts: 7
Joined: Sun Jul 03, 2016 4:41 am

Re: Take new input and insert into new table

Post by mouseandcat »

I'm assuming the existing table refers to “Table Pop”.
yes
For each entry in “Table Pop” is there only one entry in "new data" or can there be more than one?
one to many relationship between "Table Pop" to "new data" (on a column, suppose we call it "ID").
Can there be an entry in "new data" but no matching entry in “Table Pop”?
let's say there cannot be
I think I know the answer to this but I will ask it anyway. Can there be entries in “Table Pop” but no matching entry in "new data"?
yes there can be entries that do not match with "new data"
When you enter data into "new data" will you find the matching entry(s) in “Table Pop” at the same time?
not sure what you mean at the same time but eventually in the most usable form there will be a left join of "Table Pop" to "new data"

Suppose I have two tables:

Table Pop:
CITY, STATE, ZIP, FACILITY, POPULATION

Table 2:
CITY, NAME

i have a form set up so that the user can append new records for the "new data" and a column for ID (key for "Table Pop") to a second table ("Table 2").

how can i use forms (perhaps a button with sql code) to left join Table 2 with Table Pop? one thing i want to do is query the existing table (and create a view or table) using a time-stamp that i have inserted by default

e.g.:

SELECT "l"."first_name", "l"."last_name", "l"."number", "a"."Facility", "a"."Mailing Address", "a"."City", "a"."State", "a"."Zip", "l"."Paid", "l"."Notes"
FROM "address" "a", "letters" "l"
WHERE "a"."id" = "l"."id" AND ("l"."date_entered" BETWEEN {D '2016-07-04 00:00:00' } AND {D '2016-07-05 00:00:00' })

i tried using the proper Base syntax to create a table (using create table or select into) and it will not run in forms. I can get it to run in Queries but Queries does not seem to allow me to store/manipulate variables (i.e. timestamp column).
WIN 7 X64
OpenOffice 4.1.2 AOO412m3
mouseandcat
Posts: 7
Joined: Sun Jul 03, 2016 4:41 am

Re: Take new input and insert into new table

Post by mouseandcat »

i also want to clarify what i really want to do:

export a table (comprised of two joined tables) using some of the features found in Form: buttons, drop down lists.
WIN 7 X64
OpenOffice 4.1.2 AOO412m3
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Take new input and insert into new table

Post by Villeroy »

A report is an export to text document.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
mouseandcat
Posts: 7
Joined: Sun Jul 03, 2016 4:41 am

Re: Take new input and insert into new table

Post by mouseandcat »

vill, thanks. i'll have to check later tonight. i didn't see a way to export it tab or comma delimited text nor could i find anything online but i can't say i poked around much with reports.
WIN 7 X64
OpenOffice 4.1.2 AOO412m3
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Take new input and insert into new table

Post by Villeroy »

Base is a bridge between databases and office documents.
You can put it in a spreadsheet and save as csv.
[Tutorial] Using registered datasources in Calc
You can do something similar with Writer.
You can use the database program (HSQL I guess), prepare a linked table and drag a view over the link.
[Tutorial] Using csv/text files as editable data source.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
UnklDonald418
Volunteer
Posts: 1544
Joined: Wed Jun 24, 2015 12:56 am
Location: Colorado, USA

Re: Take new input and insert into new table

Post by UnklDonald418 »

I have uploaded a rather crude attempt at what I think you are trying to do. But I've designed the tables to conform more closely to the standards of good database design. It's meant to show you a new way of thinking about how your database might work.
Start by looking at the tables.
I've added one table that is simply a list of State Abbreviations and the associated State Names, its function is to reduce some errors when inputting State information.
The fields in POP should look familiar, but the fields in NEWDATA are where our designs begin to diverge. In particular note that I have added a field “TABLE_NAME”. This where I am trying to steer you away from the notion that you need umpteen different tables.
Now open the form NEWDATA. This is still pretty rough but it should give you an idea of what is possible.
The STATE column uses a listbox supplied by a query to allow the user to pick which state they need. You can either click on the arrow to display the list, or you can begin typing the name of the state and it should make a guess as to what you want. This column displays the state name but actually stores the abbreviation.
The column named POP_LINK is another listbox that is supplied by another query. It displays the city name, but actually stores the value POP_ID.
The field TABLE_NAME is where you can have
control over "full table" name
While I haven't provided one here a query can narrow the displayed data to a particular TABLE_NAME without going through the brain damage of actually creating a new table.
If you open the form in the edit mode and select the Form Navigator you can see the structure of the form. On the NEWDATA table right-click on the STATE column and select Column from the list that pops up. Select the Data tab in the Properties: List Box window to see the SQL query that supplies data to this column. If you click on the POP_LINK column you can see that query also.

In the Queries section I left the two queries I used on the form. The form actually uses a copy of the SQL statements so if these get changed the form is not affected. If you open them in the edit mode you can see how they are constructed.
Attachments
POPtest.odb
(20.45 KiB) Downloaded 259 times
If your problem has been solved, please edit this topic's initial post and add "[Solved]" to the beginning of the subject line
Apache OpenOffice 4.1.14 & LibreOffice 7.6.2.1 (x86_64) - Windows 10 Professional- Windows 11
UnklDonald418
Volunteer
Posts: 1544
Joined: Wed Jun 24, 2015 12:56 am
Location: Colorado, USA

Re: Take new input and insert into new table

Post by UnklDonald418 »

I've uploaded POPtest01, an expanded version that that adds a 'TimeStamp' field “TS”. Then to make the timestamp entry automatic I executed the following in Tools->SQL

Code: Select all

ALTER TABLE "NEWDATA1" ALTER COLUMN "TS" TIMESTAMP(0) DEFAULT CURRENT_TIMESTAMP;
There is a form NEWDATA1 that is used for data entry with a TimeStamp and several new forms demonstrating various methods of displaying the data. Including one way to display data using a range of TimeStamps. That and another form make use of Filter Tables. There is also a form that makes use of a Parameter Dialog.
Attachments
POPtest01.odb
(61 KiB) Downloaded 298 times
If your problem has been solved, please edit this topic's initial post and add "[Solved]" to the beginning of the subject line
Apache OpenOffice 4.1.14 & LibreOffice 7.6.2.1 (x86_64) - Windows 10 Professional- Windows 11
Post Reply