Table Transformer SQL TypeError: Cannot read properties of undefined (reading '1')

[Scott Gillespie]

Hi,

I have a page where I was displaying a simple table of resource rating results for all engagement groups. I was asked to add breakout values for each engagement group (FG-AM-3, FG-AM-5, and FG-AM-6).  I had separate Table Transformers so I had 4 separate tables.  I want to merge the code so I have one Transformer macro producing a single table with 4 data rows. Note that for brevity, the code below is just for the total and one group.  Final solution will have additional code to display results for 2 more groups.

The first SELECT statement below before UNION ALL works perfectly by itself. The second SELECT statement after UNION all also works perfectly by itself.  Together, I get the Cannot read properties error noted in subject. 

Based on similar posts in this forum, I think the problem is with the presence or absence of the "T1." prefix on some fields.  I think I've tried every combination, but still get the error.  Please suggest how I can resolve this. Also open to different approach. 

/* Display total resources rated and resource satisfaction percentage:
(Meets + Exceeds Count / Total Rated Count) */
SELECT
FORMATWIKI("{cell:width=300px|align=center|font-size=50px|font-weight=bold}",
"All", "{cell}")
AS 'Engagement Group',
/* Display total count of engagements */
FORMATWIKI("{cell:width=300px|align=center|font-size=50px|font-weight=bold}",
(SELECT COUNT(DISTINCT(T1.'Engagement')) FROM T1), "{cell}")
AS 'Total Engagements',
FORMATWIKI("{cell:width=300px|align=center|font-size=50px|font-weight=bold}",
COUNT('No Rating') + COUNT('Meets') + COUNT('Exceeds') + COUNT('Does Not Meet'), "{cell}")
AS 'Total Resources Reviewed',
FORMATWIKI("{cell:width=300px|align=center|font-size=50px|font-weight=bold}",
COUNT('No Rating'), "{cell}")
AS 'Total Resources Not Rated',
FORMATWIKI("{cell:width=300px|align=center|font-size=50px|font-weight=bold}",
COUNT('Meets') + COUNT('Exceeds') + COUNT('Does Not Meet'), "{cell}")
AS 'Total Resources Rated',
FORMATWIKI("{cell:width=300px|align=center|font-size=50px|font-weight=bold}",
ROUND((COUNT('Meets') + COUNT('Exceeds')) / (COUNT('Does Not Meet') + COUNT('Meets') + COUNT('Exceeds')) * 100) + "%", "{cell}")
AS 'Total Resource Satisfaction % (Meets or Exceeds Expectations)'
FROM T1

UNION ALL

/* Display FG-AM-3 total resources rated and resource satisfaction percentage:
(Meets + Exceeds Count / Total Rated Count) */
SELECT
FORMATWIKI("{cell:width=300px|align=center|font-size=30px|font-weight=bold}",
"FG-AM-3", "{cell}")
AS 'Engagement Group',
/* Display total count of engagements */
FORMATWIKI("{cell:width=300px|align=center|font-size=30px|font-weight=bold}",
(SELECT COUNT(DISTINCT(T1.'Engagement')) FROM T1 WHERE T1.'Engagement Department' LIKE "FG-AM-3%"), "{cell}")
AS 'Total Engagements',
FORMATWIKI("{cell:width=300px|align=center|font-size=30px|font-weight=bold}",
COUNT('No Rating') + COUNT('Meets') + COUNT('Exceeds') + COUNT('Does Not Meet'), "{cell}")
AS 'Total Resources Reviewed',
FORMATWIKI("{cell:width=300px|align=center|font-size=30px|font-weight=bold}",
COUNT('No Rating'), "{cell}")
AS 'Total Resources Not Rated',
FORMATWIKI("{cell:width=300px|align=center|font-size=30px|font-weight=bold}",
COUNT('Meets') + COUNT('Exceeds') + COUNT('Does Not Meet'), "{cell}")
AS 'Total Resources Rated',
FORMATWIKI("{cell:width=300px|align=center|font-size=30px|font-weight=bold}",
ROUND((COUNT('Meets') + COUNT('Exceeds')) / (COUNT('Does Not Meet') + COUNT('Meets') + COUNT('Exceeds')) * 100) + "%", "{cell}")
AS 'Total Resource Satisfaction % (Meets or Exceeds Expectations)'
FROM T1
WHERE T1.'Engagement Department' LIKE "FG-AM-3%"

