Forum Discussion

bhelou's avatar
bhelou
Responsive Resident
6 years ago
Solved

expressions that yield variant data-type

Hello Community ,

 

i made this formula and its makinf # error ( expressions that yeild variant data-type cannot be used to define calculated columns 

 

Employee_Name = IF(Employee_ID_Name[lookup_Name]="",Employee_ID_Name[EMPLOYEE_ID],Employee_ID_Name[lookup_Name])
 
* (Employee_ID_Name[lookup_Name] : is coming from a related formula and working
 
i have changed the lookup_name to ID the formula is working but when it is text its not 
I have changed the formula : replaced "" with blank() , didnt work 
I have changed the formula : replaced "" with 0, didnt work 
 
Thank you for help.
 
 

 

Need Help
  • v-xuding-msft's avatar
    v-xuding-msft
    6 years ago

    Hi bhelou ,

    I guess the column of EMPLOYEE_ID is number type and the lookup_Name is the text type. Your expression returns either number or text. But the data in calculated column need to be the same type. You could try using the function of FORMAT to return the EMPLOYEE_ID to text.

    Employee_Name =
IF (
    Employee_ID_Name[lookup_Name] = "",
    FORMAT ( Employee_ID_Name[EMPLOYEE_ID], "" ),
    Employee_ID_Name[lookup_Name]
)

     

1 Reply

  • v-xuding-msft's avatar
    v-xuding-msft
    Community Support
    6 years ago

    Hi bhelou ,

    I guess the column of EMPLOYEE_ID is number type and the lookup_Name is the text type. Your expression returns either number or text. But the data in calculated column need to be the same type. You could try using the function of FORMAT to return the EMPLOYEE_ID to text.

    Employee_Name =
IF (
    Employee_ID_Name[lookup_Name] = "",
    FORMAT ( Employee_ID_Name[EMPLOYEE_ID], "" ),
    Employee_ID_Name[lookup_Name]
)