I understand all about normalisation and not wishing to duplicate data but… I have a form with a subform. The subform basically list a load of item lines. Each item line is basically information about a product. I use a list box to get information about the product. Now this is where I’m getting stuck. I want to display various fields from the product table. At the moment using a list box I can get the product name but I have literally no idea how to display other data such as price description etc etc. Some data is just for displaying, description, for example. As it never changes and if it does it shouldn’t make a difference. However with such fields Like the price I want to store in the item line table. The price held on the product file is the default price and the user may wish to amend for special clients. Also the default price may change and in that case historic invoices would be wrong. I know I can get around both of these by having a product file based on date and for applying a discount I can subtract another field that could hold the discount but that still leaves the problem of displaying the field in the first place. If I need to write sql how do I refer to selected product id field? I am slightly above a novice with SQL. I can write simple statements and work out what statements are doing. I less so with macros although they doesn’t look too bad.
I’m using windows 10, LibreOfficeVersion: 7.0.5.2 (x64), HSQL
Hello,
Have a very similar example in this post → Copy field table in field of other table
The dificulties with macros is they need specific information - field names, table names, control names. This will vary greatly from one person/project to another so you need to modify accordingly. It does use the list box (how you dsplay this is up to you).
Any further help will require a sample (edit original question & post there) with no personal/confidential information. Also explain where you are having the problem.
Looking at the link and the description from the poster that looks exactly like what I am after. I’ll take a deep look to see if I’m right and I can work out how to apply it to my case.
Took me a) a while to get round to looking again at this and b) more so to understand what was going on and to test various situations but…
I have read the similar problem in the link and downloaded the example. This is exactly what I’m after although there are things that I’d like to do a slightly different way as explained why below.
I’ve read through the macro and understand what is going on. I understand the logic but not the naming conventions. It’s similar I guess to VBA in access and I’m sure it’s documented somewhere in the links I’ve already been given.
There are a few funny things going on. I can explain some but not others. I can fix some but not others. Below will hopefully make things clearer.
Changing the amount (UPET field) from that on the product file changes back to the original in certain circumstances. The first row on the first invoice will change if you change UPET then close the form and open it again. The original UPET value is displayed. TBC
Other rows that have had their UPET values changed will change back if you use up arrow to get onto the appropriate Product field (maybe tab as well). I think this is because the copy macro should be in the after updating event and not item status changed event. I’ve changed this and a quick test shows it’s OK.
The other things are
- If I click on the form. All the products for the 1st invoice displayed show productid in the product field and not label/price concatenation. If you click to next invoice and return to the previous invoice everything is OK. This was like this before I moved the copy macro event and persists after.
- My change to where the macro event takes place means that the cursor isn’t getting moved to (i think) column 2. Don’t know if this is a problem. Currently it will position on invoice ID. I don’t think this is a problem as I can hide that.
TBC
- This is a design ‘feature’ but I can’t add the same product to an invoice as you get a duplicate key (invoice sub has invoice id and products id as a key). Why would I want to? Well if I was going to charge standard price for the first x items say and then a discount for the subsequent y then it’s easiest to have 2 lines or more for the same product. Not a huge problem but not sure best way to deal with it.
Other questions are - It looks like product label and product price (PRO_LABEL and PRO_UPET)are concatenated together solely to get the price for displaying on UPET. This is fine but then the price is displayed on the invoice screen in the description, if you like, even if the price has changed. I can see this is being an issue so you mentioned doing this in sql. Is this within the macro itself? Is it like VBA where you build up your sql string and the (in VBA) run an sql command. Can’t quite remember but something like docmd.runsql stringname. Any good reads for writing sql in
Any good reads for writing sql in this environment?
3. How do you actually debug? I can set the break point but it never does, break that is.
Apologies for 4 comments. Would let me do it all in a oner.
@Rytee,
Please note that this is a question and answer site, not a forum. Multiple questions should be asked independently. Your original question was about copying fields. The link gave some indication as to how to do this. That sample was created based upon the OP’s request - that amounts changing back were part of the request. Your supposition was that it was incorrect but that is not true.
The list box control can display information in a variety of ways and store different information. this linked sample also uses the data to be used in another field.
You also start to get into items you want differently then sql then debugging.
Here is a link to the LO documentation to assist with some of these last requests → LibreOffice Base Guide
Will certainly help with any further questions regarding the original post but other items should be asked as new question(s).
Regarding SQL, for reference I have used this site → w3schools
However, a word of caution. SQL used in a database depends upon the database used (even with standards applied). Because HSQLDB embedded is old (v1.8) it is always best to refer to the database documentation. See → HSQLDB v1.8 User Guide
Edit:
You stated:
All the products for the 1st invoice displayed show productid in the product field and not label/price concatenation.
Don’t see this anytime.
OK take your point. Other forums I use do go off on tangents. I’m new to Base and the question were, obviously, to further my understanding with the help of experts like yourself. I wonder why mine is showing ID. I’ll have to have a look around to see if it’s anything to do with my set up. I used to get it in Access but that was just down to displaying the wrong column in the list box.
Anyway I have two questions!!
Is your version OK with the UPET with the macro being in item status changed event. What OS are you using.
Other forums I use do go off on tangents.
Based upon this it appears I was not clear enough before. This site (ask.libreoffice.org) is NOT a Forum. It is a question and answer site.
Mainly I use Ubuntu 20.04.x but for your comment went to Win 10 and tested. It did, upon first open, show ID in the field. Did not need to do anything but to select each drop down and all was OK. Closed & re-opened and no further trouble. Seems as some obscure bug.
Is your version OK with the UPET with the macro being in item status changed event.
The post works as the original OP wanted. Therefore it must be OK.