[Solved] Help adding a simple formula to a form

[dazedandconfused]

I have a form called PO invoice. The main form consists of date, po, invoice and customer. The subform has the part number, type, quantity and price for each individual PO. Thanks to the help on these forums it "only" took a week to figure that out, but prior to that I was trying for longer than I want to admit publically! I've watched lectures on youtube and read a book on base, but it's still very new to me. I want to add another column for "order amount" which would be something like SUM (quantity row 1 x price row1 + quantity row 2x price row 2+ quantity row 3 x price row 3)...and so on depending on how many rows of parts I have. Some PO's have just one part number and others have dozens of part numbers. What is the easiest way to do this? I saw some examples that mentioned creating a form in calc then linking that but isn't there an easier way? Right now "order amount" is a formatted field if that matters at all.

[KRC254]

DAZEDANDCONFUSED,

I am a newby too, but have found calculations need to be done in Query. A column in query headed with something like ("quantity row 1" * "Price row 1")+("quantity row 2" * "Price row 2") etc. is what you need. Add the column headings to the original table call it, say, TOTAL. Base the Query on the original table and add in the 'TOTAL' field. Write over the heading (TABLE) in query with the formula using the precise names of the fields you want in the calculation. Base the form on the Query.

[dazedandconfused]

Thanks for taking the time to respond. So you're saying, if I understand correctly, to add another column called 'TOTAL' then in there select the data tab and change it to a query. Next in "list content" type in the formula?

I need help w/ the formula. How do I find out the precise names of the fields I want in the calculation? They are quantity and price and they are located right next to 'TOTAL', but I don't know how to enter the text so the OO understands it.

[Arineckaig]

The subform has the part number, type, quantity and price for each individual PO

No need for Calc but for a query to supply a calculated field to the sub-form. For example, use a query as the data source for the sub-form. This query could be:

Code: Select all

SELECT *, "quantity"*"price" as "TOTAL" FROM "tableName"

Replace tableName with the actual name of the table that holds the data to be supplied to the the rows in the sub-form [also replace "quantity" and "price" if these are not the correct field names in your source table]. . Then add a field (or column) to the sub-form bound to the "TOTAL" filed in the query.

[dazedandconfused]

The subform has the part number, type, quantity and price for each individual PO

No need for Calc but for a query to supply a calculated field to the sub-form. For example, use a query as the data source for the sub-form. This query could be:

Code: Select all

SELECT *, "quantity"*"price" as "TOTAL" FROM "tableName"

Replace tableName with the actual name of the table that holds the data to be supplied to the the rows in the sub-form [also replace "quantity" and "price" if these are not the correct field names in your source table]. . Then add a field (or column) to the sub-form bound to the "TOTAL" filed in the query.

I tried but it didn't work. I attached the database so you can see what I did.

[Arineckaig]

Having now seen your database I am not sure if you are looking for a total column in each line of the PO_ITEMS subform table or a grand total for the sum of all the lines.

To produce a total for each line you will need to add a calculated field to the data source for the PO_ITEMS subform - in place of the Table “PO ITEMS” I would suggest using as the data source the following SQL:

Code: Select all

SELECT "PO ITEMS".*, 
( "PO ITEMS"."Quantity" * "PO ITEMS"."Order Price" ) AS "Item Total" 
FROM "PO ITEMS"

It is then possible to add a further column to the subform grid/table to display these line totals in a formatted field column that displays the result in USD.

To display a Grand Total of all the lines in the subform it is necessary to add to it a yet lower generation subform for which the data source will again be SQL as an aggregate query:

Code: Select all

SELECT "FK_ID_PO", SUM( "Quantity" * "Order Price" ) AS "Grand Total" FROM "PO ITEMS" GROUP BY "FK_ID_PO"

The two linking fields for this sub-subform and its immediate parent subform should be their respective "FK_ID_PO" fields. The effect is to filter the grand total sum to include only those lines relevant to the overall PO Invoice. The single output of this aggregate query is displayed in a formatted field “Grand Total”

As an example is probably more helpful than a description, you will find these suggestions included in the attached revised .odb file. Incidentally you will see I have reduced the size of your file without loss of data by running from the menu bar Tools>SQL and entering and executing 'SHUTDOWN COMPACT'.

Later Edit: Apologies for uploading the wrong database. Hopefully the correct one is now attached.

[dazedandconfused]

I added another sub form to keep track of when we send literature. I was able to relate it to the main form by a Fk_Id so when I type the job name in the main form it is automatically entered in the Drawings and Data form. Now I have the error in the screen shot attached when trying to save the data. I can enter the dates, but once I click on the row below it I get the error or if I move w/ the form navigator it's not saved.

Ideally I'd like to have the data save whenever I press the enter button or tab, but open office seems to only save the data I enter if I click on the row below where I entered data and that's been consistent w/ all 3 forms.

I'm getting a second error. I select a product type, then click on another cell to enter data and it says error writing data to database. Input required in field "Item Total." Please enter a value. In every other instance it multiplies the order price by quantity except when I enter a new record

[Arineckaig]

....it says error writing data to database. Input required in field "Item Total." Please enter a value. In every other instance it multiplies the order price by quantity except when I enter a new record

Try setting the "Input required" property of the formatted "Item Total" column in the sub-form to 'No'

[dazedandconfused]

