Issues with forms and autofill in Base


It was suggested to me over on reddit/r/libre to post this here as you guys are wonderful and might be able to help :stuck_out_tongue:

Hi all, I have been trying to get this sorted for weeks now. Crunch time is upon me. If anyone is willing to help me complete this project that would be awesome.
I know most of you help each other just for the enjoyment of it but if someone wanted a few bucks to help me get this done i wouldn’t mind, just need to get it finished.
I have heard great sories about how good the libre community is and its why ive switched from openoffice and come here for help rather than there.
Any help is welcome even if its to tell me this simply isnt possible.

(drop box link to database) Dropbox - Error

the problem is you want to use a FirstName control serve as the link to a subform with customer information. Technically possible if you move the FirstName control to the MainForm and then change the SubForm properties. However, you can’t really identify your customers uniquely just by first name.

try instead using a ListBox on the MainForm that binds on the CustomerID but that displays the text from the name columns using CONCAT("LastName", "FirstName").

The use of a ListBox on a MainForm to anchor and change the contents of SubForm also may require strategic use of reload for the SubForm, whenever the name in the ListBox changes. That is, it would require use of macros.

I have done forms that use functionality like this, but it always winds up taking much more macro coding than anticipated.

There are multiple issues here.

1. Customer details.

For the customer details, the best practice would be to store only the primary key ID in the child table, in this case Customer ID. You have a form and subform here, which is great, you’re a lot of the way there. To finish you will need to create a CustomerID field in the MainForm, make this the link with the SubForm and then insert a macro to make it reload on changes:

i) create a new list box CustomerID field on the MainForm – this must not be on the SubForm because the recordset is different. To see which form is hosting the control, use the Form Navigator (image description) For useability, select the option Dropdown list in the control box for the list box.

ii) make the data source the field CustomerID field, which on the MainForm will be from table All Bookings New

iii) Make the list source for the List Box a query with the first column however you want the name to appear on your list, for example CONCAT(CONCAT("LastName", ', '), "FirstName"), and the second column CustomerID. Thus: SELECT CONCAT(CONCAT("LastName", ', '), "FirstName") AS "nm", "CustomerID" FROM Customers ORDER BY "nm" ASC

iv) Make the Bound Column in the control dialog 1 meaning it will update the data set with the value in the second column, CustomerID (zero being the first column). Now, when you return to the row, it will display the name in the list box.

This will not update LastName etc in the child table Bookings, nor is the best practice to do that. Instead, when you want to retrieve, you will JOIN the tables together on the CustomerID.

To make the form work the way you want, next fix the link the SubForm in a manner similar to the JOIN statement, which you already have sort of done. The relationship must be on the CustomerID field only, not also the name fields, as those are not going to be updated on both tables. In addition, the Control for the LastName etc on the SubForm must be a Text Box or else it will not display anything, because there is no list. Right mouse click and select Change To in edit mode.

When you do these things, the List Box will select a CustomerID somewhat invisibly and will display a name. The other Text Box controls will browse to the correct row on the Customer table SubForm

There is one last thing for this operation. When you change the ListBox it will not immediately recalculate the SubForm link. If you really, really want it to work this way, you will need to use a Macro and Reload() the form, which also will require saving the change and browsing back to the same row after Reload. That is, in the Changed event on the List Box control you would have a macro where CustomerListID is the new list:

Sub ReloadMainForm
  FormContainer = ThisComponent.DrawPage.Forms
  MainForm = FormContainer.GetByName("MainForm")
  CustIdCtrl = MainForm.getByName("CustomerIdList")

  RowNum = MainForm.Row
  CustIDCtrl.Commit()
  If MainForm.IsNew() Then
    MainForm.InsertRow()
    MainForm.Reload()
    MainForm.Last
  Else
    MainForm.UpdateRow()
    MainForm.Reload()
    MainForm.Absolute(RowNum)
  End If
  
End Sub

