Calculating a value in a form

Creating and using forms
Post Reply
User avatar
johncraw
Posts: 18
Joined: Fri May 09, 2008 9:47 pm
Location: Ontario, Canada

Calculating a value in a form

Post by johncraw »

I have just started working with Base, so this is, I think a simple problem. I just hope there is a simple solution.
I have a database with a number of tables. The first problem was that I could not create relational tables with keys, without using the table wizard. That is how I did it finally, but it was a bit weird.
I finally got the tables, and created a form. All the data is correct, and the listboxes work fine. The problem is that I have 3 fields Gross, Tare and Net weights. When the gross and Tare values are entered, I want the Net weight to be automatically calculated. I created a macro and have tried various possibilities:
Net = Gross - Tare (There are the table field values)
DataEntry.fmtNet = DataEntry.fmtGross - DataEntry.fmtTare
(DataEntry is the form name and the fmtNet, fmtGross and fmtTare are the form field names)
DataEntry.fmtNet.value = DataEntry.fmtGross.value - DataEntry.fmtTare.value (another variation)
I either get an error, or the macro is ignored. How should I correctly update this value?
Any help or direction would be appreciated
Thanks in advance
John Crawford
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Calculating a value in a form

Post by Villeroy »

Try the form in the attached database. To see how it is constructed, open the form in edit mode and call the "Forms Navigator" from toolbar "Form Design".
I'd suggest that you fix your tables and relations *before* you create any queries and forms, since everything depends on working tables and relations and changes in tables and relations require tedious changes in queries, forms and reports.

Link to tutorial with this topic's example document: viewtopic.php?f=83&t=26328
Last edited by Villeroy on Sat Apr 06, 2019 3:52 pm, edited 5 times in total.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
User avatar
johncraw
Posts: 18
Joined: Fri May 09, 2008 9:47 pm
Location: Ontario, Canada

Re: Calculating a value in a form

Post by johncraw »

Thank you. I will look into the example.
I got all the relationships working. That is the first thing I did. I just found it a bit convoluted, since I could not create the key when I opened a new table in design mode.
John
User avatar
johncraw
Posts: 18
Joined: Fri May 09, 2008 9:47 pm
Location: Ontario, Canada

Re: Calculating a value in a form

Post by johncraw »

I looked at the Cuboids, and it does the calculation when the focus moves from record to record. If that is the best I can get then I guess that will have to do. I was hoping for a dynamic update.
I created a query and it works. However in looking at Cuboids, the query is added to the form. Now, this is where my lack of knowledge kicks in. When I open the form in Cuboids or my database, the Form toolbar item is greyed out. If I select one of the fields the Form toolbar item is enabled. I do not know why that is the case, and cannot find any explanation.
Now although my query works when I select it, it still does not work in the form. When I select the Volume field in Cuboids, and select the Form toolbar item, there are two properties (Link Master Fields and Link Slave Fields). The other fields in the form do not show these properties? Shouldn't the Form properties be the same regardless of field? How do I get these two properties to show up on my form?
Another question. Is there a way to call a query from a macro in OpenOffice.org Basic? Then I can call the update when I need it.
Sorry to be asking such basic questions, but nothing that I have found so far seems to be able to explain what I need.
Thanks
John Crawford
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Calculating a value in a form

Post by Villeroy »

Get the form navigator, select "MainForm" and show it's properties on tab "Data"
"MainForm" is bound to "Table1" with all it's fields. The form is editable since primary key "ID" is included in the form's datasource. The representation of the ID field consists of a text field, grouped together with a label. You are free to remove the box and label. "ID" is part of the form anyway and the form remains editable. All the other fields are represented as text boxes and associated labels as well. Nothing forces you to represent all the fields in the form or any field at all. There would be still a form, bound to "Table1" in a writable manner.
Apart from the 5 pairs of labels and boxes, there is a subform named "SubForm". It is bound to query "Query1" which is just a synonym for

Code: Select all

