Template not correctly formatting with Parent records

[Mark_Taylor]

Greetings,

I am trying to create a template to generate session reports.

I have a table called “Sessions”

These record goals that were worked on during the session

Goals are grouped into domains.

Goals and Domains each have their own tables where information about them is stored.
Goals has a column referencing the domain that the goal belongs to.

Each session may only use a subset of goals from a subset of domains.

Within my session table I added two virtual columns

One for the Goals that were used for the session referencing the original goals table
One for the Domains that those goals that were used belong to referencing the original Domains table

In my template I used a <Start:> statement to select all the domains.

For each of the domains returned I added a sub <Start:> statement to only return the goals that belong to that domain.

When I generate the report I successfully only get the domains that were used, but below them are all the possible goals that belong to that domain and not just the goals that were used during the session.

I added some extra lines below the chart just to ensure that my statements should be working.

One referencing the Virtual “UsedGoals” column directly
One using a select statement instead.
You see these below the table in the template

Here is my template

Template

This is what it produces

Report

Here it is in word with what I am trying to achieve

Should be

I am currently using
https://help.appsheet.com/en/articles/961746-template-start-expressions
to guide me though this creation process but have not found anything there that explains this.

Any ideas as to what I am doing wrong?

Thanks in advance

[AlexM]

You don’t need select for the goals.
Start ref column
[column to show 1]
[column to show 2]
End

[Mark_Taylor]

Alex,

Thank for the reply. I want to filter the list to just the goals that have a domain matching the domain from the parent Start. that is why I am using the Select.
<<Start: SELECT([UsedGoals][Goal ID],[Domain] = [_ThisRow-1].[Domain ID]) >>
If I use
<<Start: [UsedGoals] >>
I do not know how I can accomplish the filtering.

Thanks

[AlexM]

But that’s what start in start does.
I hope I am not getting it wrong and wasting your time but humor me for a second:

<<Start: [UsedDomains]>><<[Domain]>>
<<Start: [UsedGoals]>><<[Goal]>>
<< End>>
<< End>>

try this without a table (that can mess it a bit sometimes if the End is not in the right place

[Mark_Taylor]

I am trying to restrict the goals return to just the goals associated the Parent domain
But that’s what start in start does.
I hope I am not getting it wrong and wasting your time but humor me for a second:

<<Start: [UsedDomains]>><<[Domain]>>
<<Start: [UsedGoals]>><<[Goal]>> (Where Domain = [_ThisRow-1].[Domain ID]
<>
<>

[Mark_Taylor]

I tried this (Removed the leading << with , becuase this text editor gets wierd

, Start: [UsedGoals] >>
, If: [Domain] = [_ThisRow-1].[Domain ID]>> <<[Goal]>>
,EndIf>>
,End>>

instead of
,Start: SELECT([UsedGoals][Goal ID],[Domain] = [_ThisRow-1].[Domain ID]) >>
,[Goal]>>
,End>>

and I get this

New

Which leaves me blank cells

[AlexM]

One shift (session) has multiple units (domains) which have multiple stops (goals).
Is this how your data is set up as well?

This is my start in start:

for each unit (domain - parent) I am getting only the stops (goals - children) that belong to that unit

[AlexM]

You can call Start "a filter"as it will only return the value(s) stored in ref column so it will only return the goals that belong to that parent.

so what you are trying is to filter a table within a filter that already does that for you.

[Mark_Taylor]

These two domains (Safety and Adaptive) have the following Goals (The ones used for the session have a *

All Goals

Safety
*Develop appropriate response to environmental stimulus.
*Develop recognition of danger
Develop understanding of basic safety concepts like: “no” and “stop”
Mouthing inedible objects
Self-Injurious Behaviors (SIB) (Head banging)

Adaptive
*Develop activities of Daily Living (ADL) Hand washing
*Develop activities of Daily Living (ADL) Toilet Training
Develop activities of Daily Living (ADL) Brushing teeth
Develop activities of Daily Living (ADL) Self-feeding Chewing and swallowing.
Develop transition skills (Novel stimulus)
Develop sleeping routines
Activities of Daily Living (ADL) Develop leisure skills

Both of the expressions
<<Start: [UsedGoals] >>
<<[Domain].[Domain]>> <<[Goal]>>
<< End>>

And

<<Start: SELECT([UsedGoals][Goal ID], TRUE) >>
<<[Domain].[Domain]>> <<[Goal]>>
<< End>>

Return the following

Results

Safety Develop appropriate response to environmental stimulus.
Safety Develop recognition of danger
Adaptive Develop activities of Daily Living (ADL) Hand washing
Adaptive Develop activities of Daily Living (ADL) Toilet Training

If I run
<<Start: SELECT([UsedGoals][Goal ID],[Domain] = “ceb03935”) >>
<<[Domain]>> <<[Goal]>>
<< End>>

(“ceb03935” is the [Domain ID] for Safety )

I get the following
ceb03935 Develop appropriate response to environmental stimulus.
ceb03935 Develop recognition of danger

[AlexM]

got it! sorry! I was imagining a different thing

how many domains in total? 2-3 or plenty?

[Mark_Taylor]

There are 5 Domains but if the session does not use any goals from a given domain I don’t want them to appear in the report hence the [UsedDomains] Virtual columns

I thought there may be something to that Filter so I used the following

<<Start: [UsedDomains] >><<[Domain]>>
<<Start: Filter([UsedGoals],([Domain] = [_ThisRow-1].[Domain ID]))>>
<<[Goal]>>
<< End>>
<< End>>

But I get an error

Expression ‘Filter(UsedGoals,([Domain] = [_ThisRow-1].[Domain ID]))’ is invalid due to: Unable to find table ‘UsedGoals’,

[AlexM]

if it’s just 5, you can hide them, again with start-start.

i need to look for an example that I’ve posted here a while ago.

maybe that will help

[AlexM]

in this example you can see the photo table appears only if it has a record, otherwise it doesn’t

Ok, so below is the template START EXPRESSION... Questions

You can use a Start in Start template. Check the results below. [image] With child record: [image] Without the child record: ] [image]

[Mark_Taylor · Accepted solution]

Ok I got it to work

I had to abandon my Virtual Column “Usedgoals” and instead use the expression I used to form it inside of a filter

<<Start: [UsedDomains] >><<[Domain]>>
<<Start: Filter(Goals,AND(([Domain] = [_ThisRow-1].[Domain ID]),in([Goal ID],Select(CSOU[GoalRef],[Session] = [_THISROW].[Session ID]))))>><<[Goal]>>
<< End>>
<< End>>