I’m attempting to use a list box to populate fields in my main table from a related table. I can easily get ONE field to display, HOW do I display the other fields of a record. In this case all it is I have a device that requires batteries and I need to know How many, what type, and the unit cost. (from the battery table) to post into the retail table.
Libreoffice Base 7.3.7.2 - Linux Mint 21.2 Cinnamnon
Retail.odb (127.3 KB)
This is your original code:
SELECT "BatteryType",
"UnitCost",
"BatteryID"
FROM "Battery"
ORDER BY "BatteryType" ASC
There are 3 fields, but listbox only could show one field and will save a second filed depending on the number of bounded field.
SELECT "BatteryType"||' → '||"UnitCost" AS "field",
"BatteryID"
FROM "Battery"
ORDER BY "field" ASC
This will show 2 fields. The first concatenates the content of “BatteryType” and “UnitCost” together with an arrow. Content of “field” will be shown, content of “BatteryID” will be saved.
Might be you need to switch decimal separator for UnitCost depending on your country, also somthing like ‘€’…
Thanks, I shall try that and see how I get on.
That works in that it provides the result of the query in the listbox which, in itself, is elegant and useful.
Is there any method where I can place the result into a variable that I can manipulate using code?
As for the actual currency involved, it is AUD so nothing strange.
Don’t know what you mean with “manipulating using code”.
For AUD (and the internal decimal separator9 I would set the code like
"BatteryType"||' → '||"UnitCost"||' AUD' AS "field"
Hope you defined the field “UnitCost” as DECIMAL(10,2) or something wit 2 decimal places.
I need to multiply unit cost by qty of batteries per device. and possibly add a profit margin. That math is easy, If I can just extract the unit price from the related table. Yes, the Unit cost is defined as Decimal with 2 places.
Thanks for all your help.
Take a query for your form:
SELECT "a".*,
(SELECT "UnitCost" FROM "Battery" WHERE "BatteryID" = "a"."BatteryID") * "Qty" * 1.5 AS "BCost"
FROM "Product" AS "a"
Query could be used for input data. You won’t need BatteryCost in table “Product”. 1.5 might be the profit margin. Don’t know how high this should be…
That query worked only once! Now all it throws is an Error “Basic Syntax Error Expected Case”. I understand where Your thinking is, but cannot seem to work the formula.
Can you please explain where “a” comes from?
I attempted to substitute a for the source table name - that did not work, I then tried the destination witht he same result.
WHERE "BatteryID" = "a"."BatteryID"
Bcost is sounds like the result, but is not a Field in any of my tables or Forms - I’m guessing that You meant BatteryCost there, but even substituting that did not work.
@LSemmens : Take the query, save it as a new query. It couldn’t be it only woks once. What error appears.
“BCost” is an alias, because there is also “BatteryCost” in your table “Product”. I would delete “BatteryCost” in the table and name the result “BCost”.
Retail.odb (127.4 KB)
Thanks, that explains a lot more about SQL than what I had found previously. I’ll bookmark that site