SELECT "ID", "Height" * "Length" * "Width" AS "Volume" FROM "Table1"
"Query1" also includes the "ID" field of the very same table, so each "Query1"."ID" points to the same entity as "Table1"."ID". The subform is bound to the main form through this common "ID" field. The second field of "Query1" is calculated by "Height" * "Length" * "Width" and I named it "Volume". "Volume" is represented as a white textbox with associated label. I left off the querie's "ID" field here. It's content would be exactly the same as the "ID" of the main form. The subform is not writable since you can not manipulate the result of the calculation.

One way to add subforms is via navigator>main form's context-menu>New>Form
Controls can be drawn onto the Writer document and you can use the "add field" button from tolbar "Form Design". If a control gets added to the wrong form or subform respectively you can fix it by dragging around on the navigator.

The following represents my more or less speculative view on databses and forms.
When you change the current recordset, you put a cursor (finger, pencil,...) onto a certain entity which gets loaded from disk into memory. This movement is propagated to subforms as well to represent the current state of the stored recordset. When you edit the form controls nothing happens with the underlying database. You commit changes by switching to another recordset or hitting some commit-button (think of the "Submit" of this forum's software. Just another frontend to another database).
You can add a refresh-button to the subform: Draw a button associated to the subform and set property "Action" to "Refresh". This will refresh the subform. Obviously it triggers the submission of the main form as well.
 Edit: Replace the above attachment with a new one, including two buttons for saving the main form record to disk and refreshing the subform. A macro could unify both actions. 
OK, that must suffice for now. Every time I do something with Base I find a bug. Today I learned that Base goes into some kind of limbo when you try to load a form which is already opened in edit-mode.
Last edited by Villeroy on Sat May 31, 2008 10:21 am, edited 1 time in total.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
User avatar
johncraw
Posts: 18
Joined: Fri May 09, 2008 9:47 pm
Location: Ontario, Canada

Re: Calculating a value in a form

Post by johncraw »

Thanks again. I think I understand. I will look into it more deeply.
I am also pursuing doing it in Basic as a macro. Since all of the fields are named (fmtGross, fmtTare, and fmtNet) if I can find the items on the form, then I can do the calculation and enter the value in the fmtNet field. I am looking at a number of examples and trying to understand which structure will give me the values of the fields, and how to access them.
The hunt goes on!
John
User avatar
NiksaVel
Posts: 91
Joined: Fri Jan 25, 2008 12:00 pm
Location: Croatia

Re: Calculating a value in a form

Post by NiksaVel »

very helpful to me as well.... thanks!
OOo 3.1.X on Ubuntu 8.x + Linux Mint 7, Win XP, Vista
User avatar
johncraw
Posts: 18
Joined: Fri May 09, 2008 9:47 pm
Location: Ontario, Canada

Re: Calculating a value in a form

Post by johncraw »

I am starting to try to understand how to access the controls. There is a lot of, to me, poorly documented information on how the macros access and work with forms.
I found this entity/container? called ThisComponent. When I look at Help for this item I get "Addresses the active component so that its properties can be read and set." This tells me nothing. What are the properties and methods. I put this into a search on OpenOffice.org, and find nothing that I can use. The only two documents that I can see are both written in languages that I do not understand.
So I found a couple of examples about ThisComponent. I can find the following properties:
DrawPage() - This is the current page.
DrawPage().getCount() - This is the count of the shapes(controls?) on the page.
DrawPage().getByIndex(val) - This returns a shape(control?)
I tried to use .getByName("Name") - but that property was not valid.

It appears that the shape can be more than one thing and you are to use '.supportsService(value)' to determine the type of shape.
If I have a shape I can then get the name of the shape
DrawPage().getByIndex(val).control.name
I tried to use control.value, hoping to get the value of the control, but that also was not valid.
I think that if I can get/set the value of a control I can accomplish what I need to do. So this is the question:
Where can I find the properties/methods for the ThisComponent item.
I do not know if this is the best way to access the items, but it is the only one that I have found enough information to be able to at least try.
Thanks in advance for any direction.
John Crawford
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Calculating a value in a form

Post by Villeroy »

Personally I avoid macros like disease. Mostly because they do not save the time you invest in them. Base does not even provide a proper integration for macros. You can store them in forms and reports like any other Writer macro, but then you have to lower macro security or turn off all macro security to get rid of security warnings. Storing everything in a public container results in countless routines, specific to distinct databases and macros have to be installed before using the database.
What do you want to program when you do not know yet how the application works?
If you want to stay with Basic (the only advantage would be it's easy integration into documents) then you definitively need to install the Xray tool together with the SDK.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
User avatar
johncraw
Posts: 18
Joined: Fri May 09, 2008 9:47 pm
Location: Ontario, Canada

Re: Calculating a value in a form

Post by johncraw »

Your points are valid, and I do not necessarily disagree with them. OpenOffice has it's limitations as do most other systems. The app that I am writing started out as a Calc sppreadsheet, and probably should have stayed there. I thought, however, that a database would be more appropriate. I could simulate most of the functionality in Calc, but coming from programming Access and Approach, I figured "what the heck"
It has been a learning experience. But learn I did.
The following is a solution that works for what I need. Originally the Net Weight was to be stored, but I can calculate it when needed for reports or for data entry.

Code: Select all

Sub GetNetWeight
  Dim oThisPage
  Dim oShape
  Dim i as integer
  Dim sControlShape
  dim oName as string 
  dim Gweight as single
  dim Tweight as single
  dim Nweight as single

  'I don't know anything about this
  sControlShape = "com.sun.star.drawing.ControlShape"
  
  'Get the current page (seems to work)
  oThisPage = ThisComponent.getDrawPage()

  'Loop through all the shapes
  'The event "Text modified" for fmtGross and fmtTare call this routine
  'Also the event "After record change" on the form also calls this routine.
  'In this way when a new record is displayed it will show the correct value.  
  For i = 0 To oThisPage.getCount() - 1
    'Get this shape. (I would rather try to get the name , but it would not work)
    oShape = oThispage.getByIndex(i)
    
    'Save the name of the control
    oName = oShape.control.name

    'Seems to be needed to get a single, as opposed to a grouping
    'I have no groups on the page, so I do not have to worry about them    
    If oShape.supportsService(sControlShape) Then
      'oShape contains the shape control
      
      'If it is the Gross weight then save the weight
      if oName = "fmtGross" then
        Gweight = oShape.control.text
      end if 
      
      'If it is the Tare weight then save it
      if oName = "fmtTare" then
        Tweight = oShape.control.text
      end if 
      
      'If it is the Net weight then calculate the net weight
      'and place it in the Net weight box
      if oName = "fmtNet" then
        Nweight = Gweight - Tweight		'calculate
        oShape.control.text = Nweight	'display it
      end if
    End If
  Next
End Sub

Since I will not be doing much development in OO, I did not want to have to get the SDK, for such a small requirement.
John
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Calculating a value in a form

Post by Villeroy »

You can add a refresh button to a subform and push that whenever you want to see the result without changing the record. If you want to save this click, you may try to refresh the subform through some event exactly like a refresh-button would do.

Code: Select all

REM untested pseudo-code in Basic
Sub textbox_changed(oEvent)
' the calling thingy:
oSource = oEvent.Source
' the form
oForm = oSource.getParent()
' the subform
oSub = oForm.getByName("SubForm")
oSub.refresh() 'oSub.recordset.refresh(?), update(?) something like that
'there could be a loop for all subforms to work with any form, regardless of subforms' names
End Sub
Since I will not be doing much development in OO, I did not want to have to get the SDK, for such a small requirement.
The SDK is needed to bind compiled languages (Java, C++). So you don't need the SDK as such. However, it includes the full reference and the xray tool can use the reference of a local copy of the SDK. Xray saves many hours of work.

Code: Select all

GlobalScope.BasicLibraries.loadLibrary("XrayTool")
xray obj
shows you all the methods and properties obj. It includes a help button which loads the according html-documentation of a selected thingy into your favourite browser.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Post Reply