How do you add a Total Phones column populated by SQL COUNT(*) expression in the Zen Quickstart Tutorial?

Question

Question

Stephen Wilson · Oct 18, 2016

#Data Model #Object Data Model #SQL #Tutorial #ZEN #Caché

I have been following the online Zen Quickstart Tutorial using the lastest release documentation. In addition to playing around with the styling and making a few minor functionality tweaks, I wanted to add an additional column that shows a count of the number of phone numbers for that Contact (as shown in the image below).

The idea here is that you can see what contacts have phone numbers without having to click on the "view phones" link. All I have done here is add a blank additional column to the %ZEN.Component.tablePane object

<tablePane
          width="900px"
          id="contactTable"
          tableName="ZenTutorial.Contact"
          maxRows="1000"
          pageSize="18"
        showRowNumbers="true"
          showZebra="true"
          useSnapshot="true"
          extraColumnWidth="5%"
          rowSelect="true"
          onselectrow="zenPage.clearPhones();zenPage.hideContactForm();"
          caption="US Contacts"
          orderByClause="Name ASC"
          valueColumn="ID"
          >

          <column colName="ID" hidden="true" width="5%"/>
          <column header="Name" width="20%" colName="Name" filterType="text"/>
          <column header="Type" width="9%" colName="ContactType"/>
          <column header="Street" width="20%" colName="Address1_Street"/>
          <column header="City" width="12%" colName="Address1_City"/>
          <column header="State" width="7%" colName="Address1_State"/>
          <column header="Zip" width="7%" colName="Address1_Zip"/>
          <column header="Total Phones"  width="9%" />
          <column header="" width="5%" linkCaption="edit" link="javascript:zenPage.showContactForm('#(%query.ID)#');"/>
          <column header="" width="12%" linkCaption="view phones" link="javascript:zenPage.displayPhones('#(%query.ID)#', '#(%query.Name)#');"/>
</tablePane>

I want to use SQL similar to the following

select count(*) from ZenTutorial.PhoneNumber where Contact=?

Inotherwords, for each row in the TablePane get the total number of phone numbers for that contact. Does anyone know how to do this in ZEN?

Setting up your environment

The classes for the Zen Quickstart Tutorial are found in your Caché installation directory

eg. C:\<Your Caché Installation>\dev\tutorials\zen

Import Contacts.xml, CustomComponents.xml and ZenContacts.xml into your USER namespace

Populate the classes with some data using the Populate( ) utility

Do ##class(ZenTutorial.Contact).Populate(25)
Do ##class(ZenTutorial.PhoneNumber).Populate(75)

If anyone has a solution to this problem, let me know how you did it. I can get the SQL to work in the SQL Shell and the System Management Portal SQL viewer. I just can't get the new tablePane column to populate correctly.

Discussion (4)0

Log in or sign up to continue

Timothy Leavitt · Oct 18, 2016

Here's one option:

<column header="Total Phones" colName="TotalPhones" colExpression="(select count(*) from ZenTutorial.PhoneNumber where Contact=ZenTutorial.Contact.ID)" width="9%" />

The colExpression property allows you to specify a more complex expression to get the value for the column.

1 0

score 0 · Answer 1 · 2016-10-18T12:37:30-04:00

Thanks Timothy Leavitt, that's exactly what I was looking for. In addition, I added the "TotalPhones" column as a Transient property of ZenTutorial.Contact class.

Property TotalPhones As %Integer [ Transient ];

score 2 · Answer 2 · 2017-02-28T15:47:16-05:00

Hi Stephen and Timothy, Real nice feature, but how does it work when the datasource of the tablepane is a query instead of the table?

Adding the TotalMatrix (Transient) property to the query resulted in an Error:

Suggestions appreciated!

score 0 · Answer 3 · 2017-03-15T11:12:51-04:00

Hi Marco,

I think your problem might be to do with having declared 'TotalMatrix' as a transient property, it does not exist in the 'Relations' table. In my example, I referenced a table class called 'ZenTutorial.PhoneNumber' but my SQL statement did NOT refer to a 'TotalPhones' column.

Consider this

colExpression="(select count(*) from ZenTutorial.PhoneNumber where Contact=ZenTutorial.Contact.ID)"

So the result of the SQL goes into my TotalPhones column on my tablePane but the ZenTutorial.PhoneNumber class does not recognise it as a SQL field. Transient properties are useful when opening %Persistent objects and dynamically setting the transient property at the %OnOpen() event based on a set of conditions. As TotalPhones is marked as transient, it is not stored in the Global Mapping for %Library.CacheStorage. You cannot reference it in your SQL statement.

What if you tried something like

SELECT COUNT(*) AS TotalMatrix FROM Relations

You will need all those relationships in there though for it to work. If you have a <column> section defined in your tablePane be sure to set colName="TotalMatrix"

I hope that helps.