[Solved] How to add orders for multiple parts in a form?

Creating and using forms

[SOLVED]Re: How to add orders for multiple parts in a form?

Postby dazedandconfused » Wed Sep 25, 2013 9:43 pm

Villeroy wrote:When you edit a group you can select each member of that group. You temporarily de-activate the grouping without dissolving the group. This is helpful when you have groups of dozends of members (mostly in Impress or Draw).


I just want my list box to be a part of the main form. What's the best way to do that? Customer A goes w/ PO 1 and customer B goes w/ PO 2. However, right now if I go to PO 2 it shows customer A and that list box never changes and the selection isn't saved with the rest of the form data.
Last edited by dazedandconfused on Tue Oct 15, 2013 9:05 pm, edited 1 time in total.
Open office 4.0, win7
dazedandconfused
 
Posts: 73
Joined: Tue Sep 03, 2013 8:38 pm

Re: How to add orders for multiple parts in a form?

Postby Villeroy » Wed Sep 25, 2013 10:21 pm

Get the form navigator and drag the control to the right form.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 26733
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: How to add orders for multiple parts in a form?

Postby dazedandconfused » Thu Sep 26, 2013 5:29 pm

Villeroy wrote:Get the form navigator and drag the control to the right form.


Thanks. Ugh, turns out it was part of the main form which is why drag and drop didn't work earlier.. However it doesn't function along with the main form. I wonder if I'm making any sense? Customer is a dropdown and there should be a different one for each record. It allows me to select a customer from the dropdown but it isn't saved with the rest of the data. Everything I created w/ the wizard is saved together but my customer dropdown is an unwelcome guest. I added this dropdown as a list box after I used the agent to create my form w/ a subform

Let me try again. From the dropdown I select customer 1 for PO 1 then use the navigation bar to move to PO 2 and customer 1 is still in the drop down. I change it to customer 2. Then I move to PO 3 and customer 2 is still selected in the dropdown. If I then move back to PO 1 now customer 2 is shown in the dropdown area. How the heck do I get it to function along with the rest of the mainform??? I want customer 1 to be saved w/ PO one data and so on.
Open office 4.0, win7
dazedandconfused
 
Posts: 73
Joined: Tue Sep 03, 2013 8:38 pm

Re: How to add orders for multiple parts in a form?

Postby dazedandconfused » Thu Sep 26, 2013 9:19 pm

I have an unrelated question to the problem I still have above.

I have the salesman name in the PO invoice form. I linked it by the master/slave to a column in PO Items because I think it might make it easier to run a query when trying to figure out how much each salesman has sold over a period of time. I'll probably hide this column from the user. Anyway, this column shows the salesman number instead of the name. In other words in the main form it shows the name "Jim" who is the first salesman listed. In the subform it shows the number 1 instead of the name "Jim." Is there a way to change that so it shows the name instead of the number? If so I can't figure it out.
Open office 4.0, win7
dazedandconfused
 
Posts: 73
Joined: Tue Sep 03, 2013 8:38 pm

Re: How to add orders for multiple parts in a form?

Postby MTP » Thu Sep 26, 2013 9:26 pm

Do you have a version of your file that you could upload? There are several setup selections that could be related to your listbox function that are difficult to diagnose with word descriptions but would be easy to find when looking at an actual file.

A listbox can have two values: a 'bound' value that is saved to the underlying table field, and a 'visible' column that is what you see on the form. The most common way to do this: on the 'Data' tab of the properties window, 'Type of list contents' has to be 'SQL' and in 'List content' you have to write a SQL statement, something like
Code: Select all   Expand viewCollapse view
SELECT "VisibleField", "BoundField" FROM "Table"
OpenOffice 4.1.1 on Windows 10, HSQLDB 1.8 split database
User avatar
MTP
Volunteer
 
Posts: 1620
Joined: Mon Sep 10, 2012 7:31 pm
Location: Midwest USA

Re: How to add orders for multiple parts in a form?

Postby dazedandconfused » Thu Sep 26, 2013 10:01 pm

MTP wrote:Do you have a version of your file that you could upload? There are several setup selections that could be related to your listbox function that are difficult to diagnose with word descriptions but would be easy to find when looking at an actual file.

