How to get only unique results in a report based on a particular column

[Patrick_Paul]

I have a bot that generates an email based on an email template.  In the template I have the following expression:

Name

I-CAR ID

Role

<<Start:orderby(Select([Related GC Requirements][_RowNumber], [Activated]=No, True),[Name])>>

<<[Name]>>

<<[I-CAR ID]>>

<<[PDP Role]>>

I want to make it so that I only get unique [Name] results.  I currently have the select statement set to return only distinct rows, but in this data there are often numerous distinct rows with the same [Name].  Basically I want a particular [Name] to only appear once in the email, whereas right now I get the same [Name] over and over if there are multiple rows for it.  Any help?  Thanks!

[ludapa]

If im not wrong, what you need to do is to add function ANY() at the beginning:

<<Start:orderbyANY((Select([Related GC Requirements][_RowNumber], [Activated]=No, True),[Name]))>>

You nedd to know that is going to choose any random value.

[SkrOYC]

What you are trying to do is not straighforward because, if you think in terms of the data and template, you are summarizing the table by some value of your choice but you need to get only one key. This is actually a little bit weird because why are there many rows with the same name in the first place? Is this name actually a Ref to another table? Why you need to ignore the other two columns and just get a random one for a particular name? Too many questions... Anyway, here is a solution if that's actually what you need

Solved: Re: PAGE BREAKS IN PDFS - Google Cloud Community

[Patrick_Paul]

I realize you're trying to "fix" my data, but the data doesn't need fixing.  Essentially what I'm trying to do is simple and, I would think, common.  Here's a hypothetic example table where each row represents a requisition someone has placed for an item:

Req ID            Requestor Name           Item Needed     Fulfilled?

1                    Bob                                   Stapler                 No

2                   Bob                                    Ruler                    No

3                  Sally                                    Binder                 No

4                 Garth                                    Hat                     Yes

So, given this example, what if I want to send a list of names in an email that have a requistion that hasn't been fulfilled yet?  That's basically what I'm trying to do here.  I want to see all items that are not fulfilled (or in my real-world case, "activated"), but I only want to see each name once:  Bob and Sally, that's it.  Not too hard to understand, right?  Thanks!

[Patrick_Paul]

Seems like I should be able to wrap the select in a UNIQUE() function, but that doesn't seem to change anything, strangely.  Doesn't even cause an error...?

[SkrOYC]

You better check what I sent you, and also make sure to study what each function actually does so you are not lost trying to guess what happens

AppSheet function list - AppSheet Help

[Patrick_Paul]

I appreciate you taking the time to respond, but I have 20+ years in this sort of thing, but I do struggle with Appsheet syntax from time to time because it's a mish-mash of spreadsheet formulas and procedural script language.  Unfortunately, I have an awful lot going on, so I come here only when I'm stumped and need a little helping hand.  If you, or someone else, could simply look at my example above and give me a nudge, that would be very appreciated.  Cryptic "read the manual" responses are unfortunately not helpful.  I've been reading the documentation for quite some time before I came here.  Thank you.

[Suvrutt_Gurjar]

---

@Patrick_Paul wrote:

Req ID            Requestor Name           Item Needed     Fulfilled?

1                    Bob                                   Stapler                 No

2                   Bob                                    Ruler                    No

3                  Sally                                    Binder                 No

4                 Garth                                    Hat                     Yes

So, given this example, what if I want to send a list of names in an email that have a requistion that hasn't been fulfilled yet?  That's basically what I'm trying to do here.  I want to see all items that are not fulfilled (or in my real-world case, "activated"), but I only want to see each name once:  Bob and Sally, that's it.  Not too hard to understand, right?  Thanks!

---

If understanding of your requirement is correct, it sounds that you are looking for grouping records by [Requestor Name] in the template.

If so, you could use the excellent tip by @AleksiAlkio  mentioned below with some modifications.

https://www.googlecloudcommunity.com/gc/Tips-Tricks/Grouped-list-from-one-table/m-p/277320

The modifications would be:

1. Please create a slice called say " Names_Not_Fulfilled" on the table  with an expression something like [Fulfilled?]="No"

2. Create a [GROUP] VC with an expression something like 

SELECT(Table Name[Key], AND([Requestor Name]=[_THISROW].[Requestor Name], [Fulfilled]="No"))

3. Then the template can be something like 

