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

[RobynPr]

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

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

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

[RobynPr]

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

[v-micsh-msft]

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

[RobynPr · Accepted solution]

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

[RobynPr]

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