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! Go to 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]))
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.
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:
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]:
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:
Affiliates:
Leads:
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:
User | Count |
---|---|
32 | |
31 | |
30 | |
19 | |
17 |