Price calculation using other columns within form

[benh19]

I have a form with a grid control to administer the used parts and their total cost. I have a separate table with parttypes and a table with partprices.

1. The first column is a listbox dropdown menu displaying the partnames from table parttype,
2. the second column is used to enter the number of parts that were used.
3. The third column i want to use to display the partprice (corresponding to the parttype chosen in column one)
4. The fourth column should be used to enter the total cost (=number of parts used x partprice)

Can someone explain how i can create column 3 and 4?

I am using Libreoffice version 6.1.3.2.

(Response to Ratslinger)
The grid control is used to enter data into a table (this is what I meant by ‘administer’).
We will use an auto-increment column as primary key, but it won’t be included in the form control because this field will be populated by a default value (i.e. auto-increment).
Also we use a foreign key that refers to a maintenance-activity. I didn’t mention this primary and foreign key before because it didn’t seem relevant.

Column #3 should be populated by a SELECT that looks up the most recent price for the parttype selected in column #1 from table partprice.
It does not seem logical to make column #4 a generated-column (i think that’s what they call it in mysql), because ‘partprice’ and ‘number of parts used’ will never change after they have been entered into the table. It seems a waste to make the server calculate these values every time they are queried. Therefore I planned to use a normal column containing the value of the total cost.
(Currently we use it this way, but we calculate total cost by hand)

[Ratslinger]

Hello,

There seems to be information/thought missing from this process.

Stated:

I have a form with a grid control to administer the used parts and their total cost.

Is this a table? If so, not certain where the Key is. Not clear on the “Administer” part.

Typically you do not store information which can be determined from other information. Specifically #4 where you have number of parts X part price. This information can always be determined using SQL. However, if you must, the Firebird database does have the ability for computed field(s). Sample table definition(Firebird SQL):

CREATE TABLE SALARY_HISTORY
(
   EMP_NO              SMALLINT                  NOT NULL PRIMARY KEY,
   CHANGE_DATE         TIMESTAMP                 DEFAULT 'NOW' NOT NULL,
   UPDATER_ID          VARCHAR(20)               NOT NULL,
   OLD_SALARY          NUMERIC(10,2)             DEFAULT 0 NOT NULL,
   PERCENT_CHANGE      DOUBLE PRECISION          DEFAULT 0 NOT NULL,
   NEW_SALARY            COMPUTED BY (old_salary + old_salary * percent_change / 100)
);

Now item #3 is more difficult. To do as asked requires writing a macro. Another method is to have another table with linked records to this table with part types and prices. However what happens when the prices change? Even under the scenario you state, if going back to make a correction it can cause problems.

Might be able to provide better direction if there were some more information about the process. If so, edit the original question (and note edited section) or add a comment - Do not use an answer.

Edit 2019-05-09:

You seem to have some incorrect information on various points. Seems, based on latest edit of question, you are using MySQL. Computed fields (generated columns in MySQL) are similar to Firebird. However, they can be VIRTUAL or STORE. See → How To Use The MySQL Generated Columns. A STOREd column is not calculated each time a query is performed.

Also seems you want to SELECT data (price from part type) from the other table into current grid field. As stated in the answer you will need to create macro code to accomplish this. Macros in Base use specific names from your forms and database to retrieve and place data on the form/control. Because of this specific code cannot be given without the actual Base file or all the specifics. There are numerous examples on the Ask site with this type of macro code. Here are a few using SQL in macros:

Is it possible to output cell values directly to base or MYSQL with macro

Hi All I have a Calc spread sheet which i use for my invoicing, on that I have a "save"button that as well as saves the invoice to the appropriate folder it also opens a text file with the next invoice number with open for input to read the invoice number to apply’ it then updates the file with open for output and saves the new invoice number for next time. is it possible to use something like “open for output” to a base file or even better a MYSQL database as part of the save macro I am only…

Calc macro select two or more fields from MYSQL

Hi all, I have a macro in a Calc file that pulls info from a Base file which works well for me, however I am only pulling a single field from the query, all this does is pull a list of supplier from the base file and insert them into cells This is the code I am using, I havent included the database connection part of it as I have no problem with that Stmt = Conn.createStatement() strSQL1 = " SELECT Supplier_Name from Suppliers where Active = 'Active' " rem execute SQL statement;" oResult =…

You can also perform the calculation for the total in the same macro but, as mentioned above, can be done in a generated column.