Set variables using lookup using data from two Sharepoint lists - error incompatibe types : Record, Record

[sab_hia]

Hoping someone can help with this please!

I'm building an app for our housing charity which enables people to do property reports.

When a user starts the app it shows them a list of properties in their location, based on a Sharepoint List which has 2 columns, Location and User. This works fine using:

     Set(varLocation,LookUp([@'User Locations'], Username=User().FullName, Location));

Once the property check questions are answered I need to write the results to a Sharepoint list for their specific location, and also to cards on a location-specific Trello Board. So I think I need to set variables for the Sharepoint list name, Trello Board ID and Trello List ID based on the user location. Maybe there is a better way?

I've set up another list, 'Location App Output', with columns for Location, TrelloBoardID, TrelloListID and SharepointMPRname (the name of the location-specific Sharepoint List for each location).

I assumed something like this would work to set the necessary variables for use in retrieving current data and with the Patch command for saving output.

   Set(varMPRListName,LookUp([@'Location App Output'], Location=Location,SharepointMPRname));

   Set(varTrelloBoardId,LookUp([@'Location App Output'], Location=Location,TrelloBoardID));

   Set(varTrelloListId,LookUp([@'Location App Output'], Location=Location,TrelloListID));

However, I get the following error with all 3 of these 'Set' commands:

Incompatible types for comparison. These types can't be compared: Record, Record

I've searched for a solution to no avail, and tried various changes to the syntax but nothing works.

Any advice please? Thanks in advance.

@RandyHayes your reply to the post 'PowerApps lookup function returns error: Incompatible types for comparison' seems to be relevant but I can't make it work in my context. I'm very new to Power Apps by the way!

[v-bofeng-msft]

Hi @sab_hia ,

Maybe you could try:

  Set(
     varMPRListName,
     LookUp(
        'Location App Output', 
        'Location App Output'[@Location].Value=varLocation.Value,
        SharepointMPRname
     )
);

Best Regards,

Bof

[sab_hia]

Thank you @v-bofeng-msft Bof. That appears to work - at least it doesn't give me any errors on that part of the code now. However, I have other errors I need to resolve before I can tell if it actually gives me the results I need. This has allowed me to progress though - much appreciated. Will mark as Solved once I'm sure the app is all working 🙂 

[sab_hia]

It appears that my next errors are occurring because I can't use the variables set above to do what I need to do! (c.f. Solved: PowerApps Filter - Table name as a variable - Power Platform Community (microsoft.com))

I was hoping to do things like this in a dropdown OnChange:

Set(varCombinedAddress,Address_DropDown1.SelectedText);
Set(varLatestDate,Last(SortByColumns(Filter([@varMPRListName], Property_x0020_Address=varCombinedAddress.Value), "Inspection_x0020_Date",Ascending)).Inspection_x0020_Date);
ClearCollect(varLatestMPRCollection, Last(SortByColumns(Filter([@varMPRListName], Property_x0020_Address=varCombinedAddress.Value),
"Inspection_x0020_Date",Ascending)))

And similar for other functions, e.g. Patch. However, this throws up errors. If I replace the variable varMPRListName with an actual single Sharepoint list the errors disappear. However, this severely limits the scalability of the app in terms of locations. It's frustrating and I'm not sure what to do now! The Sharepoint lists have to be separate as they are all on different location-specific Sharepoint sites. Perhaps the only way is to build a separate app for each location, but this seems a cumbersome approach. Any advice welcome!

[v-bofeng-msft · Accepted solution]

Hi @sab_hia ,

The point is that varMPRListName is a single value instead of  a table.

Please check these links:
Filter, Search, and LookUp functions in Power Apps 

Best Regards,

Bof