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

(H1 Unlimited) #1

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?

(Steven Coile) #2

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) #3

+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!

(Steven Coile) #4

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

(Steven Coile) #5

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

(H1 Unlimited) #6

+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…

(Steven Coile) #7

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

(H1 Unlimited) #8

+Steve Coile When testing

(H1 Unlimited) #9

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

(H1 Unlimited) #10

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

(H1 Unlimited) #11

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

(Steven Coile) #12

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

(Steven Coile) #13

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

(Steven Coile) #14

@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) #15

+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) #16

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?

(Steven Coile) #17

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

(H1 Unlimited) #18

+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!

:- )

(Steven Coile) #19

@H1_Unlimited Awesome!

(H1 Unlimited) #20

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