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
Solved! Go to Solution.
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
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
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
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
Stay up tp date on the latest blogs and activities in the community News & Announcements.
Mark your calendars and join us for the next Power Apps Community Call on January 20th, 8a PST
Dive into the Power Platform stack with hands-on sessions and labs, virtually delivered to you by experts and community leaders.
User | Count |
---|---|
207 | |
193 | |
82 | |
57 | |
38 |
User | Count |
---|---|
299 | |
247 | |
119 | |
83 | |
55 |