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

[dazedandconfused]

I'm teaching myself open office and I've read several tutorials, spent hours watching lectures on youtube, read the OO 3.4, volume 3: Base book and I've spent weeks trying to figure out the answer to my question by myself and finally decided I need some professional help. I'm suffering from information overload.

I have a table for customer list, product list, product type. I have a Sales record form created where I have information like, Date, customer (a drop down combo box using the customer list table), P.O. #, invoice #, product type (also a drop down combo box using product type table).

Now my goal is to enter multiple items per order in the aforementioned form. I would like to have a column for "quantity" and another for "part number" One column I would enter a number from 1-100 and the column I'd select from a drop down of "part number." I would like to enter an infinite number of 2 column rows (really it won't often be more than 15) that display the quantity of each part ordered. I would like these to be related to the P.O # as well as invoice #. What is the best way to do that?

Things I've tried:

1.I tried creating a combo box but that just doesn't seem right.
2. I next looked into a subform but it looks like the subform will just display the data and won't let me select from a list of parts.
3. I looked into using a switchboard but that didn't seem like what I needed.
4. A friend suggested setting up the form in tabular format which sounded exactly what I was looking for but I can't figure out how to do that in design view or if it's possible?
5. I tried creating a form called "quantity and part". It had the fields "quantity" and "part number".....I would like part number to be a dropdown but I couldn't figure out how to do that or again if that was possible so that's where I ran into the wall.
6. Lastly I researched adding a table control (form navigator, add subform, add table control) and I've spent the last couple days on that but can't get it to display multiple rows or allow me to select from a drop down.


Now like I said in the beginning with all these options I'm confused about what to do next? Any guidance would be very much appreciated.

[F3K Total]

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

[dazedandconfused]

Thank you so much for taking the time to answer. You've got me now considering this from another angle.

I'm working through your example and modified it very slightly. I'm having the following error trying to assign a relationship between customer and FK_ID_customer. I wanted the customer field to be a primary key. Customer isn't the best title since really it's the company name. Am I having this because the customer is a "varchar" and FK_ID_customer is an "integer?

Also I noticed that under column ID it says "auto." How do you do that and is that what allows an infinite number of rows?

[F3K Total]

Hi,

Am I having this because the customer is a "varchar" and FK_ID_customer is an "integer?

Yes

Also I noticed that under column ID it says "auto." How do you do that and is that what allows an infinite number of rows?

If you create a column type BigInteger or Integer, you can define it as AutoValue, which by default means, that the first record (row) automatically will get 0 (zero), the next 1, 2 .. and so on if they where saved for the first time (created). This makes sure, that you e.g. have every ID only once in the table, which is required for a PrimaryKey.

AVPK.png (4.07 KiB) Viewed 12492 times

It's not possible to give you a BASE-course here in the forum, please read the basic tutorials in this thread, or have a look to the BASE Handbook (Libre Office) on this page.
R

[dazedandconfused]

Thanks again. I've read through the tutorials and studied your example very hard and duplicated all tables and your form until I came up with the exact same result and I understand what you did....pretty much although I'm brand new at SQL. Then I created tables and a form based off your example. I learned quite a bit especially with regards to changing columns to combo boxes and using SQL commands so I appreciate that very much also.

I'm having one problem, however. I have the relationships set up as shown in the picture. When I duplicated yours I could enter a new item in the invoice, then depending on which line I was on I could enter multiple parts on the subform. If I clicked on the line w/ ID 1 it showed me the parts for that line in the subform and the same for the other lines. It was great. The problem I'm having now is that I can't do that on my form. The subform doesn't work like yours does and I can't understand why since the relationships are the same as yours? The subform doesn't relate to the main form properly I guess. Nothing displays in FK_ID_PO . No matter what line I click on the main form the subform never changes. It's like they're separate.

Hopefully you understand.

[F3K Total]

Hello,
A guess:
the link between MainForm and Subform is missing. Open the Form in EDIT-Mode, then open the Formnavigator, right-click on Subform, Properties, tab Data should look like this:

If you can't solve the problem, please load up your file. Then we can have a look.

R

[dazedandconfused]

sigh...I made sure to do that when I duplicated your form but in creating this one I forgot even though I remember you telling me to pay special attention to it . THANK YOUUUUUUUUUUUUUU for your help!

[dazedandconfused]

Do I have to use the format in the example you gave me or can I use the attached format? I'm guessing the answer is no since I can't seem to figure out how to change anything to a combo box or list box

[F3K Total]

Hi,
you can do it, as you showed it, single or additional, or a mixture, e.g a table control for the items and single controls for the invoices.
Find the mixed version attached in Form Form_PO_invoice2.
In my point of view, the overview is worse.

... since I can't seem to figure out how to change anything to a combo box or list box...

