Template: Start expression + Select unique values

[JayG]

Hello,

While using SELECT statement with START expression, is it mandatory to use key column?

TblTest - Col 1 (key col), Col 2

This works - <<START: SELECT (tblTest[Col 1), TRUE)>> - But I want distinct values of Col 2

Doesnt work: <<START: SELECT (tblTest[Col 2), TRUE, TRUE)>>

Doesnt work: <<START: UNIQUE (SELECT (tblTest[Col 2), TRUE))>> 

Please help!

[Suvrutt_Gurjar · Accepted solution]

---

@JayG wrote:

While using SELECT statement with START expression, is it mandatory to use key column?

---

Yes, one needs to create a list of key columns within the <<START>> expressions

---

@JayG wrote:

This works - <<START: SELECT tblTest[Col 1), TRUE)>> - But I want distinct values of Col 2

---

You may want to try below, please create a slice on called say C2_Unique on  the table with a filter  expression something like 

[Col 1]= MINROW("Table Name", "_ROWNUMBER", [Col 2]=[_THISROW].[Col 2])

Here [Col 1] is a key column.

Then your start expression can be something like 

<<START: C2_Unique[Col 1]>>

[JayG]

@Suvrutt_Gurjar - Thanks for your reply. It worked perfectly 🙂

But if you dont mind, can you please help me to understand this with an example? I didnt quite get it? I have 5 other tables to apply this, hence want to be 100% sure of the logic. My Col 2 is a virtual column which consists of a combination of material type and color and my table is Quotation. In the template I just want to show unique combinations. Thank you!

[Suvrutt_Gurjar]

Hi @JayG ,

Please take a look at the description of the function MINROW() or MAXROW()

MINROW() - AppSheet Help

So essentially the slice expression of 

[Col 1]= MINROW("Table Name", "_ROWNUMBER", [Col 2]=[_THISROW].[Col 2])

selects the smallest row number record for each of the [Col 2] values.  You could instead use MAXROW() also as long as any one single record for each [Col 2] value needs to be selected. MAXROW() would select the highest row number record for each of the [Col 2] values.

Then you can use that slice in the <<START>> expression because the slice anyway has just one record for each value of [Col 2]

Hope I could explain. Please test well for your requirements.

[JayG]

Hi @Suvrutt_Gurjar - I am facing an issue related to this, hence posted it here.

Step 1) I used this solution for creating Quotation and it works perfectly.

Step 2 - I wanted a "Copy Quotation" functionality, which works perfectly (as per this - Solved: Re: Copy Functionality - Google Cloud Community

Step 3 - For this copied quotation, when my Quotation is generated (Step 1), it shows everything all the data coming from these slices as "blank"

Hardware data is coming directly from the table, hence it is showing up.

After all lot of testing and trial and error, my conclusion is the "Slices" are not working properly as it should and hence, I started searching for slices related error and I found this: Slice view rendering wrong set of rows - Google Cloud Community

Any idea how this should be tackled? Also adding @Koichi_Tsuji since he had this issue with App sheet team. This issue was raised in 2022 June, wondering still no fix has been provided? Also the work around mentioned in the post about creating a VC, is something I feel is not applicable to me because I am just trying to fetch distinct records from a virtual column itself in the template.

Please help!

[Suvrutt_Gurjar]

A

---

@JayG wrote:

For this copied quotation, when my Quotation is generated (Step 1), it shows everything all the data coming from these slices as "blank"

---

Are those all  slices of child tables? Is the data to those slices added when the parent record is created? If so, you may want to take a look at the following help article and section "Send an email only after adding a parent record and all of its children"

Also please go through this post.

Report template child records not pulling through - Google Cloud Community

[JayG]

Hi @Suvrutt_Gurjar  - Thanks for your revert! Yes, all slices belongs to Child tables only and Quotation (Parent) record is created first separately and then User goes to sub parts of the quotation and creates child records (Glass Dado, Civil, Hardware, etc.)

[Suvrutt_Gurjar]

Okay, please refer reccomended article section and similar post I shared earlier to implement solution if your sequence of records creation matches as those in shared article and oost.

[JayG]

Referred to them but I feel not applicable in my case. Just a thought, is it possible to fetch distinct values from a virtual column in the template without usage of slice (my original question)? 

[Suvrutt_Gurjar]

The VC will will need to have some relation to the record from where the bot is triggered or the values in VC need to be .a list of specific key values of the records. Or the bot will not know where to pick the VC values.

[JayG]

Yes relationship is there. VC is part of child tables and bot is triggered from parent table. Problem is I need distinct values of VC

[Suvrutt_Gurjar]

My suggestion will be to start a new post for a new issue always. You may at the most refer the earlier post if it is somewhat related to new post. This will enable a wider community to respond to a new thread. Also when you have such a requirement, may  I also request you to share the relevant expression you are using etc to post in that thread and describe your issue /requirement with respect to that or else community will not know exactly what you mean by mere description and you may receive generic guidelines based on general understanding of the issue.