Start Expression issue

[Shaun_Stringer]

I am having trouble with the filter part of my start expression.

Here is a table with example data. The table is called Training.

Employee	Training	Expiration Date
A	1	1/1/2021
B	1	2/1/2021
A	2	2/1/2021

<<Start:OrderBy(FILTER(“Training”, OR([Training]=”1”, [Training]=”2”)),[Employee], FALSE, [Expiration Date], FALSE)>>

I am trying to add to this filter expression so that if an employee has completed training 1 and training 2, as Employee A has done, AppSheet will only consider the row with the most recent Expiration Date. In this case, only the last row for Employee A.

Thank you for your help. Let me know if you need more info.

[WillowMobileSys]

Is the Training column defined as Text or as Number?

Also, you should show the template portion under the START.  Maybe the rows are be selelcted just fine but the tempalte is disregarding something instead?

[Shaun_Stringer]

Training is a text column. Here is the whole template

Training 1 or 2

<<Start:OrderBy(FILTER(“Training”, OR([Training]=”1”, [Training]=”2”)),[Employee], FALSE, [Expiration Date], FALSE)>>

<<Employee>>

Training: <<Training>>

Expiration Date: <<[Expiration Date]>>

<<End>>

[WillowMobileSys]

---

@Shaun_Stringer wrote:

<<Employee>>

Training: <<Training>>

---

The column names in this part above need to have "[ ...]" - square brackets around them

[Shaun_Stringer]

Good catch. I am happy to add those, though it has been sending me a PDF report just fine without them. I really just need to know how to expand the filter expression as I outlined initially. 

@Steve 

[WillowMobileSys]

---

@Shaun_Stringer wrote:

Good catch. I am happy to add those, though it has been sending me a PDF report just fine without them.

---

But you said it was dropping rows?

I am trying to find reasons why its doing that.  Other than the missing missing "[...]", I don't see anything else wrong with your template expression.

I don't know if the "[...]" are optional or not in a template, I always include them.  In your case, you have a table and a column with the same name.  I would think in certain expressions that missing "[...]" would cause ambiguity and could maybe be the source of the missing row(s).

I recommend adding the missing "[...]" to see if they make a difference.  After that, I would do these things:

• make sure the data has no trailing spaces.
• remove the Sorting parameters and just focus on getting ALL the rows to display - even remove the formatting and just have your template use this expression:

<<Start:OrderBy(FILTER(“Training”, OR([Training]=”1”, [Training]=”2”)))>>

     <<[Employee]>>  <<[Training]>>  <<[Expiration Date]>>

<<End>>

Once you get all the rows appearing, then slowly build it back up to its original state.

[Shaun_Stringer]

Sorry for the confusion. My current expression is working fine, but I want to edit the filter expression so that the first row of data does not show up. Since Employee A completed Training 1 and 2, I only want to see the row associated with the most recent Expiration date, which in this case, is the last row. If we added another row...

Employee	Training	Expiration Date
A	1	1/1/2021
B	1	2/1/2021
A	2	2/1/2021
A	1	3/1/2021

Now, for Employee A, we would only see a summary of the last row because he completed Training 1 again and it has a more recent Expiration Date.

---

[WillowMobileSys]

Ah!  I see now.  The issue is that [Expiration Date] now matters and is dependent on the context of the Employee.  But there is no Employee reference for whom the expression can check and get the latest training. 

So.... a different more complicated template structure is required.  You want to first choose the Employee list who have had trainings and then use each of those rows to select the proper training rows by Date.  We can do this using the MAXROW() function which will give us the max row by Date for each Employee and Training

<<Start:SORT(UNIQUE(Training[Employee]))>>
     <<Start:OrderBy(FILTER(“Training”, 
                     OR([Training Key Column] = MAXROW('Training", "Expiration Date", 
                            AND([Training] = "Training 1",
                                [Employee] = [_THISROW-1].[Employee])),
                        [Training Key Column] = MAXROW('Training", "Expiration Date", 
                            AND([Training] = "Training 1",
                                [Employee] = [_THISROW-1].[Employee]))
                     )), [Expiration Date], FALSE)>>
<<Employee>>

Training: <<Training>>

Expiration Date: <<[Expiration Date]>>

     <<End>>
