I was not sure if code needs to be written for this, as I thought there would possible be a way in Base to do so. I defenitely do not ask for somebody to do the job for me. I thought maybe this has been done before and I could folow the analogy and learn from it. Worked for me in the past. Anyway it feels a little lke I am a burden here and do not want to be. Sorry for the trouble and thanks for your help.
Had mentioned multiple times that what you wanted needs macros (code) and even gave you links to examples.
We are here to help but it seems as if you are asking the same question over & over again and wanting someone to do this for you.
If I wanted you to right the code for me I would have not started with asking I have 2 listboxes and 1 table. I want the first listboxes choice to function as filter for the 2nd. I went through all the examples. Every time it concerned 2 different tables where the id’s have the function to retrieve the data.As you mentioned everytime I do not understand what you mean I tried to explain better. The suggestion to concatenate, Is how I started of in the beginning, but was not a solution.
I concatenated it in calc first. In calc by the way I manage quite well. Yes its true I am not very strong in the programming language used. I found visual basic a lot easier to handle and learn. Open source is relatively new to me. In calc via recording macro’s I learned it a little. i feel somewhat offended you mentioning I want you to write to code for me. I spent hours trying to figure this out on my own, watching you tube video’s on base, sql etc. nothing there. last thought was the forum.
I always hesitated, because first I wanted to do this myself and second because I thought this is only for people that now a on the subject. Well i guess my feeling was right and I will go trying to figure it out on my own.I time I will. You say you are here to help, but apperantly in this case you were not able. Except for teaching me to spell concatenate. So thank you for that.No hard feelings from my side and wih you all the best with the forum.
You did ask a question in your second comment which I should have answered:
If I for listbox2 could somehow refer to the selection made in listbox 1 (Shop_name), it would be a step in the right direction, no?
To be clear, have given you all the information you need but you seem to not be able to comprehend piecing it together.
If you want progressive list boxes your one table has two columns beside the key. This is just like the two table example. You can base the first list box on SQL select of Distinct Shop_Names. Then use this selected item as the basis for the second list box:
Select Shop_Place, Shop_ID from Table where Shop Name = selection from first list box
Obtaining the information and putting it together is where the macros come in - examples given. You do have the two necessary tables - just in the form of one.
The selection from the first list box is useless as far as saving the data in the record. The second list box selection has all you need
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.