I need to reference a field on the User Object but it just uses me as the only user, I need to search all users

Question

I'm trying to compare two fields and if they are both satisfied it should spit out the name of the user. Except this formula is only using me as the single user reference when I need the formula to search through all users to find a match. How do I get the formula to look through all available users instead of just me. Is this possible do I need to create a flow/process?

IF(
  AND(
      Account_Segment__c = $User.Assigned_Segment__c,
      Territory__c = $User.Assigned_Territory__c),
      $User.FullName__c, 
     "IDK")

Answer 1

Formulas can only reference:

• the current object
• lookup fields in parent (grand, great-grand, ...) objects
• environment variables (like $User)
• hierarchical custom settings (also based on current user/profile/org)
• custom metadata

To look at all users requires querying and that can be done (server-side) in either Flow or Apex; or using LWC (client-side)

Answer 2

Agreeing with copredy but looking at a declarative solution, a Flow can absolutely do this for you if you are an Admin without developer knowledge or access.

Creating a Record-Trigger Flow to fire on Account create and update, you can use a single decision component with 2 outcomes where each outcome has an assignment component. Unaware of your business use case end to end, the trigger conditions on this flow is open ended. I advise you set one or more conditions for when this flow runs.

Decision Component

First would be a field match i.e Account_Segment__c = $User.Assigned_Segment__c equals Territory__c = $User.Assigned_Territory__c with the other being no field match i.e Account_Segment__c = $User.Assigned_Segment__c does not equal Territory__c = $User.Assigned_Territory__c.

Matched Assignment Component Your new custom field should be set to a concatenated version of the User name in this Component i.e {!$Account.CustomField__c} equals {!$User.FirstName} {!$User.LastName}.

Unmatched Assignment Component Your new custom field should be set to the I don't know (IDK) acronym i.e {!$Account.CustomField__c} equals IDK.