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?

Solved Solved
0 50 8,987
1 ACCEPTED SOLUTION

Sorry, I’m dumb, I didn’t see the computed name was the label and something else…
IFS("None"<>[AffilateCode], FILTER("Affiliates", [_THISROW].[AffiliateCode]=[AffiliateCode]))

View solution in original post

50 REPLIES 50

Steve
Participant V

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. You the bomb @Steve at explaining how that stuff works.

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.

@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?

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.

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.

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.

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.

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

Hi Steve, I bump with this answer in the quest for the solution to my problem. I am trying to create a virtual column using the sum function but with no success. Note that the list table(ref_rows) is fetching weight values which are in decimal, from another table. I tried using count function instead of sum to see if I am pulling the exact data I need and it seems to work fine. Any help, please.

GreenFlux
Participant V

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.

I think I might kind of understand. When AffiliateCode=“None”, you want something to be available for the user to ADD a new “Affilates” row. If AffiliateCode=“Fun143D” (or any other that already exists), display their first and last name in Affiliate.

Does that sound correct?

Yes! This is exactly what I want.

Ok, so now send me a screenshot of the all the column details for the Affiliate column you highlighted yellow in the previous form screenshot. I think I know how to do it, but I gotta see where my description begins.

Edit: Scratch that. I have enough information I think.

In the Leads table, change your [AffiliateKey] column from Ref, to Enum with a base type of Ref.
Suggested Values: IFS("None"<>[AffilateCode],[AffiliateKey].[_ComputedName])

This should give you the name when it finds a match, and should give you the add new button when no match, since there won’t be any suggested values if the Code is “None”.

Am I doing something wrong? When I put the code into “Suggested Values” I get an error:

Column Name ‘AffiliateKey’ in Schema ‘Leads_Schema’ of Column Type ‘Enum’ has an invalid Suggested_Values constraint ‘=IFS(“None”<>[AffiliateCode],[AffiliateKey].[_ComputedName])’. Error in expression [AffiliateKey].[_ComputedName]


Sorry, I’m dumb, I didn’t see the computed name was the label and something else…
IFS("None"<>[AffilateCode], FILTER("Affiliates", [_THISROW].[AffiliateCode]=[AffiliateCode]))

Had a similar problem today, your answer saved my day man! You're a genius😁

you saved my life!!! thank you very much 

Although the expression is valid, something is not working right

Oh, I see the issue, either clear the Valid_If or make my expression the Valid If instead of suggested. You can only use one or the other.

I removed the Valid_If upon you suggesting this formula and it still does this.

Hmm, same if you move my expression to Valid If? Can you click the Test button for the expression and post a screenshot of that window that pops open?

@Bahbus, you are a genius! I put your formula in the Valid_If expression and removed it from the Suggested Values and it works like a charm! The only caveat is there is no option to add New affiliate, which I’m not to worried about. There is a register option in our app for that option anyways. Thank you so much for your help!

On one of the up and coming video webinars Appsheet has, this should be an extended Ref_Rows lesson. LOL. Being able to filter REF_ROWS based on conditions can come in really handy. I wasn’t aware you could change the column types and have a base type still be a REF_ROWS.
@praveen

Yeah, that’s why I initially wanted to use Suggested Values, since that would allow the New to stick around, but not sure why it still pulled everything.

Thanks @Steve & @MultiTech_Visions for trying to help out through this process!

@Bahbus, last question. I apologize. Why is it that when I fill in values such as Affiliate & Affiliate Representative, when I progress to Customer (A REF_ROWS Table) and add new, these fields are not populated with the details that were input previously as seen in the image below?

Not entirely sure off just that. You’re table structure is pretty complicated though. But my guess is that, either the valid_if, or however else your populating, for it is incorrect. Send me a screenshot of the whole column details.

Customers:


Customers Cont’d:

Affiliates:


Affiliates Cont’d:

Leads:


Leads: Cont’d:

While this might be helpful for something in the long run, what I really need to look at is just the details of the column for Customers on that Leads form you were showing me.

Customers:


Affiliates:
Leads:

Top Labels in this Space