cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Advocate I
Advocate I

Can't seem to join two sharepoint lists with one having a lookup field in my query

Hi

 

I have created a sharepoint list (Student ILP) that has two lookup fields (ICanID & StudentID) that "join" two shrepoint lists ('Curriculum I Can' & 'Student' respectively).

 

I was able to use the Patch function to correctly write to the 'Student ILP' sharepoint list and everything looks good. Here is my patch function:-

 

Patch(
    'Student ILP',
    Defaults('Student ILP'),
    { I_x0020_Can: {'@odata.type' : "#Microsoft.Azure.Connectors.SharePoint.SPListExpandedReference", Value: "",Id: ThisItem.ID},
      Student: {'@odata.type' : "#Microsoft.Azure.Connectors.SharePoint.SPListExpandedReference", Value: "",Id: ComboBoxStudent.Selected.ID}})

I want to be able to create a chart on my screen that aggregates some counts and the like. But when I try to join the Student ILP table to the 'Curriculum I Can' table I continually get an error. I've tried lots of combinations, with and without the odata.type syntax but none seem to work. Here are some of my efforts, all not working :

AddColumns('Student ILP',"ICanRecord", LookUp('Curriculum I Can', ID = 'Student ILP'.ICanID_x003a_ID))

AddColumns('Student ILP',"ICanRecord", LookUp('Curriculum I Can', ID = Value('Student ILP'.ICanID_x003a_ID)))

AddColumns('Student ILP',"ICanRecord", LookUp('Curriculum I Can', 'Curriculum I Can'.ID =  Value('Student ILP'.I_x0020_Can.ID)))

... and the list goes on ...

 

'Student ILP' Settings

2018-08-18_1104.png

That ICanID column is the link to the 'Curriculum I Can' table as per below:-

2018-08-18_1112.png

 

It's a bit of a long post but I have tried many combinations, also using some odata.type syntax but all to no avail.

 

Any ideas would be greatly appreciated.

 

Robyn

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Advocate I
Advocate I

Re: Can't seem to join two sharepoint lists with one having a lookup field in my query

 

Hi @v-micsh-msft

 

I tried your suggestion on my test files and found that the .value did not work. But I tried the Id as below and it worked.  Yaay.

 

 

AddColumns(tablerelationship, "table1record", LookUp(table1, ID = table1ID.Id), "table2record", LookUp(table2, ID = table2ID.Id))

 

I was then able to show the table1 and table2 title fields as below:-

 

ThisItem.table1record.Title

ThisItem.table2record.Title

 

Thanks for that. BUT 😞

 

But going back to my original problem using my sharepoint lists, applying the same principle above I changed the code to be:-

 

 

AddColumns('Student ILP', "ICanRecord", LookUp('Curriculum I Can', ID = ICanID.Id))

but ICanID is not recognised by powerapps as a valid field, even though as per my original post it is the lookup field's column name. I created a new app over the 'Student ILP' and 'Curriculum I Can' sharepoint lists so there were no other distractions to test this and again it did not work.

 

Is it possible that my sharepoint lists have somehow become corrupt. Not sure what to try now.

 

Regards

Robyn

View solution in original post

4 REPLIES 4
Highlighted
Advocate I
Advocate I

Re: Can't seem to join two sharepoint lists with one having a lookup field in my query

Hi

 

Further to the problem above I have reproduced the problem to its bare essentials below with still no luck.

 

table1 (ID, title)

table2 (ID, title)

tablerelationship (table1ID (lookup field on table1.ID), table2ID (lookup field on table2.ID, title)

 

Here is the query I've attempted without any luck

AddColumns(tablerelationship, "table1record", LookUp(table1, table1.ID = tablerelationship.table1ID))

The = has the little red line under it indicating "Invalid Argument Type". Though if I wrap a Value() around tablerelationship.table1ID I get a bigger red line saying "the function Value has some invalid arguments" which leads me to think that table1ID i not valid in the first place.

 

Any suggestions would be appreciated.

 

Regards

 

Robyn

Highlighted
Community Support
Community Support

Re: Can't seem to join two sharepoint lists with one having a lookup field in my query

Hi @RobynPr,

 

Please take a try with the code below:

AddColumns('Student ILP',"ICanRecord", LookUp('Curriculum I Can', ID = ICanID.Value))

See if doing it in this way could work.

 

Regards,

Michael

Community Support Team _ Michael Shao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Highlighted
Advocate I
Advocate I

Re: Can't seem to join two sharepoint lists with one having a lookup field in my query

 

Hi @v-micsh-msft

 

I tried your suggestion on my test files and found that the .value did not work. But I tried the Id as below and it worked.  Yaay.

 

 

AddColumns(tablerelationship, "table1record", LookUp(table1, ID = table1ID.Id), "table2record", LookUp(table2, ID = table2ID.Id))

 

I was then able to show the table1 and table2 title fields as below:-

 

ThisItem.table1record.Title

ThisItem.table2record.Title

 

Thanks for that. BUT 😞

 

But going back to my original problem using my sharepoint lists, applying the same principle above I changed the code to be:-

 

 

AddColumns('Student ILP', "ICanRecord", LookUp('Curriculum I Can', ID = ICanID.Id))

but ICanID is not recognised by powerapps as a valid field, even though as per my original post it is the lookup field's column name. I created a new app over the 'Student ILP' and 'Curriculum I Can' sharepoint lists so there were no other distractions to test this and again it did not work.

 

Is it possible that my sharepoint lists have somehow become corrupt. Not sure what to try now.

 

Regards

Robyn

View solution in original post

Highlighted
Advocate I
Advocate I

Re: Can't seem to join two sharepoint lists with one having a lookup field in my query

After skyping powerapps support the concluion is that the sharepoint list has somehow become corrupted which causes powerapps not to recognise my lookup fields for some unknown reason.

 

Contact sharepoint support! I'll just create new sharepoint lists and migrate the data.

 

But hopefully the way to write the join (as demonstrated in the test file code above) comes in handy for others.

 

Cheers

Helpful resources

Announcements
Ignite

Microsoft Ignite

This will be a conference that you do not want to miss!

secondImage

New Return to Workplace

Reopen responsibly, monitor intelligently, and protect continuously with solutions for a safer work environment.

August 2020 CYST Challenge

Check out the winners of the recent 'Can You Solve These?' community challenge!

Experience what’s next for Power Apps

Join us for an in-depth look at the new Power Apps features and capabilities at the free Microsoft Business Applications Launch Event.

Check this Out

Helpful information

Featuring samples like Return to the Workplace and Emergency Response Applications

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Top Solution Authors
Top Kudoed Authors
Users online (3,418)