I'm sure this is easy for someone, but not me...

H1_Unlimited
Participant III

I’m sure this is easy for someone, but not me: I have a table (RESULTS) that lists all results by the driver name, event and their results. I made a virtual column (DRIVER RESULTS) in that table that gives me a concatenated text string - for instance (2015 Seafair: 1st Place) for each entry so I now have a pretty text string I can use.

In the DRIVERS table, I have the drivers listed by name (only 13 of them), and all I want to do is get the (DRIVER’S RESULTS) list from the RESULTS table to the DRIVERS table as a column I can display.

I can get a comma delimited list of row numbers, but I cannot get the (DRIVER RESULTS) text. Also, if I CAN get the DRIVER RESULTS

list to show up, how do I get the entries as individual lines instead of comma delimited?

In other words, i need to have the ability to list a driver’s bio info, then a list of all of their finishes in the bottom of a detail view as a list and not a string of text separated by commas…

I am completely confused at how to do this!!!

Can anyone give me some ideas?

0 30 1,197
30 REPLIES 30

In the RESULTS table, make the [DRIVER RESULTS] column the Label column so that the value of this column is displayed when its row is referenced. Then, in DRIVERS, add a virtual column with an App formula using either select() or filter() to gather the row’s driver’s results from the RESULTS table.

For instance (assuming both tables use [DRIVER_ID] to identify a particular driver)

=filter(RESULTS, in(DRIVER_ID, list([_thisrow].[DRIVER_ID])))

=filter(RESULTS, in([DRIVER_ID], list([_thisrow].[DRIVER_ID])))

H1_Unlimited
Participant III

+Steve Coile I appreciate the response, but it doesn’t work - I get no result from this at all.

Once more, here’s the setup: In the DRIVERS table I have a DRIVERS column with their names (13 of them). In the DRIVER’S RESULTS table I have 1000 lines of results with the driver’s name in the DRIVER column, and results for that driver at each event with a concatenated EVENT FINISH column returning something like this: “2016 Seafair: 1st Place”.

I just want to compare the driver’s name in the DRIVER column of the DRIVERS table to the driver’s name in the DRIVER column of the RESULTS table and return a list of ALL of the EVENT FINISH entries for that driver to my virtual column in the DRIVERS table, in individual lines I can display as a list below the driver’s bio info. I just want a list of finishes per driver - that’s all.

No matter what combination of variables I plug into your formula, I cannot get ANYTHING to show as a result. No matter what column I choose as a label or key or anything, nothing returns!

This is driving me frickin’ crazy and I’m ready to throw in the towel! This shouldn’t be so tough!

@H1_Unlimited Without the square brackets, AppSheet is looking for the literal text, “DRIVER”, in the results [DRIVER] column value.

@H1_Unlimited My mistake in the formula I gave you earlier.

H1_Unlimited
Participant III

+Steve Coile Changed DRIVER to [DRIVER] and get back row numbers separated by commas.The return should be the EVENT FINISH values, separated by lines, not commas…

@H1_Unlimited Where are you seeing this list? In the app itself, or when testing the expression?

H1_Unlimited
Participant III

+Steve Coile When testing

H1_Unlimited
Participant III

@H1_Unlimited Saving and trying it in the driver’s bio display now

H1_Unlimited
Participant III

+Steve Coile This is what returns in the app display, then when you hit “VIEW” nothing shows up…

H1_Unlimited
Participant III

@H1_Unlimited Now we get a result, but only a few, and when you hit “VIEW”, it goes to the detail page of the results, which is not really what we want. We actually don’t need or want ANY detail view here - just a list of the results for that driver…

@H1_Unlimited In testing, I would expect a comma-separated list of numbers: the numbers are the row numbers of the matching rows. The row numbers are also the row keys, which makes it a list of references, which is why you go to the detail page when you click on them in the app. When displaying references, the app uses the label column value rather than the key column value.

@H1_Unlimited Instead of the filter() expression I gave you, try replacing it with:

=select(DRIVER RESULTS ONLY[EVENT RESULT], in([DRIVER], list([_thisrow].[DRIVER])))

This should give you the textual list you’re looking for.

@H1_Unlimited It might help if I could look at your config. Would you be willing to give me access? I’ll not make any changes.

H1_Unlimited
Participant III

+Steve Coile You are a STUD! It works! Now the only problem is that each entry is separated by a comma. Do you know how to make each entry an individual line?

H1_Unlimited
Participant III

OK, this is not working so well: without the ability to have each entry on its own line, the list is unusable. I have been searching for an hour and all I have found are posts that explain there is no way to display each result on its own line. Is there another work-around or is AppSheet really that limited?

@H1_Unlimited I agree with your consternation. Yes, AppSheet has some severe deficiencies.

H1_Unlimited
Participant III

+Steve Coile Aleksi Alkio was able to save the day with this:

You just need to substitute the ", " with the line break. The expression could be like…

SUBSTITUTE(CONCATENATE([LIST]),", “,CONCATENATE(” "))

It works PERFECTLY, so keep this fix handy if you ever want to convert those comma separated values into line breaks!

:- )

Hello,
This is helpful, thanks, but how do I put the line break character into the expression?
I copied the expression from the post and it just replaced the , with a space.
Thanks

@Simon
Try with

SUBSTITUTE(
	CONCATENATE(
		FILTER(
			DRIVER RESULTS ONLY,
			IN(
				[DRIVER],
				LIST(
					[_THISROW].[DRIVER]
				)
			)
		)
	),
	" , "
	"
	"
)

Thanks. I didn’t expect it to be that simple

You’re welcome. Just try the expression and post us back should you require further assistance.

I should have said, I tried it and it worked.
Now it’s much clearer when it shows on the app

@H1_Unlimited Awesome!

H1_Unlimited
Participant III

+Steve Coile No problem at all - how do I do that?

@H1_Unlimited Click Share App near the top of the left rail, enter my email (sc2758g@gmail.com) in User emails, set Add as co-authors? to ON, and click Add users + send invite. You’ll also remove my access from the same screen.

@H1_Unlimited I’m in.

H1_Unlimited
Participant III

+Steve Coile Done. On the Columns tab, we’re using “H1 Drivers” and “DRIVERS RESULTS ONLY” tables

@H1_Unlimited What is the results table, that should show only the select rows?

@H1_Unlimited I’m guessing the [EVENT FINISH] virtual column of the H1 Drivers table.

@H1_Unlimited Change this:

=filter(DRIVER RESULTS ONLY, in(DRIVER, list([_thisrow].[DRIVER])))

to this:

=filter(DRIVER RESULTS ONLY, in([DRIVER], list([_thisrow].[DRIVER])))

Note the added square brackets around DRIVER in the first argument to in().

Top Labels in this Space