1. Michael Welch
  2. InfoMaker
  3. Friday, 21 May 2021 21:00 PM UTC

Hello all,


I am very new to Infomaker and Sql information to begin with, but I am having a hard time wrapping around computed columns. I am working on the below.


I have contracts for items and I want to calculate the amount of the item that we have purchased this item. (multiple contracts, for multiple customers, appearing on mulitple invoices, for multiple items) see attached pic.

What I need is, if the Item ID, Customer Name and Contract no are all the same I want them to sum the invoice qty (last column on the pic).


Right now what it is doing is making another row of information for every item when it appears on an invoice. I want it to maintain one row and increase the final column every time it appears on an invoice.


Please advise if there is an easier way to handle this or if you have a way to point me in the right direction, been googling and watching youtube for days and can't find a proper solution for my exact case.


Attachments (1)
Olan Knight Accepted Answer Pending Moderation
  1. Monday, 24 May 2021 01:41 AM UTC
  2. InfoMaker
  3. # 1

To your original question:

A computed FIELD is onethat is created within the datawindow itself, usually to SUM various totals andusually placed in the Trailer band.

A computed COLUMN is amanually added fake column in the SQL of the datawindow. There are two ways to do this. 1.  Put the DATA view into SYNTAX mode, and add your fake column. Examples include
           " "  ptr                  // create a column to hold the row pointer image
           0    column_flag     // create a column into which you will place various flags or values for internal useview

2. In the GRAPHIC view of the syntax, click on the COLUMNS tab and ass one fake column per row.
    I call these fake columns beccuase the datawindow will add your column into the result set just as if it was a table column from the current SQL.

Later -


There are no comments made yet.
John Fauss Accepted Answer Pending Moderation
  1. Sunday, 23 May 2021 23:52 PM UTC
  2. InfoMaker
  3. # 2

Hi, Michael - 

If you wish to group the rows that share identical values for the first three columns and sum the seventh column, you need to first eliminate any other columns that do NOT also share the same values...This means you could, if desired, also include the 4th, 5th & 6th columns in the data grouping, from the looks of the image you posted.

I'm not familiar with the Infomaker product, but assuming the menu options and terminology are the same as in PowerBuilder, you need to create a Group in your report. To do that, use the Rows -> Create Group menu item.

A dialog window will open where you drag/drop the columns that are in the Group (the columns that share the same data values). Once done, click OK to close the dialog window.

Important: The row sorting MUST include the same set of columns, in the same order as what you've defined in the Group, or the data grouping will not work correctly. 

Two bands (initially both zero height) will be added to your report: Group 1 Header and Group 1 Trailer. Open (increase the size of) the Group 1 Trailer band. Drag the six column objects you defined in the group from the Detail band to the Group 1 Trailer band. Delete the column object that will be summed from the Detail band (this does NOT delete the column from the SQL Query) and close up (decrease the size) the Detail band to height=0.

Add a computed field object to the Group 1 Trailer band in the location where you want the sum to reside. The expression dialog window will appear. An expression to sum the column named xyz, for example, is:

   sum(xyz for group 1)

Save the report and retrieve (you may need to close and re-open the report for your changes to appear). The results you desire should be visible. Of course, you will likely need/want to make cosmetic changes.


Best regards, John 

There are no comments made yet.
  • Page :
  • 1

There are no replies made for this question yet.
However, you are not allowed to reply to this question.