Articles in this section
Category / Section

9.5 Work report with hidden rows and partial sums

Published:
Updated:

In this example we wanted a report where we can add various types of work with different product codes. We want to add the number of items, such as hours, spare parts used, etc., for each product code. Then we want to know the cost for each code and the total cost.

You can download this form from our Downloads page and import it to your own server.

The work report will look like this:

951.png

Our form calculates partial sums according to Product code. As there are 2 lines of “A1”, the number of items of these two lines have been summed up (3+10=13).

The table IN TOTAL only lists the 3 product codes used in this report. As you can see from the Multiple Select element “Product code” in the Elements panel shown below, there are actually 5 codes in all; A1, A2, B1, C, and D. This example also shows how we can omit the product codes that are unused instead of just listing them all as =0. This is especially handy when you have a long list of options.

952.png

First we want to find out how many lines have a specific work code in them and add the number of items in these lines together. As we’ve just seen, for the code “A1” we want to find 3 and 10, and get 13. For this we can use the element No of A1: that contains the function sumCorrectRepeatAnswers – see picture below. This function sums up number_of_items for all the times the Product code “A1” is entered and puts the number in no_of_A1.

953.png

The price per item is set in a hidden Number element, Unit price of A1, etc. In the Other tab in Element Properties, we have set the Default value of the unit price, in the picture below €22. Note that this element is placed outside of the repeat so it won’t show up in the repeat table. We may also choose to show the Unit price element, and set the price each time we use the form.

954.png

This amount is calculated in the field Amount A1 (€):

956.png

We could easily make a list for all the 6 product codes like this. The codes that are not used will then show that their number is 0 (zero) and amounts is 0. But we want to create a more elegant IN TOTAL list that omits the unused codes. This means that we must use an IF – THEN logic (if the value of the element is 0, then do not show it). Such logic is always defined as a rule in the top Form element.

First, we want to make each row in the IN TOTAL table hidden. We select each row one by one, and click “Hidden”as property. See picture below:

957.png

Then we select the form element at the very top, and add rules for each of the 6 product codes:

958.png

The rule for showing the amount for code A1 if it is larger than 0 looks like this, and the other rules are defined similarly for each product code:

959.png

We’ve added a Number field at the end, Total Sum, adding all the individual amounts based on the function sumNumbers:

9510.png

Was this article useful?
yes
No
Help us improve this page
Please provide feedback or comments
Access denied
Access denied