Indeed this solved the first problem. Thank you. Is there any way to change open office so that if I tab or if I press enter the data that I just inputted is saved? Right now the only way it saves an entry is if I click on the row below the one where I entered data. In addition the calculation for "total" isn't done until I click on the row below where I'm entering data. Is there any way to change that or is it just something I have to live with?

I still have the second problem, however

[dazedandconfused]

Orig quote price, order price and premium price all round down to the nearest dollar. It will let me input $1245.99 for example, but when I click on the row below it changes to $1245. OK so that's actually not rounding since it should say $1246...not sure what it's called... I verified the formatting for each and I have currency as USD selected, 2 decimal places. What do I need to change?

[MTP]

Forcing a record refresh can be done by adding a button with action: refresh form to a subform (it works also in the form you want to update, but generates an annoying pop-up to verify you want to save your new entry, so most examples show it in the subform). It can also be done "invisibly" with a macro.

When you say you verified the formatting, do you mean inside the form? You will also need to check the formatting of the underlying table. It's possible the table data is stored as INT, which doesn't recognize decimal places.

[dazedandconfused]

Forcing a record refresh can be done by adding a button with action: refresh form to a subform (it works also in the form you want to update, but generates an annoying pop-up to verify you want to save your new entry, so most examples show it in the subform). It can also be done "invisibly" with a macro.

When you say you verified the formatting, do you mean inside the form? You will also need to check the formatting of the underlying table. It's possible the table data is stored as INT, which doesn't recognize decimal places.

Thank you! I thought I double and tirple checked the table and made sure it was set to 2 decimal places. However, when I opened it to edit I see that the bottom where it says entry required, etc showed 0 decimal places. I clicked on the format example and changed it (again?) to 2. For some reason it didn't save on the main screen. Therefore I entered "2" in decimal places on the main screen and that saves now. Thanks again. I'm not good w/ macros at all. In fact if it wasn't for the help of the people here I'd be lost. Do you know where I can find out more about this one specifically?

[MTP]

For macro introduction, you might try Sheep Dog Guide's Macro use:Introduction page. DACM has posted a sample database of creating an invoice with macros.

[dazedandconfused]

For macro introduction, you might try Sheep Dog Guide's Macro use:Introduction page. DACM has posted a sample database of creating an invoice with macros.

Thanks.....after spending a couple days researching macros I think it's over my head right now. I see that somehow he has it where the record appears to be saved in his database but I have no idea what part of the code did that for him. It would probably take me months to understand it well enough to be able to write anything and I just need it to do one thing that you'd think wouldn't even need a macro. Also, the link to the database doesn't seem to specifically address the issue I had unless I missed something in the tutorial, but thanks for the help.

[dazedandconfused]

Arineckaig I'm having a couple problems with this copy. I was able to use your changes on my real database and they worked perfectly. The "Copy of database" I use for testing and I try everything there until it works then I duplicate it on my real database. This first problem really just confuses me and it's not a problem I have w/ my real database. I have a column called Total1 where the SQL is written. I have another I called Total2. The data displays under the Total2 column and not the Total1 column. Why is that?

Secondly I tried using the wizard to run a report and neither column, Total1 or Total2 shows up as a field I can select to show in the report. Why is that and is there a way to incorporate those columns in the report with the wizard? The same can be said if I run a query

Edit: Since the original problem "Help adding a simple formula to a form" was solved and I got off on a tangent, should I just start a new thread w/ the other problems and mark this one solved?

[Arineckaig]

I got off on a tangent, should I just start a new thread w/ the other problems and mark this one solved?

That would be an excellent idea and would be much appreciated. It would comply with the advice of the "Survival Guide for the Forum" to be found at http://forum.openoffice.org/en/forum/vi ... ?f=5&t=166

[dazedandconfused]

I want to add another box that gives the summation of COG. I already have:

Code: Select all

SELECT "PO ITEMS".*, 
( "PO ITEMS"."Quantity" * "PO ITEMS"."Order Price" ) AS "Item Total" 
FROM "PO ITEMS"

How do I add a second one? Do I add it at the end of the string w/ a comma, for example? Would this be the code to simply add the COG column?

Code: Select all

SELECT "PO ITEMS".*, 
( "PO ITEMS"."COG") AS "Item Total" 
FROM "PO ITEMS"

[MTP]

The * will select all the columns in the table. The column "item total" needed to be called out separately for the query because it was not part of the table.
If you only want some of the columns to show in the query, you'd call out the columns you need and not use the * at all. For example, to show just four columns (quantity, price, COG, and item total):

Code: Select all

SELECT "PO ITEMS"."Quantity", 
  "PO ITEMS"."Order Price",
  "PO ITEMS"."COG",
  ("PO ITEMS"."Quantity" * "PO ITEMS"."Order Price") AS "Item Total"
    FROM "PO ITEMS"

[Arineckaig]

dazedandconfused:

I want to add another box that gives the summation of COG.

Although the formatted form control "COG FORMATTED FIELD" has been added to the sub-sub-form "Grand Total", the SQL which is the content source for that data form does not include a field to supply the data for this extra form control.

See my post above of Tue Oct 08, 2013 8:56 pm for why an aggregate query is required. Thus the SQL content source for the "Grand Total" data form should now be:

Code: Select all

SELECT "FK_ID_PO", Sum("Quantity" * "Order Price") AS "Grand Total", Sum("COG") AS "COG TOTAL" FROM "PO ITEMS" GROUP BY "FK_ID_PO"