A listbox can have two values: a 'bound' value that is saved to the underlying table field, and a 'visible' column that is what you see on the form. The most common way to do this: on the 'Data' tab of the properties window, 'Type of list contents' has to be 'SQL' and in 'List content' you have to write a SQL statement, something like
Code: Select all   Expand viewCollapse view
SELECT "VisibleField", "BoundField" FROM "Table"


Unfortunately there are some name and phone numbers, etc there that I probably shouldn't put on the internet.

The main form is called "Po Invoice" The column in question is called "Salesman." I have that as a dropdown list box w/ an sql statement in there. I really just clicked that "..." box and selected the name and ID from the salesman table. I didn't write anything fancy. The sub form is called "PO items" and the column is also called "Salesman." Are you suggesting that I change that subform to a list box so I can write an sql statement?

If so would that be :

Code: Select all   Expand viewCollapse view
SELECT "VisibleField", "BoundField" FROM "Po invoice"


I tried and got the error attached. I tried replacing "visiblefield" "boundfield" with "salesman" but still got an error. How should I write it exactly?
Attachments
SALESMAN ERROR.PNG
Open office 4.0, win7
dazedandconfused
 
Posts: 73
Joined: Tue Sep 03, 2013 8:38 pm

Re: How to add orders for multiple parts in a form?

Postby MTP » Fri Sep 27, 2013 1:33 am

Can you make a copy of your database, delete all the rows in the tables, and put in just 2-3 dummy rows in each table to make it safe to upload?

Clicking the "..." box gives you a GUI and Base will try to write the sql statement for you. IMO the GUI is more difficult to use than the sql. You have to learn the same things to use either one effectively. How everything relates behind the scenes is a lot to take in, but it gets easier with practice.

I'm guessing you have a table that lists your salesman's name, their ID number, and maybe other information? If that table were called "SalesmanTable" and had columns "SalesmanName" and "SalesmanID", then to get a listbox to show the names but save the ID number you'd write
Code: Select all   Expand viewCollapse view
SELECT "SalesmanName", "SalesmanID" FROM "SalesmanTable"
Note that you are pulling the names FROM the salesman table (per the sql) and saving the ID numbers TO the PO table (per the listbox being part of a form based on the PO table).
OpenOffice 4.1.1 on Windows 10, HSQLDB 1.8 split database
User avatar
MTP
Volunteer
 
Posts: 1620
Joined: Mon Sep 10, 2012 7:31 pm
Location: Midwest USA

Re: How to add orders for multiple parts in a form?

Postby dazedandconfused » Mon Sep 30, 2013 10:40 pm

MTP wrote:Can you make a copy of your database, delete all the rows in the tables, and put in just 2-3 dummy rows in each table to make it safe to upload?

Clicking the "..." box gives you a GUI and Base will try to write the sql statement for you. IMO the GUI is more difficult to use than the sql. You have to learn the same things to use either one effectively. How everything relates behind the scenes is a lot to take in, but it gets easier with practice.

I'm guessing you have a table that lists your salesman's name, their ID number, and maybe other information? If that table were called "SalesmanTable" and had columns "SalesmanName" and "SalesmanID", then to get a listbox to show the names but save the ID number you'd write
Code: Select all   Expand viewCollapse view
SELECT "SalesmanName", "SalesmanID" FROM "SalesmanTable"
Note that you are pulling the names FROM the salesman table (per the sql) and saving the ID numbers TO the PO table (per the listbox being part of a form based on the PO table).


Great idea! I did that and have attached the database.
Attachments
Copy of database.odb
(100.65 KiB) Downloaded 61 times
Open office 4.0, win7
dazedandconfused
 
Posts: 73
Joined: Tue Sep 03, 2013 8:38 pm

Re: How to add orders for multiple parts in a form?

Postby dazedandconfused » Tue Oct 01, 2013 3:44 pm

MTP wrote:Can you make a copy of your database, delete all the rows in the tables, and put in just 2-3 dummy rows in each table to make it safe to upload?

Clicking the "..." box gives you a GUI and Base will try to write the sql statement for you. IMO the GUI is more difficult to use than the sql. You have to learn the same things to use either one effectively. How everything relates behind the scenes is a lot to take in, but it gets easier with practice.

I'm guessing you have a table that lists your salesman's name, their ID number, and maybe other information? If that table were called "SalesmanTable" and had columns "SalesmanName" and "SalesmanID", then to get a listbox to show the names but save the ID number you'd write
Code: Select all   Expand viewCollapse view
SELECT "SalesmanName", "SalesmanID" FROM "SalesmanTable"
Note that you are pulling the names FROM the salesman table (per the sql) and saving the ID numbers TO the PO table (per the listbox being part of a form based on the PO table).