<<Start: SELECT(Names_Not_Fulfilled[Key],[Key]=INDEX([GROUP],1))>>

<<[Requestor Name]>>

Req ID	Item Needed	Fulfilled?
<< Start: [GROUP] >><<[Req ID]>>	<<[Item Needed]>>>	<<[Fulfilled?]>><<End>>

  <<End>>                                                                                                                                                                                                                                 

[SkrOYC]

---

@Patrick_Paul wrote:

Essentially what I'm trying to do is simple and, I would think, common

---

That's actually one of the problems, the fact that it's not common to do such a thing in AppSheet.

---

@Patrick_Paul wrote:

what if I want to send a list of names in an email that have a requistion that hasn't been fulfilled yet? 

---

Well, if it's a List of the Names rather than the actual rows, it's very easy:

SELECT(
  TableName[Requestor Name],
  NOT([Fulfilled?]),
  TRUE
)

This expression assumes that your table is called "TableName" and your [Fulfilled?] column has a Y/N column type.

Now, the problem with your use case and the reason I gave you my opinion above is that it seems you were looking for a way to get the Rows, not just the Names. The best way that I know of to do such a thing without messing with virtual columns (not a bad thing, just not one that I recommend) is to learn this out-of-the-box solution:
https://www.googlecloudcommunity.com/gc/AppSheet-Q-A/PAGE-BREAKS-IN-PDFS/m-p/460458/highlight/true#M...

It is an exaplanation of a technique I learned from @Steve 

Also, when I pointed to how you have your data structured, I just was wondering what would you do -assuming you actually want the row and not just the names- when you have more than one row whose Requestor Name is Bob and it hasn't been Fulfilled. Which of the rows that match this criteria is being used? Any? The latest? The earliest? You expect this to not happend? Etc...

---

@SkrOYC wrote:

why are there many rows with the same name in the first place? Is this name actually a Ref to another table? Why you need to ignore the other two columns and just get a random one for a particular name?

---

[SkrOYC]

Let's assume for example that:

1. You don't care if multiple rows match the criteria and
2. You actually have a table of Requestors so that [Requestor Name] is a Ref to that table.

You could do something like:

FILTER(
  "Requestors",
  IN(
    [Related TableNames][Fulfilled?],
    FALSE
  )
)

This would get you the Rows of the Requestors that have at least one related record in the table called TableName where [Fulfilled?] is False/No

[Patrick_Paul · Accepted solution]

I appreciate everyone's contributions... they sparked my imagination.  However, I think between not being familiar with my data and perhaps misunderstanding my objective a bit, everyone was (unintentionally) trying to make this more complex than it needed to be.  I found a simple solution:

I created a slice, similar to @Suvrutt_Gurjar's suggestion.  It filtered the data as follows:

and(
[Activated]=No,
isnotblank([Name])
)

The "isnotblank" on the Name field just scrubs out some anomalies in the data.  One thing I didn't bother mentioning is that this data is provided to us from an external source, so unless I want to do a bunch of scripts and/or spreadsheet formula gyrations (which I personally find inelegant and prone to breakage) in Google Sheets prior to Appsheet "ingesting" the data, I had to do a little scrubbing.  I prefer to clean my data within Appsheet when possible.

Anyhow, this slice resulted in only the rows without an Activated status.  Yay.  I then created a virtual column ("Name and ID") that was a concatenation of the Name (main thing I was going for) and their ID, which is helpful as well.

I need to execute this bot at the row level based on a table of locations so each manager gets a list of the people he needs to active, so I then created another virtual column on the locations table (which was the source of the dereference in the original template) as follows:

Select(Not Activated[Name and ID], ([_THISROW] = [Linked Location]),true)

Notice this allows me to procure a list of only unique data based on the virtual column [Name and ID] I created above.  Since I'm not creating a dereference here, I can get away without using the key field, which skirts around the issue of not being able to procure unique rows in the template.  As an aside, I find it unfortunate that in Appsheet functions such as UNIQUE() when wrapping a SELECT() take into account the key field, which should be unique, so therefore they don't do anything in that case.  Took me awhile to figure that out.

So now my template just outputs the above virtual column for each location.  I would say "easy peasey" but it really wasn't.  Took me a few hours to figure all this out and unfortunately that kind of time just doesn't exist for me during the work week.  Just thought I'd share my solution in case others could benefit from it.  Thank you again for your help, without your ideas I wouldn't have come up with it.