How do I show 2 different table with a similar (ID)/unique keys in one Gallery

[Anonymous]

Hi community, 

I have 2 tables where table 1 is named
(QuestionsVerify)  [questionID,question,vapquest,mm,guide]

while table 2 is named
(AnswersVerify)  [questionID,question,questionRemark,questCorrect]

As you see the table above, there are 2 similar columns among these tables to be used for lookup ---> [questionID,question] with similar data/content. 

Now what I need is to have a Gallery with a layout of (Title, subtitle), where the Title will be from table (QuestionVerify)[question] and the subtitle will be from table (AnswersVerify)[questionRemark]. 

I've tried some answers from other community questions of doing it but still failing to display any content with 

(Method 1)
AddColumns(GroupBy(QuestionsVerify,"question","questionGroup"),
"Group",
AddColumns(questionGroup,"questionRemark", LookUp(AnswerVerify,question=questionGroup[@question],questionRemark)))

(Method 2)
AddColumns(QuestionsVerify,"questRemark", LookUp(AnswerVerify,questionID = [@questionID],questRemark))

How do I work around with the Items of the Gallery without using Collection and only LOOKUP function ? 

Thanks in advance.

[mdevaney]

@Anonymous

Can you please provide some sample data from each table to help visualize the problem?  Also show an example of what you want the resulting table should look like.

Additionally, can you please tell us what type each column is?  (e.g. text, number, lookup, yes/no, choices, date, calculated, etc.) 

---
Please click "Accept as Solution" if my post answered your question so that others may find it more quickly. If you found this post helpful consider giving it a "Thumbs Up."

[Anonymous]

Hi @mdevaney 

For table 1, QuestionsVerify is name of the table, [questionID] is the unique key which will be integer (1,2,3,4 .......), [question] is the Title/code of questions like(VAP1.1,VAP1.2....), [vapquest] is the question like (Whats your name, Experience....),[mm] is the criticality of the questions like (Major,Minor,Major,Minor......), and last column for this table [guide] is the guideline to answer the questions with long sentences and descriptions. 

For table 2, AnswersVerify is name of the table, where the first 2 columns have similar attributes as table 1, while the next two will be [questionRemark] which consist of answers from the one who answer the questions, and the last column [questCorrect] will be option to be input as YES/NO by the one who answer.

Does this explain your questions ?

[mdevaney]

@Anonymous 

Can you please show an example in a Table format like this?  It hard to understand your when you are writing out a narrative instead or showing in a diagram. 

QuestionsVerify

Field1	Field2	Field3	Field4
Value	Value	Value	Value
Value	Value	Value	Value
Value	Value	Value	Value
Value	Value	Value	Value

AnswerVerify

Field1	Field2	Field3	Field4
Value	Value	Value	Value
Value	Value	Value	Value
Value	Value	Value	Value
Value	Value	Value	Value

FinalTable (Showing In Gallery)

Field1	Field2	Field3	Field4
Value	Value	Value	Value
Value	Value	Value	Value
Value	Value	Value	Value
Value	Value	Value	Value

---
Please click "Accept as Solution" if my post answered your question so that others may find it more quickly. If you found this post helpful consider giving it a "Thumbs Up."

[v-yutliu-msft]

Hi @Anonymous ,

Could you tell me what is your data source?

I assume that it is sharepoint list. 

Do you want the gallery displaying the [questionRemark] and its related [question]?

Are you sure you only need these two fields in the gallery?

If so, you do not need to use so complex formula.

Just using AddColumns function is enough.

Since you have a lookup field named [question]  which is related to the [question] field in QuestionsVerify, using "question.Value" in AnswersVerify could already represent the question value in QuestionsVerify.

So, I suggest you set the gallery's Items:

AddColumns(AnswersVerify,"question1",question.Value)

//question1's value will be the question value in QuestionVerify list.

Set the gallery's Title to question1, subtitle to questionRemark.

Then the gallery will display like your description.

Best regards,

[Anonymous]

Hi @v-yutliu-msft 

Thanks for the idea, but my data source is a Excel file in google drive.

As per the image I attached (Sorry for the unprofessional labeling), you can see that the gallery is only reading from one data source which is (AnswerVerify),  but I want it to grab data from 2 different data source which is (AnswerVerify) and (QuestionsVerify) together that has similar column which is either [questionID] or [question] to be used as unique key.

By doing this, let's say when I select a Parent/Row in the gallery, the 2 different forms shall change together based on the specified table.
If having 2 different table in one gallery is possible and succeeded, the data source gallery when gallery is selected should display something like:

Data Source : AnswerVerify,QuestionsVerify

I know there's a way and I saw some videos doing it but I just don't know how to work around it to make it possible. Hope you can help me out.

[v-yutliu-msft · Accepted solution]

Hi @Anonymous ,

I'm sorry that I thought your data source is sharepoint list.

Based on your screen shoot, do you want:
1)a gallery display the value of question and questionRemark?

2)if you select an item in the gallery, the right part will display an edit form of related QuestionsVerify record and an edit form of related AnswersVerify record?

If so, I do not think you need to display two table's data in the gallery, you just need to filter based on different data source for these two forms.

Since you already have question field in AnswersVerify table, there's no need to add other data.

Try this:
1)connect your app with these two tables: QuestionsVerify,AnswersVerify

2)set the gallery's Items:

AnswersVerify

choose question as Title, questionRemark as subtitle.

3)set the above form's data source: QuestionsVerify

 set its Item:

LookUp(QuestionsVerify,question=gallery1.Selected.question)

 4)set the below form's data source:AnswersVerify

 set its Item:

Gallery1.Selected

Then the gallery will display question data and questionRemark  data.

The above form will display related item of QuestionsVerify.

The below form will display related item of AnswersVerify.

Best regards,

[Anonymous]

Thanks a lot buddy, this really helped me out.