[Solved] Auto populate and update multiple fields

Discuss the database features

[Solved] Auto populate and update multiple fields

Postby nicromx » Thu Dec 27, 2012 9:55 pm

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 46 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
nicromx
 
Posts: 2
Joined: Thu Dec 27, 2012 7:59 pm

Re: Auto populate and update multiple fields

Postby eremmel » Thu Dec 27, 2012 10:15 pm

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.
Please, edit this thread's initial post and add "[Solved]" to the subject line if your problem has been solved.
OOo 3.3.0 on XP SP3 for real life with ORB; AOO 3.4.1, 4.0.1, LO 4.1.2.3 on W7 for testing
User avatar
eremmel
Volunteer
 
Posts: 656
Joined: Tue Dec 30, 2008 1:15 am
Location: Barneveld, Netherlands

Re: Auto populate and update multiple fields

Postby path32 » Fri Dec 28, 2012 4:35 am

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
path32
 
Posts: 136
Joined: Tue Oct 11, 2011 5:44 am
Location: Philippines

Re: Auto populate and update multiple fields

Postby F3K Total » Sat Dec 29, 2012 12:14 am

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 77 times
    MMove 1.0.6
  • Extension for easy, exact positioning of shapes, pictures, controls, frames ...
    my current system
  • Windows 7 OOo 3.3.0, AOO 3.4.1, 4.0.1, LO 4.0.6, 4.1.5 Linux Mint AOO 3.4.1, 4.0.1, LO 4.0.6, 4.1.5
F3K Total
Volunteer
 
Posts: 403
Joined: Fri Dec 16, 2011 8:20 pm

Re: Auto populate and update multiple fields

Postby nicromx » Mon Dec 31, 2012 5:02 am

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
nicromx
 
Posts: 2
Joined: Thu Dec 27, 2012 7:59 pm


Return to Base

Who is online

Users browsing this forum: No registered users and 3 guests