cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
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

 

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
Advocate I
Advocate I

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

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.

 

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

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
News & Announcements

Community Blog

Stay up tp date on the latest blogs and activities in the community News & Announcements.

Power Apps Community Call

Power Apps Community Call- January

Mark your calendars and join us for the next Power Apps Community Call on January 20th, 8a PST

PP Bootcamp Carousel

Global Power Platform Bootcamp

Dive into the Power Platform stack with hands-on sessions and labs, virtually delivered to you by experts and community leaders.

secondImage

Power Platform Community Conference On Demand

Watch Nick Doelman's session from the 2020 Power Platform Community Conference on demand!

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