We will be migrating from Ask to Discourse on the first week of August, read the details here

Ask Your Question

Python macro to show calculated totals [closed]

asked 2017-03-05 17:37:31 +0200

FAL gravatar image

updated 2017-03-05 17:39:24 +0200

I'm writing a Python macro to read fields in currently shown Base form, and use them to calculate value for another field in the same form. I don't know how to access those fields. Could anybody help me?

edit retag flag offensive reopen merge delete

Closed for the following reason the question is answered, right answer was accepted by Alex Kemp
close date 2020-09-27 23:36:22.485328

2 Answers

Sort by » oldest newest most voted

answered 2017-03-16 10:37:12 +0200

librebel gravatar image

updated 2017-03-20 17:38:15 +0200

Hello FAL, To change the value in a control in a Base Form using Python, you could write:

desktop = XSCRIPTCONTEXT.getDesktop()
thisComponent = desktop.getCurrentComponent()
oForm = thisComponent.getDrawPage().getForms().getByIndex( 0 )  # MainForm
if oForm:
    value1 = get_FormControl_Value( oForm, "Your_Control_1" )
    # Do your computations here ...
    set_FormControl_TextValue( oForm, "Your_Control_2" , value1 )

def get_FormControl_Value( oForm, strControlname ):
    # Gets the current Value from the specified control's model.
    if oForm:
        oCtrlModel = oForm.getByName( strControlname  )
        if oCtrlModel:
            return oCtrlModel.getCurrentValue()

def set_FormControl_TextValue( oForm, strControlname, aTextValue ):
    # Sets the Text of the specified control.
    if oForm:
        oCtrlModel = oForm.getByName( strControlname  )
        if oCtrlModel:
            oCtrl = getParentDocument( oForm ).CurrentController.getControl( oCtrlModel )
            if oCtrl.isEnabled and oCtrl.isEditable:
                oCtrl.Text = aTextValue

def  getParentDocument( oForm ):
    # Returns the OfficeDocument object of the specified Form, SubForm or Control.
    if not oForm.supportsService("com.sun.star.form.FormComponent"): return oForm
    x = oForm
    while not x.supportsService("com.sun.star.document.OfficeDocument"):
        x = x.getParent()
    return x

EDIT: to change the table values directly:

desktop = XSCRIPTCONTEXT.getDesktop()
thisComponent = desktop.getCurrentComponent()
oForm = thisComponent.getDrawPage().getForms().getByIndex( 0 )  # MainForm
if oForm:
    table_field1 = oForm.findColumn( "Field1" )
    table_field2 = oForm.findColumn( "Field2" )
    float_value1 = oForm.getFloat( table_field1 )
    # Do your computations here ...
    oForm.updateFloat( table_Field2,  float_value1 )

EDIT2: with format

desktop = XSCRIPTCONTEXT.getDesktop()
thisComponent = desktop.getCurrentComponent()
oForm = thisComponent.getDrawPage().getForms().getByIndex( 0 )  # MainForm
if oForm:
    table_field1 = oForm.findColumn( "Field1" )     # Get the table field index
    float_value1 = oForm.getFloat( table_field1 )   # Get the table field value as Float
    float_value1 *= 2                       # Do your computations here ...
    oCtrlModel = oForm.getByName( "txtField3"  )    # The name of your unbound control here
    if oCtrlModel:
        oCtrlModel.setString( "{0:.4f}".format( float_value1 ) )    # show precision 4 decimals
edit flag offensive delete link more


THANK YOU, librebel! This was exactly what needed. I did not know where to look... Could you please direct me to some documentation about thisComponent.getDrawPage().getForms() to find more useful info by myself?

FAL gravatar imageFAL ( 2017-03-17 20:59:17 +0200 )edit

yw FAL, here is example for thisComponent.DrawPage().Forms in StarBasic: https://wiki.openoffice.org/wiki/Documentation/BASIC_Guide/Working_With_Forms Here some info for writing LO scripts in Python: Python as a macro languagePyUNO samples

librebel gravatar imagelibrebel ( 2017-03-18 20:07:47 +0200 )edit

Again, Thank you!

FAL gravatar imageFAL ( 2017-03-19 02:29:40 +0200 )edit

I managed to change the fields I needed to change with your macro, librebel. But I feel there's still something wrong: those fields are formatted with Currency, but they only receive the text I give them. Is there a way to set their value with a Float, and to have them display it with the proper currency? Worse, while one of those fields correctly displays the value I give it, the other one gets a very different value from the one I give... I don't know why. Code follows.

FAL gravatar imageFAL ( 2017-03-19 20:20:56 +0200 )edit

Can't add code in a comment... too long. Trying to revise my question.

FAL gravatar imageFAL ( 2017-03-19 20:24:32 +0200 )edit

No way... can't find out how to change my question, too. :-)

FAL gravatar imageFAL ( 2017-03-19 20:28:23 +0200 )edit

in that case it would be better to perform your calculation using directly the field value which is stored in your table, rather than using the display value from the bound control. If your table field values are of type float, then you can use the Form's getFloat() and updateFloat() methods.

librebel gravatar imagelibrebel ( 2017-03-19 21:53:10 +0200 )edit

i edited the original answer with an example, good luck

librebel gravatar imagelibrebel ( 2017-03-19 21:56:42 +0200 )edit

I could see that your set_FormControl_TextValue() macro does actually change the text of the given field, but if you try to read the same field afterwards with your get_FormControl_Value(), you find it zeroed. Why?

FAL gravatar imageFAL ( 2017-03-20 01:49:20 +0200 )edit

I saw your comment only now! THANK YOU! but the fields I want to change have no value in the table, I try to calculate them from the table fields. Basically, I try to display a total due and a balance based on some fields in the current record. And I want to update them while the user edits the record.

FAL gravatar imageFAL ( 2017-03-20 01:54:05 +0200 )edit

I'm too sleepy to try it now, it's two o' clock and I'll have to get up at six. But as soon as possible I'll try these new functions. But where can I find a complete documentation? Where did you find all available functions and commands? I did not find a language and module reference.

FAL gravatar imageFAL ( 2017-03-20 02:07:46 +0200 )edit

I tried your suggested command, but as I feared, FindColumn() only works with table - backed fields. It seems it doesn't know of fields lacking a database-resident value...

FAL gravatar imageFAL ( 2017-03-20 17:11:57 +0200 )edit

answered 2017-03-21 00:58:17 +0200

FAL gravatar image

Standing on librebel's shoulders. I could finally solve my problem. Actually, it's simpler than you told me, librebel!

def set_FormControl_Value( oForm, strControlname, myvalue ):
    Sets the Value of the specified control. If the control is formatted,
    then it will take care of the formatting
    You only have to give it a myvalue of the right type.
    For example, if strControlname is a Currency field, 
    myvalue has to be a Float. 
if oForm:
    oCtrlModel = oForm.getByName( strControlname  )
    if oCtrlModel:
        oCtrlModel.Value = myvalue
edit flag offensive delete link more

Question Tools



Asked: 2017-03-05 17:37:31 +0200

Seen: 657 times

Last updated: Mar 21 '17