Complete a function field using a report script

You can use a script to assign a value to a field and include the field in the report layout.

Preparation:

In the list of balances you have calculated the average amount per invoice per debtor. You now want to add the average for all invoices.

You could duplicate the Gemiddelde field in the Detail section and place the duplicate in the footer. You could then define the field as a calculation field that calculates the average:

However, the result may be inaccurate because you first calculate the average per line and then use the same field to calculate the average for all invoices. The correct result is:

Average = 8.841,07 / 14 = 631,51

To complete a function field using a report script:

In this example you first add a GemTotaal function field to the report.

1. Open the report: Saldilijst met gemiddelde.
2. Click on:  to go to the Function fields tab.
3. Add a function field named GemTotaal.
4. Enter the value 0 in the function field.

   A function field always has to have a value. If the value is not known yet, you enter a 0. Later on in the process the function field will get a value.

   

5. Click on: OK.
6. Drag the field in the report into the Document footer.

   

   You put the field in the layout because otherwise you would not be able to use it in the script editor.

7. Click on:  in the toolbar.

   Profit opens the script editor.

8. Open the Document footer / Print heading to add a script that refers to the document footer.

   

   Profit opens a new window for scripts in the document footer.

9. Perform the following steps to include fields in the script:
   1. Go to the tab: Report elements.
   2. Open the 'Document footer / DocumentVoetSubsectie' heading.
   3. Drag the fields to the editing area.
   4. Compose the formula.

      In the course you need the following formula:

      {Element: VeldGemTotaal}.value=DecDev({Element: Veld Totaal Factuurbedrag1},{Element: Veld Aantal facturen1})

   

   The addition of .value to a field is required to make sure that the field can get a value.

10. Click on: OK.
11. Click on: Close.
12. Press: F7. or go to Document / Print preview.

    

    To get the above result, you have to set a report filter that only shows debtor numbers smaller than 10024. You could also add a report filter with ‘Number of invoices > 0’ to prevent division by zero in the script (the chance of this error occurring is small in this situation, but if the error does occur, an error message will appear when you try to save the report).