Current Location on Animal Table power FX

[Nickie_]

I am trying to have the current location to give the animal location detail whilst checking the current shelter status e.g.

If shelter status is in foster home to give me details from the foster claimer column

if shelter status is ready for adoption to give me details from the foster claimer column

if shelter status is claimed for adoption to give me details from the foster claimer column

if shelter status is in shelter to give me details from the initial shelter column

if shelter status is ready for foster to give me details from the initial shelter column

else to give me blank . 

Some body please help with the formula I have been stuck

[StineN · Accepted solution]

Hi! 

Is your ShelterStatus a choice column similar to this? The choice columns work a bit differently than regular text columns, because they have both a label and a value.

Here is my solution for the calculated column CurrentLocation:

Here is the full formula: 

Switch(ShelterStatus,
    'ShelterStatus (Animals)'.'In Shelter', InitialShelter.ShelterID,
    'ShelterStatus (Animals)'.'Ready for Foster', InitialShelter.ShelterID,
    'ShelterStatus (Animals)'.'In Foster Home', FosterClaimer.FosterID,
    'ShelterStatus (Animals)'.'Claimed for Adoption', FosterClaimer.FosterID,
    'ShelterStatus (Animals)'.'Ready for Adoption', FosterClaimer.FosterID,
    'ShelterStatus (Animals)'.Adopted, Blank())

I'm using a Switch() statement, where I check if the value of ShelterStatus matches "In Shelter", "Ready for Foster" etc., and then specifies what the result should be. To get the "In Shelter" option from the choice field, it's necessary to use the name of the choice set ('Shelterstatus (Animals)'), a dot and the label ('In Shelter'). To get the value of the initial shelter, I've used the name of the column (InitialShelter), and a dot to get the related column ShelterID from the Shelter table. In my table the InitialShelter column is a LookUp column. 

Here's some useful resources I found:

- https://learn.microsoft.com/en-us/training/modules/choice-columns-formulas/2-filter

- https://www.reddit.com/r/PowerApps/comments/ynwhy3/power_fx_formula_columns_in_dataverse_how_do_i/?r...

-  https://learn.microsoft.com/en-us/power-platform/power-fx/reference/function-if 

Hope this helps! Good luck 🙂 

[Nickie_]

Awesome!!! Thank you this helped me very much. 

[Grkm_A]

Hi,

The fx is working but while i am trying to upload the file for that column (CurrentLocation) there is a several "couldnt resolve" issues. Somebody faced with that kind of problem? 

"Couldn't resolve the guid for the lookup field: ga_fosterclaimer."

Formula is the same with the upper answer.

[StineN]

Hi! Is that error message related to the specific CurrentLocation column when you upload the file? Have you already uploaded the table with the foster family data, so that the rows can be accessed when uploading? When I uploaded the Animals table, I removed all of the rows in the CurrentLocations column, because they would be populated by the fx column either way. I guess that doesn't solve the problem, but it's a workaround at least. If that doesn't work, I think there must be something with how the lookup field FosterClaimer or the Foster table is set up. 

[Grkm_A]

Hi,

Foster and Shelter data already uploaded. And to this tables Animal Tables' columns are connected. (Inital Shelter -> Shelter and Foster Claimer -> Foster) 

But when i uploaded all rows that contains data throw an error. (For 25 rows) And i also tried clean all data for Current Location column but it didnt work either. ,

I couldnt find a solution

[StineN]

I'm not sure if this helps, but are you sure you're using the primary column as the lookup field? If you're using another column named "pu_fosterclaimer" that isn't the primary column of the Foster table, that might be the cause of the error.

In other words, when you create the tables you need to rename the primary column (It's automatically called 'Name'). If you've changed the display name afterwords, it doesn't always update the logical name. You can see the logical name of the column under Advanced options in the Edit pane. 

In my example the primary column of the Shelter table is ShelterID, and FosterID for the FosterFamilies table. This is used in the formula:

Switch(ShelterStatus,
    'ShelterStatus (Animals)'.'In Shelter', InitialShelter.ShelterID)

Here is my columns for the FosterFamilies table: 

 

Hope this helps!

[Grkm_A]

Hi,

Thanks for answer and your time. Foster and Shelter table have 2 columns that contains the IDs. (FosterIdentity and ShelterIdentity) My primary columns for those tables are FosterName and ShelterName. In a short way my ID columns are not primary ones. 

In the Animal table i have 2 lookup columns one is FosterClaimer lookup with Foster table and InitialShelter lookup with Shelter table.  The logical names seem to me ok. But i really don't understand the issue. Because when i check the table datas for both Foster and Shelter i have values in the spesific columns that formula should detect and get the correct data to CurrentLocation. 

I will try start all over again with the new tables at the end if i can not ffind a solution. 

Again thanks for your reply.

[StineN]

Hi,

Yeah, that's strange.. Have you checked the relationship in the 'Relationship' menu on the tables? Maybe there's something that could indicate the error there.

If not, maybe there's something wrong with the PowerFx formula that prevents the data from being imported? Maybe the relationship and the tables are ok, but the formula can't access the values for some reason? 

On another note - I used the model-driven app to import the data, to be able to map the columns correctly. Maybe you could try that, if you haven't already? 

[Oyin]

Hi @Grkm_A ,

I would make a suggestion about the table naming, just in case you choose to start over.

Don't name your tables 'Foster' and 'Shelter' so that you can rename your Primary Name columns FosterID and ShelterID instead of FosterName and ShelterName.

You could make the table names a combination of two words such as 'Fosterfamily' like what @Stine N has in her screenshot.