cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
RobynPr
Level: Powered On

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
RobynPr
Level: Powered On

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
RobynPr
Level: Powered On

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

Community Support Team
Community Support Team

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.
RobynPr
Level: Powered On

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

RobynPr
Level: Powered On

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
thirdimage

Power Automate Community User Group Member Badge

Fill out a quick form to claim your user group badge now!

sixthImage

Power Platform World Tour

Find out where you can attend!

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

fifthimage

Microsoft Learn

Learn how to build the business apps that you need.

Top Kudoed Authors (Last 30 Days)
Users online (5,411)