<<End>>

Some Notes:  

• replace "[Training Key Column]" with the actual key column from the Training table.
• [_THISROW-1] is a way to reference a row from the outer template expression
• This expression is not tested so may need some tweaks

[Shaun_Stringer]

<<Start:SORT(UNIQUE(Training[Employee]))>>
     <<Start:OrderBy(FILTER(“Training”, 
                     OR([Training Key Column] = MAXROW("Training", "Expiration Date", 
                            AND([Training] = "Training 1",
                                [Employee] = [_THISROW-1].[Employee])),
                        [Training Key Column] = MAXROW("Training", "Expiration Date", 
                            AND([Training] = "Training 2",
                                [Employee] = [_THISROW-1].[Employee]))
                     )), [Expiration Date], FALSE)>>
<<Employee>>

Training: <<Training>>

Expiration Date: <<[Expiration Date]>>

     <<End>>
<<End>>

The edits I made to get it working are in red. However, the first start expression duplicates the results of the second start expression by the number of unique employees.

And the second start expression, although it does return only the most recent row based on the expiration date if there are two rows of training 1, it does not go one step further and choose and return only the most recent row based on expiration date if Training 1 and 2 are present for the same employee. 

My output is the following, based on our example data above;

A
Training: 2
Expiration Date: 2/1/2021
A
Training: 1
Expiration Date: 3/1/2021
B
Training: 1
Expiration Date: 2/1/2021
A
Training: 2
Expiration Date: 2/1/2021
A
Training: 1
Expiration Date: 3/1/2021
B
Training: 1
Expiration Date: 2/1/2021

I am still working through some edits but let me know your thoughts.

What I am looking for is...

A
Training: 1
Expiration Date: 3/1/2021
B
Training: 1
Expiration Date: 2/1/2021

[WillowMobileSys]

Good catch on the tweaks!

It looks to me that the template expression is executing twice. But first let's adjust to get your actual desired results.  The expression is actually much simpler - sorry I didn't get it before

Just  need to adjustment to the inner expression.  You actually don't care which training,  just get the most recent one for the Employee.  See the changes for that below:

<<Start:SORT(UNIQUE(Training[Employee]))>>
     <<Start:MAXROW("Training", "Expiration Date", [Employee] = [_THISROW-1].[Employee])>>

<<Employee>>

Training: <<Training>>

Expiration Date: <<[Expiration Date]>>

     <<End>>
<<End>>

 If you are still getting the results duplicated, then you will need to look outside these expressions.  Something is causing them to execute twice.

[Shaun_Stringer]

I am getting some errors because we are no longer using the key column. 

"Please verify that it generates a List and that the contents of the List are Ref values. Ref values should come from the 'Key' column of the referenced table."

Also, although that would give me the most recent row of any training type, I do care about which training. In reality, there are many employees and many types of training. I just need to show the most recent result training of 1 or 2, but not both. With the following row additions..

Employee	Training	Expiration Date
A	1	1/1/2021
B	1	2/1/2021
A	2	2/1/2021
A	1	3/1/2021
B	3	4/1/2021
A	4	5/1/2021

my desired result would be the same as I noted previously...

A
Training: 1
Expiration Date: 3/1/2021
B
Training: 1
Expiration Date: 2/1/2021

[Shaun_Stringer]

Employee	Training	Expiration Date
A	1	1/1/2021
B	1	2/1/2021
A	2	2/1/2021
A	1	3/1/2021
B	3	4/1/2021
A	4	5/1/2021
B	2	6/1/2021

With this extra row, my desired result would now be..

A
Training: 1
Expiration Date: 3/1/2021
B
Training: 2
Expiration Date: 6/1/2021

[Shaun_Stringer · Accepted solution]

I got it. It is this.

<<Start:OrderBy(FILTER(“Training”, 
                     [Training Key Column] = MAXROW("Training", "Expiration Date", 
                            AND(
                                OR([Training] = "Training 1",
                                   [Training] = "Training 2"),
                            [Employee] = [_THISROW-1].[Employee]))),
        [Expiration Date], FALSE)>>
<<Employee>>

Training: <<Training>>

Expiration Date: <<[Expiration Date]>>

     <<End>>

Thank you for all of your help!