Location Power FX

[MichaelVaughan]

Evening all,

I'm hoping for assistance with a Power FX formula I'm trying to create for my dataset. I need to determine the "Current Location" based on the values in specific columns, and I'm struggling to get the formula right.

Here’s what I need the formula to do:

• Shelter Status: This is a choice field that includes several possible statuses.
• Initial Shelter: This column is with the initial shelter assigned
• Foster Claimer: This column records the name of the Foster claimer's id

The "Current Location" should be determined as follows:

1. If the "Shelter Status" is In Adopted, Ready for Adoption, In Foster Home, or Claimed for Adoption, the "Current Location" should return the value in the "Foster Claimer" column.
2. If the "Shelter Status" is Ready for Foster or In Shelter, the "Current Location" should return the value in the "Initial Shelter" column.

I'm quite worried that if I spend much more time on this, I won't be able to finish the challenge. Any guidance or examples on how to write this formula in Power FX would be greatly appreciated! Thank you in advance for your help!

Best regards,

Michael

[MikeCVaughan]

Hello everyone! 😊

I apologise I am using a different account, but I’ve been experimenting with various approaches. After some reflection last night, I decided to work with date fields as it looks like it more difficult working with choice fields, which is what the 'Shelter Status' field is. However, I’m still encountering error messages.

Here’s my latest attempts:

If(

    IsBlank('Date of Adoption'),

    If(

        IsBlank('Date of Foster'),

        'Initial Shelter',

        'Foster Claimer'

    ),

    'Foster Claimer'

)

 However, I am still getting an syntax error:  The result type Void is not supported in formula columns. 

Any help would be greatly appreciate

[jdmoze2 · Accepted solution]

Hi @MichaelVaughan  and @MikeCVaughan .  I posted this in a separate thread a minute ago, but wanted to share here as well since it's the same issue.

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.

[MichaelVaughan]

Hi Justin, 

Thank you, you are awesome, this worked brilliantly!

Many thanks,

Michael

[Ignacio-G]

Marvelous explanation @jdmoze2 ! Thanks !