The table is called, "PO INVOICE." Columns are "Salesman" and "ID" Therefore I changed the box on the sub form to a list box and changed type of list contents to "SQL" I entered

Code: Select all   Expand viewCollapse view
SELECT "Salesman", "ID" FROM "PO INVOICE"


however that still didn't help. Now it has a 1 in the box, but doesn't actually show it unless I click on it.
Open office 4.0, win7
dazedandconfused
 
Posts: 73
Joined: Tue Sep 03, 2013 8:38 pm

Re: How to add orders for multiple parts in a form?

Postby Villeroy » Tue Oct 01, 2013 6:34 pm

Get the data properties of the subform.
Currently the master --> slave binding is:
FK_ID_PO --> ID
SALESMAN --> Salesman
The second pair is obsolete.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 26733
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: How to add orders for multiple parts in a form?

Postby dazedandconfused » Tue Oct 01, 2013 8:26 pm

Villeroy wrote:Get the data properties of the subform.
Currently the master --> slave binding is:
FK_ID_PO --> ID
SALESMAN --> Salesman
The second pair is obsolete.


Thank you. I removed SALESMAN --> Salesman, but I would like it to display mickey mouse or Donald duck instead of 1 and 2
Open office 4.0, win7
dazedandconfused
 
Posts: 73
Joined: Tue Sep 03, 2013 8:38 pm

Re: How to add orders for multiple parts in a form?

Postby Villeroy » Tue Oct 01, 2013 8:47 pm

dazedandconfused wrote:Thank you. I removed SALESMAN --> Salesman, but I would like it to display mickey mouse or Donald duck instead of 1 and 2

Use a list box like you do in the parent form.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 26733
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: How to add orders for multiple parts in a form?

Postby dazedandconfused » Tue Oct 01, 2013 10:21 pm

THANKS!! I swear I tried that before but it didn't work. Obviously I was wrong because it works now.

I'm having a weird problem and it could be a rather big problem. I select customer and salesmen in the main form then move down to the Items subform to enter data. Here in ID 17 I select product type then tab to order price and enter 6 items. For ID 46 I entered 200, then pressed enter. Now I'm done entering data and I want to move on to the 5th Purchase order. I used the navigator to move to item 5 and then came back to item 4 immediately. When I did noticed that the order price of 200 that I just entered wasn't saved. However, if I enter 200 then click on the row below it or even tab to the row below it the data is saved.

So it looks like I have to select the next row for the data to save and that can be a problem. Is there any way to change this so that the information is saved as soon as I hit enter? I can see a salesman forgetting to tab all the way to the next line and therefore constantly losing the last item they enter.

Make that 2 problems. When I enter an item, here in the screen shot ID 47 the salesman's name is saved on the row below where it should be.
Attachments
salesman error.PNG
Copy of database.odb
(101.52 KiB) Downloaded 60 times
Open office 4.0, win7
dazedandconfused
 
Posts: 73
Joined: Tue Sep 03, 2013 8:38 pm

Re: How to add orders for multiple parts in a form?

Postby dazedandconfused » Tue Oct 08, 2013 7:49 pm

Is there a way to change the input so that it's saved when I press the enter button or if I tab to another column? Right now it looks like the only way an entry is saved in the sub form is if I move the cursor to the next row. If someone enters data and forgets to click on the row below their entry then the data is lost.
Open office 4.0, win7
dazedandconfused
 
Posts: 73
Joined: Tue Sep 03, 2013 8:38 pm

Re: How to add orders for multiple parts in a form?

Postby Hir23 » Tue Mar 12, 2019 7:43 pm

F3K Total wrote:Hi,
if i got you right, you need an invoice table and an item table with foreign key from invoice table.
In a form, this can be done with a MainForm and a SubForm, bound to the MainForm by the foreign key, which is the ID of the current invoice.
Find attached a simple example to study the structure, especially the Link master Fields/Link slave fields on data tab of the SubForm.
R

Hi can you elaborate on how you created your form? in the test db?
LibreOffice 6.2 on Windows 10
Hir23
 
Posts: 1
Joined: Tue Mar 12, 2019 7:40 pm

Previous

Return to Forms

Who is online

Users browsing this forum: No registered users and 2 guests