Multi-table Lookups / Polymorphic Lookup - Animal Table [Current Location] Column

[Anton-X]

Hi,

From the provided spreadsheets, I noticed the [Current Location] column in the Animal table has values from two different source tables.

The values are from both the Shelters table [ShelterID] and Foster Family table [FosterID].

Is it a requirement to create a multi-lookup / polymorphic lookup for this? Or will a single line of text column suffice.

https://learn.microsoft.com/en-us/power-apps/developer/data-platform/webapi/multitable-lookup 

See column image below:

[bensnow]

I ended up not including this field and having a calculated field that just says '@Shelter,@Foster' 

I could find no way to combine these.

[Anton-X]

From what I've read, it's possible to have two lookups from one column in Dataverse. It was a new feature a few years ago.

But, it can only done using the API via code. Microsoft hasn't built this feature into the UI yet for us no coders.

From some research, though, there is a no code workaround option called XrmTool. Worth exploring.

https://www.xrmtoolbox.com/plugins/MscrmTools.PolymorphicLookupCreator/ 

[Anton-X]

Well, it's confirmed. The xrmtoolbox works. For the 'Current Location' dropdown, I was able to combine the Foster Family and the Shelters in a single dropdown list.

It was tricky to figure it all out. It also added some complexity in the canvas app. You have to use IsType and AsType functions to deal with the fact that your referencing or surfacing column values from two different columns. 

[mcofuzor]

Hi Anton, please can you give me more details on how you were able to look up both table

[Anton-X]

Sure @mcofuzor ,

I strongly suggest trying this for the first time by creating a test solution and test tables, simulating your animal shelter setup. It's just a weird concept to get your head around. And, you may want to try it a few times first before committing to it.

Also important to point out that creating the polymorphic lookup column using this method is 100% no code. I suspect the creator of the xrmToolBox likely made use of the api under the hood though. The tool just feels like an extension of Power Apps.

First, you'll need to download the xrmToolbox. I'm pretty sure I didn't need to install it, instead just run the .exe file from the directory you saved it to.

Within the xrmToolBox, you'll be using a specific tool called 'PolyMorphicLookupCreator'

You'll use the xrmToolbox to connect to your Power Apps Solution, and will use your Power Up Microsoft login credentials.

The Polymorphic lookup creator will do the heavy lifting for you by creating the polymorphic column. It's not created from the Power Apps side.

You pick the table you want the column created in, and then pick the related tables. For example, 'Animals' as the main table, and 'FosterFamilies' and 'Shelters' as the related tables.

As soon as you click the 'Create Polymorphic Lookup' button as seen above, a column will be created in your Dataverse solution called 'Current Location'.

Linn Zaw Win has a good explanation of the tool's interface.

I'm pretty sure when the polymorphic column is created it will utilize the Primary Name columns for the choice value names. So that's something to lookout for. That will depend how you set things up in Power Apps ahead of time.

Once the column is created, it will look like a regular lookup column in your column list. The difference being it's looking up two columns from two different tables when you use it.

Below is a list of tutorials I collected while learning about this type of column:

https://learn.microsoft.com/en-us/power-apps/maker/canvas-apps/working-with-references 

https://thepoweraddict.com/how-to-work-with-polymorphic-lookups-in-dataverse/ 

https://learn.microsoft.com/en-us/power-apps/developer/data-platform/webapi/multitable-lookup 

Polymorphic Lookup in Canvas App (youtube)

Tried n' Tested: Embedded Canvas App Part 2 - Gallery and Polymorphic Lookup (youtube.com) 

Multi-table lookups in Microsoft Dataverse - YouTube 

Hope that helps get you started.

Anton

[MorganG]

@bensnow, how did you do this?

[bensnow]

I didn't.

The polymorphic control mentioned by @Anton-X is very impressive but far outside the scope of what the

powerup program is asking for.

Instead, I just used the status to control the text of what appeared in that column, so is now just a text field and not linked.

[MorganG]

Hi @bensnow, I don't know what you mean by using the status to control the text. I really need a ELI5 version with pictures if you could. 

Even to create two separate columns in the Animals table for current shelter or current foster, I can't create lookups to their respective tables. 

It seems that in one table, only one column can be a lookup, and for me that is Initial Shelter.  But it makes sense to click on the current shelter/foster information and get their info. I tried watching the power fx video that this program recommended for the location fields, but honestly power fx is just so over my head and I could not comprehend it.