Display Shelter Name - Model-driven app Requirement

[Corry]

Hi everyone,

I am stumped on figuring out how to display the name of shelters in my 'Animal' table view.
The first requirement states "View a list of animals displaying name, type, shelter status and shelter name".

Shelter name doesn't exist in the 'Animal table final' Excel data but does exist in the 'Shelter table final' Excel data.
I am trying to display the 'Shelter Name' in a new column in the 'Animal' table by checking if the value in my 'Current Locations' column in my 'Animal' table matches the 'ShelterID' from the 'Animal Shelter' table. If it does match 'ShelterID', display the 'ShelterName' from the 'Animal Shelter' table.

I tried this as a PowerFx formular column called 'Shelter Name', but I couldn't get the attributes I need.

I also added the 'ShelterName' column as a related column in the 'Active Animals' table view for the 'Animal' table, but no results appear. See Animal_Table_View_Related_ShelterName_Column.png

My 'Current Locations' column is a PowerFx formula column with the following code:

Switch('Shelter Status',
'Shelter Status (Animals)'.'In Shelter','Initial Shelter'.ShelterID,
'Shelter Status (Animals)'.'Ready for Foster','Initial Shelter'.ShelterID,
'Shelter Status (Animals)'.'In Foster Home','Foster Claimer'.FosterID,
'Shelter Status (Animals)'.'Claimed for Adoption','Foster Claimer'.FosterID,
'Shelter Status (Animals)'.'Ready for Adoption','Foster Claimer'.FosterID,
'Shelter Status (Animals)'.Adopted,Blank())

Although I have followed the learning material, this one has gotten me stuck. I know there is a way, I just can't see it.

I would really appreciate guidance on this so that I can move forward with the other requirements. 

[CN_05]

Hello Corry

I am stuck here too, not sure how to go about it

[Corry]

So, after reading many articles to try to understand how Dataverse Column Formulas work, I am still just as confused. Work with formula columns 

So naturally, I had a lengthy and arduous conversation with ChatGPT, constantly telling it that the functions it was giving me were not supported in Dataverse Column Formulas.

But I persevered and it finally spat out something that is at least a temporary fix for now. (Hard Coded values!)

Here is my approach to display the Shelter Names in my Main Information Form for the Animal Table.

1. Ensure there is a N:1 Relationship between the Animal table and the Shelter table.
   1. My relationship is Many Animals to 1 Shelter. (Relationship Name: cr0f4_animals_ShelterID_cr0f4_animalshelt)
2. Create a new formula column in the Animal Table called 'Shelter Name'.
3. Add this formula to hard code the values.
   1. If(
      IsBlank('Current Location'),
      "", // Return empty string if Current Location is blank
      Switch(
      'Current Location',
      "S-01","Hooves Haven Rescue Clinic",
      "S-02","Companion Critter Retreat",
      "S-03","Pawsitive Steps Shelter",
      "S-04","Happy Tails Haven",
      "S-05","Loving Paws Shelter",
      ""
      )
      )
4. Save, Publish, and Test.
5. Add newly created Shelter Name column to view.
6. Save, Publish, and Test.

Unfortunately, this is not the ideal solution as it would require manually updating the formula as more Shelters are added, but I have put too many hours into trying to resolve this a proper way.

Side note, I did try a different formula that Dataverse accepted but it didn't produce the desired result.

If(
IsBlank('Current Location'),
"", // Return empty string if Current Location is blank
Switch(
'Current Location',
'Current Location', ShelterID.ShelterName,
""
)
)

[Esraa_k]

heey,

In case the current location is empty or it FH- , what is the shelter name will be

[Corry]

Hi @Esraa_k,

In the formula I have used, if Current Location is empty this part of the formula returns an empty string i.e., nothing.

IsBlank('Current Location'),
"", // Return empty string if Current Location is blank

For FH-, I’m not showing these values with my formula as these are not shelters, they are foster homes.

As my column is called Shelter Name, I felt it would be confusing to show non-shelter location names.

I hope this explains my approach.

[Esraa_k]

Okay, in case the current location was empty of FH the shelter name will not be displayed, it's means if the shelter staff tries to filter with the shelter name some animals will not be displayed because their current location was empty of FH.

So why you didn't use Intiail shelter column?

[StineN · Accepted solution]

Hello! 

I solved this by adding a new column in the Animals table called ShelterName, where I used the lookup column InitialShelter to get the name of the shelter. This will get the name of the initial shelter that the animal belongs to, and doesn't change even if it's moved to a foster family or adopted. In my opinion that should fulfill the requirements of showing animals by shelter name. 

- In the Shelter table, there is a coulmn called ShelterName

- In the Animals table, InitialShelter is a lookup on the Shelters table 

- In the Power Fx formula on ShelterName in the Animals table, I access the name column from the related table with this formula: InitialShelter.ShelterName

I then added this column to the model-driven app. 

[Steve50244]

I too, read Documentation and watched Videos, and tried different ... Thank you for your solution. At least if it works, it is better than banging my head and never finishing this.

[Corry]

In my mind, initial Shelter wasn't an accurate representation of where the animal currently is in respect to shelters. But since looking at the data, I have noticed that if an animal is in a shelter, the initial shelter and the current location will be the same.

I have taken on the approach that @StineN has posted here in this thread by using the formula InitialShelter.ShelterName.

Thank you @Esraa_k for being inquisitive.

[Corry]

Thank you @StineN

I think I got jumbled up in my way of thinking that made this more complicated than it needed to be. I appreciate your contribution and love the simplicity in your approach.

I have taken this on board and using it in my solution.