Libreoffice base clone rows to other table

[Chris-H]

Hi,
I have been working on a simple database to write some invoices for my own business using libreoffice W10, LO7.0 and HSQLDB split database. I built this from bits and pieces early last year during lockdown, by learning some SQL basics and a lot of help of the people here, thank you all.

As I move forward and get more orders I need to write various forms, like orders and invoices etc…

I know the question is trivial but is there a way to write say an order (Invoice, items sold) and then later, then “copy” this order into an invoice which basically would mean to sort of clone data from one table to another while creating a new ID in the invoice table.

I have zero clue in creating macro codes etc… so if somebody has an idea that could make this possible.

Thanks much
Chris-H

As my skills are more than limited, I wondred if somebody had an advice or if

[Ratslinger]

Hello,

It may be the structure of your database that is not correct. There should be no reason for copying data from one table to another.

Would think this is a usable structure - each a table and relating to the one above it:

Customers
    Invoices
        Items Ordered

This is certainly general in structure but you can see that you simply select a Customer, create a new invoice and then enter the order. No need for duplication.

If this is not of help, please provide further details on your situation and possibly a sample Base file showing this.

Edit:

After a bit more consideration of the question, if this is a matter of re-ordering the same items as a previous order in the same quantities, then a macro would be required. This requires details of tables, fields and procedures. Consideration also must be given for prices - how far in the future is this? Most likely that many other questions would surface.

[Chris-H]

Ah thank you Ratslinger for you answer. You just made me aware that I did not explain my idea very well at all. Basically, what I want is to write say an order confirmation which is one one table and then turn that order confirmation into an invoice which would be on another table. Reason why I want to do that is that I have a stock that is tyed in with that. So the idea is to write an order confirmation but the stock would not be affected. Then later once the order is confirmed, it shoudl become an invoice which would deduct an item form stock. I hope that the idea is better explained now. Yes, I also came to the conclusion that a macro would be the only solution but this iwell over my knowledge. Is there anything similar you have dealt with in the past that could be of help?

[Ratslinger]

@Chris-H,

Maybe not knowing very much about you overall processing is a hindrance but seems this is a simple indicator in the record in the “Invoice” (“Order” better?) table. This indicator on/off as to ‘order confirmation’ or ‘Invoice’.

[Chris-H]

Thanks for sharing this idea Ratslinger, so you mean like adding an On/off button on each row? Would this button disable that specific row? it would still raise the concern that some ID numbers (invoice numbers) would be missing and here in France invoice numbers have to be following each other AND their dates must be the same (according to my accountant). Actually I want to use this separate table more for backorders, meaning items that are on order but not yet available. Once items available I want to clone this backorder into the invoice table and sold items table. But I guess that this is very complicated to put together and will require serious macro codes. I can send an example if needed.

[Ratslinger]

@Chris-H,

That is where some problems arise. I am certainly not in a position to give advice when there are legal or procedural requirements required by local law I have no idea of.

The problem with macros and you not knowing how to code them is that when (not if) you need to repair (have had many LO bugs stop me from using code and needing work around) you will be placed in a difficult position. Either the bug needs to be fixed (some I have had took years and some after that time are still open) or you need to rely on someone to fix them for you. Either way you have a problem with your business procedure.

You can post an example and I can look and give you code based upon your request but I still believe with proper design (probably would mean to start over completely) you could do something without macros.

[Chris-H]

Thanks much Ratslinger, that pretty much answers my question and I will not want add any more macro codes, I use one for print and that is it, I prefer to keep things as they are as it seems to be working for now. Again,many thanks for sharing your wisdom with us.