How to complete fields with data in fields in another table

steven.emt · December 16, 2019, 3:51pm

Two Tables: Customers (billing address, shipping address); Orders (product detail, shipping address)

How do you use a macro to get the default shipping address from the customer table and populate the shipping address for the order with the default data.

Edit:
LO Version 6.1
OS Version: Windows 7, OS X 10.14.5
MySQL: 8.0.16

My background is in sql administration and Visual Studio; new to the mysql world.

Thanks in advance…

Ratslinger · December 17, 2019, 12:05am

Hello,

Unfortunately your question is missing information. What OS, specific LO version used, Database used and a better process description.

Please take a moment to review the following.

On the main page after you sign in, there, in the right hand column, is a section called Resources. Please read How to use the Ask site. It’s not all encompassing but will provide basic information to ask a bit better question. Also of help is this post → Guidelines for asking

There are also a few other links at the bottom of the page - FAQ & Help which are often overlooked and contain other information.

Also don’t know your background with writing macros. And it appears there is no real need. Simply use SQL to join the shipping address with the order.

Ratslinger · December 17, 2019, 12:55am

Please either edit the original question or use add a comment for the additional information. Do not use an answer.

steven.emt · December 17, 2019, 1:34pm

Thank you Ratslinger - I don’t need to display the default shipping address - some orders might be shipped to a different address. I am just wanting to populate the address, city, state, postal code fields in the order shipping details with the default data and then allow the user overwrite them if needed to ship to an address that is NOT the default shipping address.

Ratslinger · December 17, 2019, 4:13pm

Will attempt later today to provide a sample. If you have no LO macro experience this is going to be difficult at best since the macros need to be specific to your forms and tables. You will need to apply principles from the sample.

This is also harder since you are using MySQL and probably cannot provide a sample of both the Base file and the MySQL data structures. This is what is really needed.

Still believe SQL is the better answer. It would appear you need to output the Order at some point to a paper document. If Order ship to is empty then use default.

Ratslinger · December 18, 2019, 2:43am

Hello,

There are many ways to do this and much depends upon the form and tables. Have included a Firebird embedded sample which demonstrates a simple macro to copy a single field. Here is the macro in the file:

Option Explicit
Sub UpdateShippingAddress
    Dim oForm          As Object
    Dim oSubForm       As Object
    Dim oOrderShip     As Object
    Dim sShipAddress   As String
Rem Get Main Form
	oForm = ThisComponent.Drawpage.Forms.getByName("MainForm")
Rem Get text from Main Form shipping textbox
    sShipAddress = oForm.getByName ("txtship_addr").text
Rem Get the SubForm
    oSubForm = oForm.getByName("SubForm")
Rem Get the shop to text box
    oOrderShip = oSubForm.getByName("txtship_to")
Rem Apply the retrieved shipping address from the customer record
    oOrderShip.text = sShipAddress
Rem Set data to be updated
    oOrderShip.commit()
End Sub

The macro is executed by a push button on the form.

In addition have included a query which pulls the either the Order ship to or Customer ship to - first non Null (Coalesce). My point again is there is no need for a macro unless you have other non-disclosed information.

Sample ----- ShippingAddress.odb