LINKTOROW should return only one row with a condition

I am trying to develop an application which should suggest a product model based on the form entries of the users. The end user should fill out a form (a couple of category questions and a numeric input) and as he hits the save button, he should be directed to a specific product model, which will be selected from a lookup table.
How should it work? Let me give you an example: The user enters in the form: Fruit, Apple, Jonadold and types in the weight 120gr. Now the expression should go to my output (lookup) table and compare the concat which is just created in the formentries table and the one in the lookup table and then take the next bigger weight for the boxmodel. So the desired result here is: Boxmodel S

[Concat] [Weight] [Boxmodel]
FruitAppleJonagold 100 XS
FruitAppleJonagold 150 S
FruitAppleJonagold 200 M
FruitAppleJonagold 250 L
VegetableTomatoeCherry 50 XS
VegetableTomatoeCherry 100 S

Until now, I have created an action with a target link with a LINKTOROW expression. User fills up the form and as he hits the Save button, he is directed to Ref View, which he should see the result. The expression I am using is validated but giving the whole entries of a lookup table instead of a single row:

LINKTOROW(SELECT(FormEntries[_THISROW], Output[Concat]=FormEntries[Concat]), “Output”)

I was hoping that this expression should at least give me the rows matching the Concats but I am viewing all of the entries in the table. I also tries expressions with Any Select and Filter but I am not getting desired results.

Here are the tables I have:

FormEntries
This is the table where the form responses are recorded. The options the users are selecting are concatenating in a new column called [Concat] with an initial formula.

Output
This is a lookup table in view only mode (with a Ref View on UX) where I have a column [Concat] which will be compared with the FormEntries later. This is my main reference table to indicate which will be the right model for the selections made based on the concat.

Here are my views:
Form
This is the form the user is supposed to fill in, of course in form type. This is the only visible UX, others are ref views. Data source is the FormEntries table
Output
This is a ref view with the data source with the same name

Behaviour Action:
For a record of this table: FormEntries
Do this: Go to another view within this app
Target: LINKTOROW(SELECT(FormEntries[_THISROW], Output[Concat]=FormEntries[Concat]), “Output”)

Can someone help me with the right expression?

Wrap your SELECT() expression with ANY().

1 Like

I have tried:
LINKTOROW(ANY(SELECT(FormEntries[_THISROW], Output[Concat]=FormEntries[Concat])), “Output”)

But still getting all of the entries…
Also the app needs to select the max of the numeric input. I have no idea how to put that in the expression.

1 Like

Please post a screenshot of the form view configuration, of the action configuration, and of the view displayed after submitting the form (the view that shows “all of the entries”).


LINKTOROW(ANY(SELECT(FormEntries[_THISROW], Output[Concat]=FormEntries[Concat])), “Output”)

As I hit Save Button, I am directed to the view where I see all of the entries:

But instead I should see only the model 801

I have also tried this expression: LINKTOFILTEREDVIEW(“Output”, [Concat]=[_THISROW].[Concat]) This is working fine in another app, but not here. I am getting a blank page with this.

1 Like

Is your view specifically configured to use your action?

1 Like

I had already defined the action “Go to Outputs”:

1 Like

Okay, so the next thing to do it confirm your expression is working as you intend.

Please open Expression Assistant to edit your action’s Target expression:

image

Within Expression Assistant, find and click on the Test button. Are the results what you expect?

2 Likes

I have just tested and the expression is indeed not doing what I was expecting. It looks like this:


the Concat column is empty altough there is input in the sheets:

My Concat Formula is in the AppSheet:
image

1 Like

The App formula expression of a normal (not virtual) column is only (re-)evaluated when its row is first added or updated in some way. An update that triggers that reevaluation is opening the row in a form and saving the form, or using an action to make a data change to the row. Since those rows already exist, you’ll need to update them to trigger AppSheet to recompute the App formula expressions. If you only have a small number of rows (which it appears you do from the screenshots), simply open and save (make no changes) each row from the app.

2 Likes

Hmm that makes sense. Similar expression was working on an app, in which I was only viewing the data. So I was hoping that AppSheet could update a row in a form and run the action at the same with the new added data. I can understand now why it is not working.
(I have ~6300 entries in the lookuptable. The last screenshot is from the Sheets I was using to collect the Form responses. )
I am unsure if I get your suggestion right. I remove the action from the form, so AppSheet will just add new rows as I hit “Save” in the form. then I should update them so that appSheet can run the trigger? Did I understand you right? I am afraid not :frowning:
P.S.:Thanks very much for your replies

1 Like

Is the purpose of the Concat column only to help navigation to the other row?

2 Likes

yes. This way, I can find the matching lines from the lookup table.

Thank you, I created a new workaround: After the data entry the user get an email.

1 Like