Can't select array in lookup, only text

Hello,

Yesterday and today, I’ve been trying to make an app that acts like a quiz/quickscan. The user selects certain choices, and the application should give back the unselected choices with the highest score.

Now, I’ve encountered an issue:

When a user selects multiple choices, it delimits it with a comma. (For example: Option 1,Option2). I can split this with the Split Text function, but when I try to do a lookup on that column from another table, all I can select is the original text string with the comma. I can not select the Split Text array, only the text form.

Responses table with split text and array of split text: Screenshot by Lightshot
No option for ‘Seperated answers’ or ‘Seperated answers array’: Screenshot by Lightshot

Thanks in advance!

Hi! May be you can split them in a target table not in source. But it seems not what you need.
To filter unselected you needn’t to sptlit, just if OptionX inclided in text string(Choices) so OptionX
Then you can rollup Highest score of them

Try creating a relation to that column.

1 Like

Hm, but the relation will show just a row in which array set has find?

I don’t know how @Glidebahn structures the other table to lookup from, but I imagine the relation is the right first step to go forward.

So for @Glidebahn, is the user answering only one question, and you have a list of answers with corresponding scores in another table?

1 Like

Thank you all for the responses!

I will try splitting it to a seperate table with a relation. I indeed to have an ‘Answers’ table with the weight.

App explanation
The user has to answer around 10 questions. Each question contains 4-10 answers. I want to have the answers that the user didn’t select per question. Every answer has a weight to it, and from the unselected answers I want to know which one has the highest weight within a certain category of questions. (there are 2 questions per category). You could kinda think of it like a personality test, if that helps.

My next hurdle is linking the user-specific unchecked answers + score to the categories. So it would look something like [{answer: score},{answer: score},…] for each category

Hope that clarifies, but till now it seems impossible to make this in Glide. What do you guys think? Is it feasable?

From your description, I think this would be ok for a start.

  • Create a relation from the questions table to the answers table, make it match multiple rows.

  • Create a lookup to return the list of answers for the questions.

  • Split the user’s answers for a question into an array using the split text column.

  • Use the “Remove elements” column to remove all the user’s answers from the list of all answers. You would get a list of unselected answers.

image

  • Create a relation of those unselected answers array back to the Answers table.

  • Use a rollup column to return the one with the highest value from the relation.

But need highest per category, per user?
Please clarify the task^
You need highest score answer in a category(mean not highest answer for question) and all per user?
Yes?

Thank you!

Create a relation from the questions table to the answers table, make it match multiple rows.

So should each question be a row, with another column for the possible answers? I cant select the source for the values, it only lets me select the questionnames as the possible answers.

If I make the question a column, with the answers listed under it, then I can select them as possible answers. But I don’t know how I should make the relation?

I managed to get an array in the table ‘Unchecked answers’ table with only the unchecked answers! But I don’t know how to group them and use a rollup for the score. What should I select in this part? Screenshot by Lightshot

I was thinking the data structure would be like this.

Questions table

RowID | Question | Category

Answers table

Question’s RowID | Answer | Weight

Do you construct it like this, and given @slscustom.ru 's question for me, can you give a clear example of questions - answers - desired output?

Yes, so just to summarize with your structure example, this is what I have right now:

Question/answer table
Question | Answer | Score | Categories array (1 or more)

Choices table
Question 1 | Question 2 | Question3 …

Responses table
Row ID | User’s email | Q1 answerstring | Q1 seperated answers array | Q2 answer string | Q2 seperated answers array | …

Unchecked answers
User email | Relation to responses table on user email | Q1 checked answers | Q1 possible answers | Q1 only unchecked answers array | Q2 …


So what I would like is the following:

Question: What do you do for safety in a car?
Answers (+score): I put on my seatbelt (8) | I turn on my lights (6) | I adjust headsupport (6) | I have a functioning airbag (9)

User selected answers: I put on my seatbelt, I adjust headsupport
Unselected answers: I turn on my lights (6), I have a functioning airbag (9)

The tip that should get recommended to the person: I have a functioning airbag. Because the score 9 is higher than score 6.

So many variants how to implement, but still many variant how about your flow.
Main concept like this:
if some where you have single value so you can make a relation from this value to array column and to find row or rows related
Then you can deal for compare or roll.
Sorry, my English so bad.
In my opinion is much more easy to calculate result online compared to save user choices and then to route messed relations. Almost is the same, but first way is much more easy to imagine the route.

Hmm im not sure if I understand you correctly, but I appreciate your answer! :sweat_smile:

What do you mean by ‘much more easy to calculate result online’? Where online exactly?

I mean, when user acting the interface, so you can use user specific column to split out from others and use more simple relation route and use if then elese logic instead of messed relations

Setup:

Single row table with user specific to operate current question and results

Relation from answer dictionary to results

Validation


Roll up max of unchecked (but may be it is not you need but the concept)

Now you can deal with result anything and then write new data to store
P.s. I can’t see any of you screenshots