OpenOffice.org Forum at OOoForum.orgThe OpenOffice.org Forum
 
 [Home]   [FAQ]   [Search]   [Memberlist]   [Usergroups]   [Register
 [Profile]   [Log in to check your private messages]   [Log in

creating buttons in customer database

 
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Base
View previous topic :: View next topic  
Author Message
mike_s
General User
General User


Joined: 15 Apr 2012
Posts: 7

PostPosted: Sun Apr 15, 2012 1:06 pm    Post subject: creating buttons in customer database Reply with quote

Hi

Apologies if this gets asked many times. I have a heating/plumbing business and I have just started creating a customer database using open office for the first time.

How do I add buttons so that I can click onto a customer then it will open up a new table showing more details such as previous job notes and appliance details.

Any help really appreciated as I'm not too clever with software.

Regards

Mike
Back to top
View user's profile Send private message
dacm
Super User
Super User


Joined: 07 Jan 2010
Posts: 769

PostPosted: Sun Apr 15, 2012 10:34 pm    Post subject: Reply with quote

Welcome Mike,

That can be done on the same physical Form using a SubForm:
[Example #1] Filter/Search with Forms (leveraging SubForms)

Or you can open another Form "filtered" to show the current client details using a macro:
[Example] OpenForm with macros
_________________
Soli Deo gloria
Tutorial: avoiding data loss with Base + Splitting 'Embedded databases'
Back to top
View user's profile Send private message
mike_s
General User
General User


Joined: 15 Apr 2012
Posts: 7

PostPosted: Sun Apr 15, 2012 11:19 pm    Post subject: Reply with quote

that's great thanks, will get to work on it, will be my evening job for the week
Back to top
View user's profile Send private message
mike_s
General User
General User


Joined: 15 Apr 2012
Posts: 7

PostPosted: Mon Apr 16, 2012 11:45 am    Post subject: Reply with quote

just started going through this. What are the advantages of each of the 2 methods?

I would like to be able to search for a customer via name or address then click onto it to open up more details such as previous jobs so I can click on them to see more details of link to the invoice.

Same goes for the gas appliances they have so when I can click on the gas appliance to open up more details such as serial number, when next service is due etc.

Thanks for your patience, I'll keep working at it in the mean time.
Back to top
View user's profile Send private message
dacm
Super User
Super User


Joined: 07 Jan 2010
Posts: 769

PostPosted: Tue Apr 17, 2012 10:08 am    Post subject: Reply with quote

mike_s wrote:
What are the advantages of each of the 2 methods?

The advantage of using a SubForm is that you have the information immediately in front of you AND you don't have to get into macros, which can be very difficult to learn.

The disadvantage of a SubForm is that you have the information immediately in front of you which adds clutter to your Form.

The advantage of using a Macro is that it allows you to hide the details until you actually need them. And you can assign multiple push buttons to different Forms to popup various details, just as you've described.

The disadvantage of Macros is that they're exceedingly difficult to master, although I hope the demo (at the above link) will provide enough boilerplate code for most situations.
_________________
Soli Deo gloria
Tutorial: avoiding data loss with Base + Splitting 'Embedded databases'
Back to top
View user's profile Send private message
mike_s
General User
General User


Joined: 15 Apr 2012
Posts: 7

PostPosted: Tue Apr 17, 2012 10:13 am    Post subject: Reply with quote

Thanks, much appreciated. I read through the tutorials and have decided to stick to subforms until I have got to grips with the basics.

The only difficulty I'm having which is very fundamental is actually editing the field names once created or adding new fields?

I'm trying to figure it out now. I have created a table, a form based on that and a subform but I want the subform to have different fields? If I could do this then I can keep my database simple for now.
Back to top
View user's profile Send private message
dacm
Super User
Super User


Joined: 07 Jan 2010
Posts: 769

PostPosted: Tue Apr 17, 2012 12:37 pm    Post subject: Reply with quote

mike_s wrote:
...editing the field names once created or adding new fields?

The Form Wizard creates 'grouped' Controls. Press 'Ctrl' and left-click the specific Form-Control to select within a group.

mike_s wrote:
...but I want the subform to have different fields?
When creating a SubForm, you can base it on the same Table/Query as the MainForm, or you can select a related Table/Query which will offer different fields.

Perhaps post an example of your design without personal data.
_________________
Soli Deo gloria
Tutorial: avoiding data loss with Base + Splitting 'Embedded databases'
Back to top
View user's profile Send private message
mike_s
General User
General User


Joined: 15 Apr 2012
Posts: 7

PostPosted: Tue Apr 17, 2012 1:56 pm    Post subject: Reply with quote

ah, I've just created a subforum based on a separate table which is shown below. However I have a problem that with each field in the main form, the fields in the subform remain the same. I have took a screenshot and will try to post an example. By the way I can't find the screen where I can ungroup the fields like you say.
Also the font is white and that needs changing also but that is the least of my worries.

So what I want to happen is when I click on the customers details in the main form then the appliances they have will show in the subform.


Back to top
View user's profile Send private message
dacm
Super User
Super User


Joined: 07 Jan 2010
Posts: 769

PostPosted: Tue Apr 17, 2012 11:39 pm    Post subject: Reply with quote

Busy with taxes today...

Perhaps see: http://www.oooforum.org/forum/viewtopic.phtml?p=471544#471544
_________________
Soli Deo gloria
Tutorial: avoiding data loss with Base + Splitting 'Embedded databases'
Back to top
View user's profile Send private message
Arineckaig
OOo Advocate
OOo Advocate


Joined: 01 Mar 2004
Posts: 358

PostPosted: Wed Apr 18, 2012 12:07 am    Post subject: Reply with quote

mike_s posted:
Quote:
So what I want to happen is when I click on the customers details in the main form then the appliances they have will show in the subform.

It is not easy to tell from the screen dump, but the crtical element of the sub-form/main-form relationship is that the data source table for the sub-form contains a foreign key field linked to the primary key field of the data source table for the main form. While that foreign key field need not be displayed in the sub-form it must exist in the sub-form's data source table.

As soon as the record pointer in the main form is moved from one record to another, Base will automatically update the display in the sub-form to include only those records whose foreign key field matches the primary key field of newly selected record in the main form.
_________________
When this issue has been resolved, it helps other users of the forum if you add the word [Solved] to the Subject line of your 1st post (edit button top right).
OOo 3.4.1 and MySQL on MS Windows XP and Ubuntu
Back to top
View user's profile Send private message
mike_s
General User
General User


Joined: 15 Apr 2012
Posts: 7

PostPosted: Wed Apr 18, 2012 11:39 am    Post subject: Reply with quote

Ok, from the wizard I linked the customer id's together from the main form and the sub form but only one row of fields show at a time in the sub form.

Most customers need multiple rows of fields joined in the sub form so I need to set multiple foreign keys for each primary key it seems.

I don't know where or how I do this.

Thanks again for all of your help, I am progressing and will have it soon as this won't be the most complicated database.
Back to top
View user's profile Send private message
Arineckaig
OOo Advocate
OOo Advocate


Joined: 01 Mar 2004
Posts: 358

PostPosted: Thu Apr 19, 2012 12:30 am    Post subject: Reply with quote

mike_s:
Quote:
so I need to set multiple foreign keys for each primary key it seems

My apologies if I misunderstand the level of your experience with Relational Databases, but I suspect the problem lies in the design of your database: in particular, in the relationships between the tables in your database. From your posts I assume you have at least two tables each of which have several fields: for example,

Customers table:
CustomerID
FirstName
etc.

Job Table:
ID
Type
Make
Model
etc.

For each customer there could be several jobs - a one-to-many relationship. If this is the case you must also have a special field in the "Job table" which holds the value if the "CustomerID" field to record for whichever customer each job was performed. Only in this way can each record in the "Job table" be linked or related a particular customer.

If you have not already set up the two tables with such respective linking fields, I would suggest you look an an introductory tutorial on database design that can be downloaded from
http://www.scribd.com/doc/48635562/Base-Tutorial-OOo.

Without a suitable underlying design for a database, it is difficult to avoid subsequent problems when constructing forms and sub-forms.

Please come back if you have any problems with the tutorial or if this suggestion fails to address the source of the problem.
_________________
When this issue has been resolved, it helps other users of the forum if you add the word [Solved] to the Subject line of your 1st post (edit button top right).
OOo 3.4.1 and MySQL on MS Windows XP and Ubuntu
Back to top
View user's profile Send private message
mike_s
General User
General User


Joined: 15 Apr 2012
Posts: 7

PostPosted: Thu Apr 19, 2012 12:32 pm    Post subject: Reply with quote

Sorry I'm having difficulty. Been going through that tutorial and whilst it is very good I'm not picking it up very well. I'll understand it more when I get to grips with the basics.

I think I need 3 tables:
Table 1. Customer information (shown in the main form).
Table 2. Appliance information (shown in the sub form).
Table 3. Invoices (also shown in the sub form with maybe a macro or hyperlink to open up the invoices stored on my computer).



When I click on each customer id which I can use as the primary key, I need it to just show the appliance information for appliances they own in the sub form that is based on table 2.

So for this I think I need a one to many relationship. I am having difficulty understanding how to set multiple foreign keys up or just how I achieve what I have described.

We won't worry about table 3 now as I would like to add that one as a macro at a later date.

Thanks, hope I can work this out.
Back to top
View user's profile Send private message
Arineckaig
OOo Advocate
OOo Advocate


Joined: 01 Mar 2004
Posts: 358

PostPosted: Fri Apr 20, 2012 12:05 am    Post subject: Reply with quote

Quote:
So for this I think I need a one to many relationship. I am having difficulty understanding how to set multiple foreign keys up or just how I achieve what I have described.

I suggest you have a careful look at Chapter 4 of the Tutorial. In particular page 44 is concerned with the issue of adding a foreign key field to the table that is on the many side of a one-to-many relationship. You will find around page 93 of the Tutorial references to use of a sub-form to display all the records from the many side table that are applicable to the primary key of the particular record selected in the main form that is sourced from the one side table.
_________________
When this issue has been resolved, it helps other users of the forum if you add the word [Solved] to the Subject line of your 1st post (edit button top right).
OOo 3.4.1 and MySQL on MS Windows XP and Ubuntu
Back to top
View user's profile Send private message
Billyray
OOo Enthusiast
OOo Enthusiast


Joined: 06 Mar 2007
Posts: 144
Location: Lake Erie's Shore in Ohio

PostPosted: Fri Apr 20, 2012 11:58 am    Post subject: Reply with quote

Quote:
I think I need 3 tables:
Table 1. Customer information (shown in the main form).
Table 2. Appliance information (shown in the sub form).
Table 3. Invoices (also shown in the sub form with maybe a macro or hyperlink to open up the invoices stored on my computer).


I have a business with similar needs.

for invoices think sales order.
Sales orders will have the necessary data to create invoices.
For example:
Sales Order table:
    *Order ID (can be auto-increment and primary key)
    *CustomerID (this is the foreign key Arineckaig is talking about. This links your sales order to one specific customer)
    *Date of Order
    *Type of Order
    *any other fields related specifically to that order (no detail items yet)


I have two more tables related to the Sales Orders table. They are:

Receipts Table. Which has:


    *Receipt ID (can be an auto-increment field and a primary key)
    * Order ID (this is the foreign key which is the Order number above. It links these receipts to your Sales Order record. I get multiple payments on one order, so this is why I have this broken down into a separate table here. If an order ALWAYS had just one payment, then it could be a field in the Sales Order Table.
    * Date of payment
    * Amount of Payment
    * Details of Payment (check no. cash whatever)


Then I also have an Order Details Table, which has:


    * Order Details ID (can be an auto-increment field and a primary key)
    * Order ID (foreign key, links this record to one specific order)
    * Quantity
    * Type of product, Part no. etc.
    * Description
    * Price


Now you have enough info to create:

An order entry form
Invoices
Statements
whatever else you need.
It is just to give you an idea of how you might set up your tables. Wink
_________________
Billyray
using:
Linux distro: Ubuntu 10.04 LTS, Lucid Lynx, OOo Base 3.3, connected to MySql database using Java jdbc (note: ONLY sun-java-6-1.6.0_22 jre works right), and converted from MS Access 2003.
Back to top
View user's profile Send private message
Display posts from previous:   
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Base All times are GMT - 8 Hours
Page 1 of 1

 
Jump to:  
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group