Create the macro by going to the main database window, Tools → Macros → Organize, create a new module on the current document, and then save that as a new sub, select it as an event in the form control dialog. That solves problem 1.

2. Date of service.

To set the date of service as the same as a previous record, you will need to access the desired value and update it to the desired field. There are two basic methods: using an unbound control to hold the value temporarily or using a SQL statement. I’ll assume you want the latter. Thus, the macro will be as follows on the event for the MainForm AfterRecordChange or something like that:

Sub SetDateSQL
  FormContainer = ThisComponent.DrawPage.Forms
  MainForm = FormContainer.GetByName("MainForm")
  DateCtrl = MainForm.getByName("datBookingDate")

  Conn = MainForm.ActiveConnection()
  SQL = Conn.CreateStatement()

  QueryStr = "SELECT YEAR(MAX(`BookingDate`)), MONTH(MAX(`BookingDate`)), DAY(MAX(`BookingDate`))  FROM `All Bookings New`"
  ResultSet = SQL.executeQuery(QueryStr)
  ResultSet.next

  MaxDateYr = ResultSet.getString(1)
  MaxDateMo = ResultSet.getString(2)
  MaxDateDay = ResultSet.getString(3)
  
  Dim adate As New com.sun.star.util.Date
  
  adate.year = MaxDateYr
  adate.month = MaxDateMo
  adate.day = MaxDateDay

  If IsEmpty(DateCtrl.Date) Then 
    DateCtrl.Date = adate
    DateCtrl.Commit()
  End If
  
End Sub

I realize the desired date may or may not be the MAX date – you see where the query goes and can adjust it as you desire. Instead you may want the row with the MAX invoice number and the date for that row-- use a subquery and WHERE statement to achieve that result.

3. Profits Box

Once again, this is not a best practice to store another value that can be calculated from two other recorded values. That said, easy to do. You will need to change the profit control to a unique name, which I have called fmtAmountSpentProfit in the example. I use the actual names of the other controls in the test database, although I observe that they are all bound to bad data source fields and those bindings would need to be corrected in order to save the data to the right place:

Sub CalcAmt
  FormContainer = ThisComponent.DrawPage.Forms
  MainForm = FormContainer.GetByName("MainForm")
  PdInCtrl = MainForm.getByName("fmtUnitPrice")
  PdOutCtrl = MainForm.getByName("fmtBillingRate")
  ExtrasCtrl = MainForm.getByName("fmtAmountSpent")
  ProfitCtrl = MainForm.getByName("fmtAmountSpentProfit")

  ProfitCtrl.Value = PdInCtrl.Value - PdOutCtrl.Value - ExtrasCtrl.Value
  ProfitCtrl.Commit()
  
End Sub

Run this sub from the Changed event on each of the controls, or create a button, or pick desired event.

4. Auto Number Invoices

The database already does that because of the setting for AutoNumber in the table.

Conclusion
Those are the questions I see. Feel free to donate to TDF if so moved.

P.S.: It seems like a bad idea to me to save unencrpted financial account information in the database, if that is indeed what I see.

EDIT: Added the .commit() method to the second two macros, which is necessary to make the calculated valued “stick” in the database.

EDIT 2: For the first macro, added IF logic to deal with the insert row. When you are on the insert row, the write to the database is a different function (InsertRow) and the new row number is just the last one. To deal with the aesthetics of the speed of the changes, you could make the changing controls .EnableVisible = False and then True at the end. Macros can get complicated…

(if this answers your question, please accept by clicking the check box (image description) to the left)

Thank you soooo much, iv only had time to test a few of your suggestions but they worked perfectly and I understood very clearly what you wrote. Ill contact you later if I manage to get everything done.

See edit abut the commit method added to the second two macros.

Your images are not clear enough and your text says absolutely nothing. If you want help ask a detailed question.

We can’t see what is in your mind!!

Sorry the image was for another platform that can zoom much eaiser than this website, if you click the image all should become alot clearer, but thanks for responding