[Solved] Auto populate and update multiple fields

Discuss the database features
Post Reply
nicromx
Posts: 2
Joined: Thu Dec 27, 2012 7:59 pm

[Solved] Auto populate and update multiple fields

Post by nicromx »

I have three tables in this database; Clients, Products, Transactions.
Clients: Has one column containing the name of clients.
Products: Has two columns for Product name and corresponding Price.
Transactions: Has three columns for Client, Product, and Price.

I have one form in the database title TransactionEntry. When I select the client and product I need the form to auto fill the price field and populate client, product, and price into the Transactions table.

From the TransactionsEntry form I input clients name (populated by the Clients table) and the product (populated from the Products field.) I need the price field to auto update based on the Products table. All three fields need to be entered into the Transactions table.
Attachments
Clients.odb
(11.73 KiB) Downloaded 129 times
Last edited by RoryOF on Mon Dec 31, 2012 9:43 am, edited 2 times in total.
Reason: Added green tick
OpenOffice 3.4.1 on MacOS
eremmel
Posts: 1080
Joined: Tue Dec 30, 2008 1:15 am

Re: Auto populate and update multiple fields

Post by eremmel »

With databases and tables like transactions, you keep only 'references' to the other tables and might add a transaction date in the transaction table. In your case the reference is also the 'semantic' data. That is somewhat unusual.
But from the transaction table you have the reference to the Product, so implicit also its price. It is a good habit not to copy (replicate) data in a database.

It looks like you are rather new to Databases, so please try to read some tutorial etc before you continue to develop and application.
It's Microsoft marketing that tells you computers are qualified for non-technicians
W11 22H2 (build 22621), LO 7.4.2.3(x64)
path32
Posts: 136
Joined: Tue Oct 11, 2011 5:44 am
Location: Philippines

Re: Auto populate and update multiple fields

Post by path32 »

Hi,

I have a question about this thread.. clarification
1.) The price for the transactions is reflected on the product?
-the price for the Product 1 is equal to -10 then if i choose Product 1 in transaction the price will update to -10

2.) The price for the product is reflected on the transaction?
-the price for the product 1 is equal to the latest price that input to the transaction

I think you must delete one column, either price for the transaction or price for the product since the query will copy of compute for the price.
Apache Open Office 3.4, 4.0, HSQLDB 1.8(non embedded database) Windows 7, UBUNTU 10.04
F3K Total
Volunteer
Posts: 1038
Joined: Fri Dec 16, 2011 8:20 pm

Re: Auto populate and update multiple fields

Post by F3K Total »

Hello nicromx,
there is a possibility to do nearly what you want without need to use a macro. Look at the attached example, analyse the form structure with the form navigator to understand it. The advantage is, that you're able to change prices in table products, but keep old prices in table transactions.
R
Attachments
Clients.odb
(13.23 KiB) Downloaded 183 times
  • MMove 1.0.6
  • Extension for easy, exact positioning of shapes, pictures, controls, frames ...
  • my current system
  • Windows 10 AOO, LOLinux Mint AOO, LO
nicromx
Posts: 2
Joined: Thu Dec 27, 2012 7:59 pm

Re: Auto populate and update multiple fields

Post by nicromx »

path32 wrote: 1.) The price for the transactions is reflected on the product?
-the price for the Product 1 is equal to -10 then if i choose Product 1 in transaction the price will update to -10
2.) The price for the product is reflected on the transaction?
-the price for the product 1 is equal to the latest price that input to the transaction
The price is not reflected in the product name. I should have used Product A, Product B, Product C in the example. I appreciate your reply. I think my question has been answered.

F3K Total wrote:Hello nicromx,
there is a possibility to do nearly what you want without need to use a macro. Look at the attached example, analyse the form structure with the form navigator to understand it. The advantage is, that you're able to change prices in table products, but keep old prices in table transactions.
R
I think this what I was looking for. I actually started down the road of referencing product IDs. Good point on price changes though. I think your suggestion(attachment) is going to work better for my project. Thank you.
OpenOffice 3.4.1 on MacOS
Post Reply