9.5 Work Report With Hidden Rows And Partial Sums

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:

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.

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.

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.

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

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:

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

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:

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