Thanks I will give it some more effort. Meanwhile as a quick solution, i want to conatenate in base. It works with this command:
SELECT CONCAT( “Promo_type”, “Promo_description” ), “Promo_id” FROM “Table_Promo_Items”
I can’t find the way to enter a dash for having some space between the 2 tables. In calc it is no problem, but in base using symbols like ‘’- " between comma’s does not work as it would refer to a table. The same with ’ - '. I looked at SQL commands and examples, but nothing works.
Hello,
Use:
SELECT "Promo_type" || ' - ' || "Promo_description" As "MyConcat", "Promo_id" FROM "Table_Promo_Items"Thanks tworks that gives me time to figure out the previous. Still working on last the tip you gave:
Select Shop_Place, Shop_ID from Table where Shop Name = selection from first list box
Will figure out. Thanks again
Do not know if i need to open another topic for this question.
I checked and the question has been asked, but in 2013 and the answer was no.
In the form I want to use to fill the promo_distribution_table, I would like calculate two fields and store result in the table as value. reason I would like to store it is because the price of items change over time and so would the value. I can not use the query, as the data does not come from the table but has been entered manually.
Formula => Quantity (entered manually) x price (from table) = Value (this needs to be stored)
I can do the math in a query and get a result for stored values, but can not to store and make the reference to the specific field value:
:
SELECT “Table_Supplier_Items”.“Article_price” * “Table_Promo_Distribution”.“Distribution_Quantity_1” AS “Distribution_Value_1”, “Table_Supplier_Items”.“Article_price”, “Table_Promo_Distribution”.“Distribution_Quantity_1” FROM “Table_Promo_Distribution”, "Table_Supplier_Items
I would like to show the value in form, so person entering it, is aware of the value given away.
The original question is answered and it appears new questions are straying. Any new questions should be asked as new ones.
At this point will give quick answer. Some databases allow computed fields - Firebird is one (embedded comes with LO). This can also be done with macros. This has always been frowned upon as there are typically other ways to handle a situation. You can do this with macros. See my answer with samples here → Base Form data uneditable.
Further questions on this topic should be on a new question.
Hello, just a quick one. Been kind a busy so my response is not so quick. Sorry for that. Thank you for the help so far With this example added, I start better to understand how to appraoch my problem. For sure I will come back with another question soon regarding this :-). Cheers
I am trying to grasp the macro from the example and reconstructed it to my needs.
Sub LoadList(oEvent)
Dim oForm As Object
Dim oShop_country As Object
Dim oShop_name As Object
Dim sSelectedItem As String
Dim sSQL As String
Dim bClearList As Boolean
oForm = ThisComponent.DrawPage.Forms.getByName("MainForm")
oShop_country = oForm.getByName("lb_oShop_country")
oShop_name = oForm.getByName("lb_oShop_name")
sSelectedItem = oShop_country.SelectedValue
bClearList = 0
If IsEmpty(sSelectedItem) Then sSelectedItem = 0: bClearList = 1
sSQL = "Select ""Shop_country"" || ' ' || ""Shop_name"", ""Shop_id"" FROM ""Table_Customer"" WHERE ""Shop_country"" = '" & sSelectedItem & "'"
oShop_name.ListSource = array(sSQL)
oShop_name.refresh()
If bClearList Then
While oShop_name.ItemCount > 0
oShop_name.removeItem(0)
wend
End If
End Subagain sorry for the mess. I can’t seem to get attachment, i see no paperclip symbol.
Anyway so from 1 table customer, I would like to select Shop_country in 1st box and Shop_name (or if possible the concatenate Shop_name+Shop_place) in the2nd box.
I can make the selection in each box, but when the macro runs, I first stopped me at the line with oShop_country, so I figured the reference to DIM oShop_country is the box as it is declared as an object not a string?
I decided to give the boxes the labelname: lb_oShop_country & lboShop_name
after running the macro it stops me at the line sSelectedItem. I do not know why.
Another question i have that in the example the query for the listbox Tint, the selected value Color is “Red”, I copied that and set it to Austria. So without macro I see only the Austrian shops. I assume that the in the macro the linestarting with sSQL can overrule the that query’s set value?
Code:
Sub LoadList(oEvent)
Dim oForm As Object
Dim oShop_country As Object
Dim oShop_name As Object
Dim sSelectedItem As String
Dim sSQL As String
Dim bClearList As Boolean
oForm = ThisComponent.DrawPage.Forms.getByName("MainForm")
oShop_country = oForm.getByName("lb_oShop_country")
oShop_name = oForm.getByName("lb_oShop_name")
sSelectedItem = oShop_country.SelectedValue
bClearList = 0
If IsEmpty(sSelectedItem) Then sSelectedItem = 0: bClearList = 1
sSQL = "Select ""Shop_country"" || ' ' || ""Shop_name"", ""Shop_id"" FROM ""Table_Customer"" WHERE ""Shop_country"" = '" & sSelectedItem & "'"
oShop_name.ListSource = array(sSQL)
oShop_name.refresh()
If bClearList Then
While oShop_name.ItemCount > 0
oShop_name.removeItem(0)
wend
End If
End SubPaperclip icon to add a file is in the toolbar for questions (edit your question). It is not available in a comment.
@grexup stated:
Another question i have that in the example the query for the listbox Tint
Please read all of other post (latest sample) this was already posted there.
You are getting stopped in the code because something is incorrect with your naming. You should have reason to change something. The latest linked answer where you got this code has another link to the Base documentation.
Hello, I got it working :-). Indeed I made here an incorrect naming, this was the problem.This is a relief.
Thanks again for your patience and help. I am going to try to get the concatenate list working, hopefully it will work.
Hello again,
Just 1 issue I still have is that when making selections and moving on, the data selected in first listbox dissapears. How can i avoid this and keep the data in the cell?
Below the macro I use for this, but as it happens later in the process, I wonder if this is macro-related
Sub LoadList_Customer(oEvent)
Dim oForm As Object
Dim oShop_country As Object
Dim oShop_name As Object
Dim sSelectedItem As String
Dim sSQL As String
Dim bClearList As Boolean
oForm = ThisComponent.DrawPage.Forms.getByName(“MainForm”)
oShop_country = oForm.getByName(“lb_CC1”)
oShop_name = oForm.getByName(“lb_CN1”)
sSelectedItem = oShop_country.SelectedValue
bClearList = 0
If IsEmpty(sSelectedItem) Then sSelectedItem = 0: bClearList = 1
sSQL = “Select ““Shop_name”” || ’ - ’ || ““Shop_place”” || ’ - ’ || ““Shop_adress””, ““Shop_id”” FROM ““Table_Customer”” WHERE ““Shop_country”” = '” & sSelectedItem & “’”
oShop_name.ListSource = array(sSQL)
oShop_name.refresh()
If bClearList Then
While oShop_name.ItemCount > 0
oShop_name.removeItem(0)
wend
End If
End Sub
List box data is tied to the record data - at least in the samples provided. When you move to a new record there is nothing in the selected record and therefore no data to display. If it is an existing record it should display what was previously entered.
Hello again,
Do not know if i should make another question for this, but it concerns the macro code used here. I got it all working thanks again. In the following line of the coding for the macro, i would like to add a sort of data in cell 2 before selecting as this gets overwriiten when selection needs to be altered. I tried adding ORDERBY at several spots, but I run into errors.
If IsEmpty(sSelectedItem) Then sSelectedItem = 0: bClearList = 1
sSQL = “Select ““Shop_name”” || ’ - ’ || ““Shop_place”” || ’ - ’ || ““Shop_adress””, ““Shop_id”” FROM ““Table_Customer”” WHERE ““Shop_country”” = '” & sSelectedItem & “’”
I tried things like:
sSQL = “Select ““Shop_name”” || ’ - ’ || ““Shop_place”” || ’ - ’ || ““Shop_adress””, ““Shop_id”” FROM ““Table_Customer”” WHERE ““Shop_country”” = '” & sSelectedItem & “’ ORDERBY ““Shop_name”” || ’ - ’ || ““Shop_place”” || ’ - ’ || ““Shop_adress”””
analog to sql in initial query in the second cell:
SELECT “Shop_name” || ’ - ’ || “Shop_place” || ’ - ’ || “Shop_adress”, “Shop_id” FROM “Table_Customer” WHERE “Shop_country” = ‘Austria’ ORDER BY ( “Shop_name” || ’ - ’ || “Shop_place” || ’ - ’ || “Shop_adress” ) ASC
What am I doing wrong?