[Stiltsoft support · Accepted solution]

Hi @Scott Gillespie ,

Please check a simple example below:

SELECT
COUNT(DISTINCT(T1.'Data')) AS 'Distinct Data'
FROM T1
UNION ALL CORRESPONDING
SELECT
COUNT(DISTINCT(T2.'Data')) AS 'Distinct Data'
FROM T2

As I can see from your query, you've missed the "CORRESPONDING" part and still refer to the T1 table in the second part of your query (in the internal SELECT and in the last two lines) but it is your T2 table now. Please check these things once more.

If you are still stuck, please refer to our support. Attach your page storage format (upper right corner of the page -> menu ... -> View storage format). Then we'll be able to recreate your source tables and the query inside the Table Transformer macro and help.

[Scott Gillespie]

Thanks as always for the quick reply.  I did not know about the CORRESPONDING function, so another new thing learned today.  As for the T1/T2, I was unclear in my description.  I had 4 separate transformer macros, but they are all operating on the same data table from Table Excerpt Include.

The new, merged SQL I want to use is operating on a single table via Table Excerpt Include.  So, I didn't think anything other than T1 would apply.  I tried your suggested solution to be sure, but still get the same TypeError.

See sample table below.  Results table row 1 would show All with 6 resources, row 2 show FG-AM-3 with 2 resources, row 3 show FG-AM-5 with 1 resource, and row 4 show FG-AM-6 with 3 resources.

Engagement	Engagement Department	Resource	No Rating	Does Not Meet	Meets	Exceeds
A	FG-AM-32	Tom			1
A	FG-AM-32	Sue				1
B	FG-AM-51	Mary		1
C	FG-AM-63	Adam			1
C	FG-AM-63	Ben	1
C	FG-AM-63	Kelly			1

[Stiltsoft support]

Hi @Scott Gillespie ,

Then you may try the following workaround:

SELECT 'Engagement Group',
COUNT(DISTINCT('Data')) AS 'Distinct Data'
FROM
(SELECT *,
SUBSTR('Project',1,7) AS 'Engagement Group'
FROM T*)
GROUP BY 'Engagement Group'

Here in the internal SELECT we take our projects (FG-AM-31, FG-AM-32, etc.) and leave the first 7 characters: FG-AM-3, FG-AM-3, etc. For my example, I call the new column as 'Engagement Group'.

In the External SELECT we calculate whatever we need for the whole table and add the GROUP BY 'Engagement Group' statement. So, our calculations will be applied to each Engagement Group separately.

After that you can wrap your Table Transformer macro in the Table Filter macro and count your totals.

And you may also use the Spreadsheet from Table macro for the case.

Wrap you source table or your Table Excerpt Include macro in the Spreadsheet from Table macro and calculate using cell formulas, familiar conditional formatting and other Excel-like features.

[Scott Gillespie]

This!  This is such an elegant solution compared to what I had developed--thank you very much for your help here.  As you see below, I took your base code and applied my specific data points along with some formatting, and wrapped in Table Filter.

That leads me to a final question. As expected, the total line from Table Filter uses standard formatting.  I don't think this is possible, but is there any way I can apply my data row formatting to the total row, or would I have to rework the base SQL?

This is not a deal breaker, as I could remove the data row formatting for consistency. As this is for management reporting along with a lot of other data on the page, the formatting is a useful visual aid.

[Stiltsoft support]

Unfortunately, the total row can't be formatted via the Table Filter macro.

You may leave only counting in the internal Table Transformer macro, count Totals via the Table Filter macro and add an external Table Transformer macro to apply conditional formatting to all the rows.