Is there a way to Filter a ref_row based on input text in another field before sync?

I’ve created a valid_if statement for a column. Based on the values input into that column, I want my ref_row to filter the results prior to syncing. Is this possible?

I’m not clear what you want. Can you elaborate?

He want’s some of that

FILTER([Related Records][RecordID], [ColumnA] = [_thisrow].[Column1])

type of thing. :slight_smile: You the bomb @Steve at explaining how that stuff works. :wink:

1 Like

I have a VALID_IF statement In column [AffiliateCode]: AND(TRUE,IN([_THIS],Affiliates[AffiliateCode]))

The next row on my form has a REF_ROWS linked to the parent table. I want the parent table to filter based on the AffiliateCode VALID_IF statement.

1 Like

@MultiTech_Visions, I tried something similar to that but got an error.

It sounds like you want a way to apply an additional filter to the ref_rows result, yes?

Just to clarify, you’ve already got two tables setup with a reference between and what you’d like to do is take the list of child records and apply an additional filter to them - one that’s specified in the parent record.

Yes?

Typically, the REF_ROWS() virtual columns are added by AppSheet automatically. Modifying the column’s App formula expression may cause AppSheet to create another such column, so I don’t recommend modifying the existing virtual column. Instead, consider adding a new virtual column with an App formula expression like this:

SELECT(
  [ref_rows-column][row-key],
  ([_THISROW].[AffiliateCode] = [AffiliateCode])
)

replacing ref_rows-column with the name of the column with the REF_ROWS() expression, and row-key with the name of the key column of the table to which the REF_ROWS() expression refers.

2 Likes

Quick add-on to @Steve’s post. I have found that if you don’t change the name of the auto-generated “Related” column, it doesn’t try to create a new one if you only change the expression.

3 Likes

I see this as well.

Also, if you aren’t wanting to use any of the system generated ones, and you don’t want their calculations to affect performance, you can change the REF_ROW(“Whatever”, “Whatever”) to false without the system freaking.

As long as you don’t change the name, you can get away with this and that ref_rows formula won’t be calculated - thus not affecting performance.

This is correct. I have Column [AffiliateCode] and based on the valid if expression:

AND(TRUE,IN([_THIS],Affiliates[AffiliateCode]))

The next row in my form is [AffiliateKey] which is a key relating to Parent table: Affiliates

I want to have the drop down that appears to validate the [AffiliateKey] relation to [AffiliateCode] in the Affiliates table based what was typed in, or provide an add new option.

Unfortunately, this doesn’t produce the desired result. Any more suggestion?

Hi @Antwain_Jordan. Here’s a technique for filtering a list of related records. I think this is close to what you’re asking. There’s a sample app in the post that may help.

Unfortunately, I’ve tried using SELECT(), FILTER() and and VALID_IF statements and they aren’t producing the desired result, or any result for that matter.

The Parent table is Affiliates with [AffiliateKey] as the Primary key

The child table is Leads with [Form_Key] as the Primary Key and foreign REF_ROWS key as [AffilaiteKey]

I have a VALID_IF Statement in the Affiliates[AffiliateCode] that verifies the text that’s typed in this column: AND(TRUE,IN([_THIS],Affiliates[AffiliateCode]))

I want the relationship between the Affiliates table & Leads table to only show the name(s) that have the matching affiliates code used in the VALID_IF statement in the [AffilaiteCode]

Please provide screenshots of:

  • the AffiliateKey column of the Affiliates table
  • the AffiliateCode column of the Affiliates table
  • the Valid_If expression of the AffiliateCode column of the Affiliates table
  • the Related Affiliates column of the Leads table

Please explain what you mean by “I’ve tried using […] VALID_IF statements”. There’s no such thing as a “VALID_IF statement”.

What does “The child table is Leads with … foreign REF_ROWS key as [AffilaiteKey]” mean? “Foreign” isn’t really an AppSheet term, so you’ll need to clarify what you mean by it. Please also provide a screenshot of the REF_ROWS() expression. If it includes [AffilaiteKey] literally, that’s likely your problem.

Have you tried using the Test button in the Expression Assistant to test your expressions against real data? This is an excellent way troubleshoot problem expressions.

Affiliates table Primary Key = [AffiliateKey]:


Leads table Primary Key = [Form_Key]:

SHOW_IF (Sorry for confusion) expression exists on the Leads table for the REF column [AffiliateKey]. This statement works well:

I want the Affiliate drop-down BLUE (REF_ROWS) to only show Affiliates with matching [AffiliateCode] Yellow:

I’ve tried using the test option, and all it does when I use various statements is show me the primary key. for the REF_ROWS.

I need the AppSheet table column screenshots, please.

Please explain in plain language what your intention is with the SHOW_IF expression. I had a vague idea when you claimed VALID_IF, but now I’m completely confused.

I want the Affiliate drop-down (ref_rows) to filter based on the text input into the AffiliateCode column.

Affiliates:

Leads:

The SHOW_IF expression allows users to type in an AffiliateCode, instead of choosing one from a drop down as I do not want all the affiliate codes to pop up. Essentially if Affiliate Code = NONE I want the Affiliate drop down to show: Add New or Non. If it AffilaiteCode = Fun143D, then show Dough Funny, etc.

I think you would be best served by engaging support@appsheet.com directly. I do not feel I can properly help you.