Working with Lookup Columns to lists that preside in another site
My challenge! I am working with SharePoint Online. I have list A in a sub site which has lookup columns to 2 other lists B and C which are in different sites. These lookup columns are site columns and for completeness all sites are in he same site collection.
There are various now defunct SharePoint 2010 workflows that ran, sending out emails to and updating values, where the email names were pulled off the master lists B and C from list A.
I am in the process of re-producing the functionality using Power Automate and new PowerApps forms.
I have encountered the following behavior.
When building a flow on list A the lookup columns weren't visible
When creating a PowerApps form to create a column on list A that would be used to gather ID values for lists B and C I found that I can create combo boxes on list A in a PowerApps form to look up the values to select from lists B and C, writing these values back to list A using the Patch command was difficult because the Patch command would not recognise the site columns I am using for lists B and C (on list A)
As a test I created local lists in the same site containing the data I wanted to lookup, called lists D and E. I then created site columns to lists D and E, and added new columns to list A using these site columns.. When I modified the the Patch command on the form on list A it recognised the site columns for lists D and E, and importantly when I created a flow for list A I can access the site columns for list D and E
My conclusion is that working with lookup columns that are derived from site columns that are to lists that preside in another site to the current list, is very difficult to handle. This is OTB functionality going back to SP 2010 and worked well with SharePoint Designer workflows and InfoPath.
Has anyone been able to create PowerApps forms or PowerAutomate flows that reference lookup columns that are site columns derived form lists in other sites? If so I would be very grateful if you could share how because I may need to do a lot of re-engineering and before I start I want to make sure every option has been explored.