Power up challenge - What is wrong with the Fx formular

[Oluomo1]

I want to populate "Current Location" with Values from "Foster Claimer", and if "Foster Claimer" is blank, it should pick value from "Initial Location". What is wrong with my fx formular below

fx= IF(IsBlank('Foster Claimer'),'Initial Location', 'Foster Claimer')

[BCBuizer]

Hi @Oluomo1 ,

Power Fx is case sensitive, meaning you need to be very precise when it comes to capitals. In this case the second character of the IF function should be a lower case:

If(IsBlank('Foster Claimer'),'Initial Location', 'Foster Claimer')

An extra tip: especially for this scenario where you need to have the first non blank value from two references, you can use the Coalesce function:

Coalesce('Foster Claimer', 'Initial Location')

[Oluomo1]

@BCBuizer, thanks for your response, the "coalesce" is not even coming up at all. Please look at the formular in the screen shot attached to see if you know what I have done wrong

[BCBuizer]

Hi  @Oluomo1 ,

From the screenshot you are sharing it has become clear you are using the formula for a Formula column in Dataverse. Since that is still a preview feature at this point, not all functions are available: Work with Dataverse formula columns - Power Apps | Microsoft Learn. Had you try to use this in a canvas app, it would have worked.

The error you receive is probably caused because either or both of the referenced columns are LookUp columns. For those, you have to select a column within the returned record that contains a text or number, for instance:

If(IsBlank('Foster Claimer'),'Initial Location'.LocationName, 'Foster Claimer'.ClaimerName)

In the above example you probably need to change the LocationName and ClaimerName identifiers with the actual names of the columns you have and want to use.

[jdmoze2 · Accepted solution]

Hi @Oluomo1 , I was able to get mine to work by using this formula:

If(IsBlank('Date of foster'), 'Initial Shelter'.'Shelter ID', If(IsBlank('Date of Adoption'), 'Foster Claimer'.'FosterID', 'Adopter'))

Basically, it's saying that if the animal's "Date of foster" column is blank within the current table, it means the animal is still being sheltered within the "Initial Shelter".  Therefore, we want to populate the current table's "Current Location" column with the "Shelter ID" tied to the "Initial Shelter".  To achieve that, we need to understand that the current table's "Initial Shelter" column is a Lookup column that maps to another table's column titled "Shelter ID".  If the initial assessment is true -- meaning the "Date of foster" column is blank, then we should observe the population of the Initial Shelter's Shelter ID through this Lookup.

However, if the "Date of foster" column within the current table is not blank, our formula progresses to the "else" logic.  In this case, this next assessment is another "If" operation.  As you can probably now better understand, this second "If" assessment determines if the current table's "Date of Adoption" column is blank.  If it is, we now know that the animal is currently being fostered.  As such, we go through an identical Lookup as previously described for the first "If" statement.  But in this case, the Lookup is being performed through the current table's "Foster Claimer" Lookup column that can then retrieve the value from another table's corresponding "FosterID" column.

However, if both of the previous "If" assessments were not true, we enter the final "else" condition in which we realize the animal must be adopted because the "Date of foster" and "Date of Adoption" columns within the current table have both been populated.  As such, our "else" statement returns results from the current table's value captured in the "Adopter" column.

And then we reach the end...  We simply type in "))" twice to close the formula.  We then click "Save" and return to the table to verify success or failure by observing if data was automatically populated into our "Current Location" column.

I truly hope this helps!  Please don't hesitate to reply if it doesn't.  Super happy to help you troubleshoot further.

Wishing you all the best!

Justin

P.S.  If this works for you, you'll note that some of the "Current Location" entries are still blank.  That's because the Excel file we were given has several blank fields for "Adopter" even though the animal had been listed as "Adopted" and also had a date of adoption inserted within the "Date of Adoption" column.  But this shouldn't take away from the functionality of the formula and its use for future animal sheltering/fostering/adoption entries once you build the apps in subsequent portions of the challenge.