Method to filter Related Records

AppSheet AppSheet Q&A
1

Hello community. Every time a child table is generated, a column that is [related child] is automatically created in the parent table.

However, sometimes it is necessary to apply a filter to that list, since you don’t always want to get all that list. The most rustic way (so to speak) is to do a filter or a select from the child table;

FILTER(“ChildTable”,AND(

IN([RowID],ParentTable[Related Child]),

[Column1]=Condition)

)

However, I would like to know if there is a faster and more practical way to obtain this data, only using the virtual column in the parent table

I appreciate your answers

2

This:

SELECT(
  [Related Childs][key-column],
  [Column1] = Condition
)

Replace key-column with the name of the key column of the child table.

5 Likes
3

Well, you can completely replace a REF_ROWS() expression with a FILTER() or SELECT() so that you use that instead.
But I’m not sure exactly how adding extra row-filter expressions will affect the new rows added via the Add button in the Inline view, I don’t recall that since related rows are that, all of the rows related to the current one

1 Like
4

I was just about to also comment on this.

If you want to preserve the [related] column as is (suggested), filter again using @Steve 's expression, which is quite efficient because the Select() only checks the already filtered rows (the related ones)

2 Likes
5

Excelente Oscar, si usaré la expresión que me dices de Steve, porque es justamente eso lo que quiero, reciclar las filas ya filtradas.

1 Like
6

Thanks as always @Steve !

1 Like
7

If I wanted a different field from the related object, how would I do that? For instance, in my below example I’m doing a select on a related object to show a list of product names this client is enrolled in. However, calculating this virtual column takes 15 seconds, where as stepping through a relationship only takes .5 seconds. Am I just not doing this correctly?

SELECT(allenrollments[Productname], 
               AND(
                   [_THISROW].[SF Account ID] = [SalesForceId], 
                   [Productstatus] = "Pending Activation",
                   [ProgramClean] = "Web",
                   NOT([EnrollmentStatus]="Complete")
                   )
      )

My goal would be to use the relationship of table 2 and allEnrollments (the key of both tables is [productId]

and get back a list of [productname]

8

I think this might work:

SELECT([Related Enrollments][Productname], 
AND(
[_THISROW].[SF Account ID] = [SalesForceId], 
[Productstatus] = "Pending Activation",
[ProgramClean] = "Web",
NOT([EnrollmentStatus]="Complete")
)
)
9

I can’t seem to use the reference fields to step through the tables correctly when they’re encased in a SELECT, or FILTER statement. The app just tells me the column name is missing.

10

Can you post a screenshot?

1 Like