Right-click the control or within a table control the column header an choose Replace with > and select the control Type you like.
Learn, how to use the Form Navigator in Forms-Edit-Mode.
R

[dazedandconfused]

I get an error when I open the form PO invoice...which I've opened probably 20 times without a problem. If I click "OK" then continue everything works as it should, but the error pops up every time I open the form. I certainly do have that table. I didn't make any changes to it. The only thing I changed between the last time I opened it and this time was make "quantity" a combo box and added 30 list entries. Any idea what might be causing the error?

I have a second error trying to add relationships. It says I don't have the table PO Items, which I do. I'm trying to link ID from PO invoice to fk_id_po

[Villeroy]

Some form, subform, list box or combo box is bound to a table named "PO Invoice" which does not exist.

[dazedandconfused]

Hmm...thanks!

[dazedandconfused]

Some form, subform, list box or combo box is bound to a table named "PO Invoice" which does not exist.

Turns out if I went into form navigator there was another main form called "unnamed." I'm not sure how it got there but I deleted it and everything works. 1000 thanks again!

[dazedandconfused]

I don't know if I should ask this here or start a new thread, but since it's related to the same form I'll ask here.

Part type is a list box. It has 4 choices. The 2nd choice makes up 95% of the business so I'd like to set that to default. I see the default box and type in "Standard" then save it, however nothing saves. The "..." box is there but it's not enabled.

[F3K Total]

Hi,
an example for my sample-file above:
If you'd like to have Type D (ID 3) as standard in your table PO_Items, then once run this command via Tools/SQL ...

Code: Select all   Expand viewCollapse view

ALTER TABLE "PO_Items" ALTER COLUMN "Type" set default 3

Now, when you don't select a Type and jump direct to Quantity or save the new row, D will be inserted automatically.
The D can be changed afterwards.
R

[dazedandconfused]

I went to tools/SQL and copy and pasted that command in your example:

ALTER TABLE "PO_Items" ALTER COLUMN "Type" set default 3.

It said command successfully executed. I saved it and reopened Form_Po invoce2 and selected a part from the drop down and tabbed to the type. The default, D didn't show up automatically. It shows a blue dropdown box

[F3K Total]

Hi,
the default D will be inserted

when you don't select a Type and jump direct to Quantity or save the new row

R

[dazedandconfused]

Hi,
the default D will be inserted

when you don't select a Type and jump direct to Quantity or save the new row

R

I tabbed directly to quantity and a default didn't show up. It's blank. I next tried selecting a part by clicking then clicking on quantity and moving on to the next line and the default didn't show up that way either unfortunately

[F3K Total]

Hi,
did you try to save the row?
R

[dazedandconfused]

No...because I'm an idiot. I understand now. I saved the row and the following ones defaulted to D just like you said. I just have to substitute what I need then save the first row and the rest will obey that command....ugh. I guess I've spent too much time on the database today (there is other stuff I'm doing w/ it lol). Now I just need to add "order amount." I'm hoping there is an easy way to multiply price x quantity for each line item and then tally it up! That's for tomorrow lol

[dazedandconfused]

Three questions. The main form shows Date, PO, Invoice # and customer. However, it shows all the invoices all the time even when I use the arrows to move to the next record.

1. Is there a way to hide the others and only show the selected invoice?
2. Is there anyway to make the rows taller? I see I can change the column width and even hide them, but I can't figure out how to do the same thing w/ rows.
3.Lastly (I hope) is there a way to make the buttons bigger, that is the buttons on the bottom of the main form that allow the user to move from one record to the next.

[Arineckaig]

1. Is there a way to hide the others and only show the selected invoice?

There are several ways. One route makes use of the relationship between a data form and its sub-form, another route would be to use a filter from a navigation form control or the form navigation toolbar. You may get some pointers from a fairly extensive tutorial that can be downloaded from https://www.dropbox.com/s/1korf1mjp5kxv9r/FilterExamples.zip?dl=0

2. Is there anyway to make the rows taller? I see I can change the column width and even hide them, but I can't figure out how to do the same thing w/ rows.

It is not possible to change just the height of rows in a table form control, but it is possible to change the zoom level of the whole form document which will increase the displayed height of the rows: right click the bottom right hand corner of the status bar of the form document. The size and format of controls other than the table control can then be adjusted as desired.

3.Lastly (I hope) is there a way to make the buttons bigger, that is the buttons on the bottom of the main form that allow the user to move from one record to the next.

The form navigation toolbar tends to be inflexible. On the other hand a navigation form control included in any data form or sub-form and the navigation bar at the bottom of a table form control are more flexible and can also be varied with the zoom of the form document.

[dazedandconfused]

Is there a way to set the default date as "today's date?" that is the date it is when I create the entry?

[dazedandconfused]

1. Is there a way to hide the others and only show the selected invoice?

There are several ways. One route makes use of the relationship between a data form and its sub-form, another route would be to use a filter from a navigation form control or the form navigation toolbar. You may get some pointers from a fairly extensive tutorial that can be downloaded from http://dl.dropbox.com/u/10552709/FilterExamples.zip

2. Is there anyway to make the rows taller? I see I can change the column width and even hide them, but I can't figure out how to do the same thing w/ rows.

It is not possible to change just the height of rows in a table form control, but it is possible to change the zoom level of the whole form document which will increase the displayed height of the rows: right click the bottom right hand corner of the status bar of the form document. The size and format of controls other than the table control can then be adjusted as desired.

3.Lastly (I hope) is there a way to make the buttons bigger, that is the buttons on the bottom of the main form that allow the user to move from one record to the next.

The form navigation toolbar tends to be inflexible. On the other hand a navigation form control included in any data form or sub-form and the navigation bar at the bottom of a table form control are more flexible and can also be varied with the zoom of the form document.

Thank you. I'll check that out tomorrow

[F3K Total]

Hi,

Is there a way to set the default date as "today's date?" that is the date it is when I create the entry?

Yes:

Code: Select all   Expand viewCollapse view

ALTER TABLE "MyTable" ALTER COLUMN "MyDate" SET DEFAULT CURRENT_DATE

R

[dazedandconfused]

Hi,
you can do it, as you showed it, single or additional, or a mixture, e.g a table control for the items and single controls for the invoices.
Find the mixed version attached in Form Form_PO_invoice2.
In my point of view, the overview is worse.

... since I can't seem to figure out how to change anything to a combo box or list box...

Right-click the control or within a table control the column header an choose Replace with > and select the control Type you like.
Learn, how to use the Form Navigator in Forms-Edit-Mode.
R

After all this work and after getting everything nice and neat my boss decided he wants the 3d look on top. With the way you had form1 it was easy to insert a column and make it a list box, combo or whatever and it would be tied into the main form easily.

Now customer will be a drop down list box and I'll pull the name from my customer list table. I'll add a text box on top of that but now how do I connect both of those to the main form so that when I move from one invoice to the next the customer box moves along with it's corresponding invoice? It wasn't originally tied to the main form, instead it was a list box added later and it's "out in space" I tried dragging and dropping it onto the main form in navigator but that didn't work. I thought I could anchor it somehow to a form but I don't have that option

[dazedandconfused]

Hi,
you can do it, as you showed it, single or additional, or a mixture, e.g a table control for the items and single controls for the invoices.
Find the mixed version attached in Form Form_PO_invoice2.
In my point of view, the overview is worse.

... since I can't seem to figure out how to change anything to a combo box or list box...

Right-click the control or within a table control the column header an choose Replace with > and select the control Type you like.
Learn, how to use the Form Navigator in Forms-Edit-Mode.
R

This doesn't work for me in the 3d mode. I used the wizard to create a form and sub form. The main form I used is the second 3d selection in the wizard and the sub form is the last choice. I have 7 different fields ranging from date, fk_id_customer to contact info, po, etc... In the first form example you gave me I replaced FK_ID customer with a list box easy enough. Right click and then replace with list box. However, in the 3d form it's simply not an option. If I right click I only have the options: position and size, description, name, control, form, arrange, alignment, wrap, anchor, group, cut, copy. I can't "replace with"

If I add a list box control then I have the option of replacing that field with other controls by right clicking then chosing "replace with". However, if I select date, fk_id, contact info, po, etc I don't have the option to replace it.

So my next choice was to add a list box called customer which is a dropdown linking my customer table. I used the other suggestion of adding a navigation bar. If I do that everything in the main form and subform move, however the customer listbox never changes. So I'm guessing I need to anchor it somehow to the main form?

[F3K Total]

Hi,
controls created by the wizzard have grouped label- and controlfields. Rightclick -> ungroup or rightclick -> enter group helps.
R

[dazedandconfused]

Hi,
controls created by the wizzard have grouped label- and controlfields. Rightclick -> ungroup or rightclick -> enter group helps.
R

I'm not following. I have the option to edit group or ungroup. I'm not sure what edit group does because when I select it nothing happens, no windows pop up. Are you saying I need to ungroup each individual control field and then regroup them somehow? Or is there a way to add the list box to the group?

Here's what I did. I dragged my customer list box into the group of 10 controlfields created by the wizard. I also added a navigation bar. Then I highlighted that entire selection and selected ungroup. Next I highlighted the selection again and selected group, but that didn't work. The list box still doesn't change but you can see In the screen shot hat when I click on the main form it includes the list box and navigation bar. The navigation bar works nicely, however it doesn't change the list box

[Villeroy]

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).