Skip to main content
Question

Insert a ; or space into formula

  • March 15, 2018
  • 0 replies
  • 63 views
Formula reads:
var Text QTA = Part(UserListToNames([Quality Team]), 1, ";"); 
var Text QTB = Part(UserListToNames([Quality Team]), 2, ";");
var Text QTC = Part(UserListToNames([Quality Team]), 3, ";");
var Text QTD = Part(UserListToNames([Quality Team]), 4, ";");
var Text QTE = Part(UserListToNames([Quality Team]), 5, ";");

If (not Contains([Quality Approval], $QTA & "] Approved") and not Contains([Quality Approval], $QTA & "] Rejected"), $QTA) &
If (not Contains([Quality Approval], $QTB & "] Approved") and not Contains([Quality Approval], $QTB & "] Rejected"), $QTB) &
If (not Contains([Quality Approval], $QTC & "] Approved") and not Contains([Quality Approval], $QTC & "] Rejected"), $QTC) &


It comes out with all the names jumbled together.  
This topic has been closed for replies.

Can we see an example of the current result that you do not like and how you want it to appear?
  • ArchiveUser
  • Author
  • Registered
  • March 15, 2018
Appears: Waiting onButch Glenn Juan Caballero Matthew LensmireMawuena Gnamavo 

Wanting: Waiting on: Butch Glenn; Juan Caballero; Matthew Lensmire; Mawuena Gnamavo

I tried: 
If (not Contains([Quality Approval], $QTA & "] Approved") and not Contains([Quality Approval], $QTA & "] Rejected"), $QTA & ";") &

But the ";" appears even if the user is not there;  In other words it appears as:
Waiting on: Butch Glenn; Juan Caballero; Matthew Lensmire; Mawuena Gnamavo;;;;;
Ah, so what you want to use if the List function.  The list function separates a list of text phrases with a delimiter that you set, but is smart enough not to add the separator of there is nothing to separate.

So you want your separator to be "; "

List("; ",

If (not Contains([Quality Approval], $QTA & "] Approved") and not Contains([Quality Approval], $QTA & "] Rejected"), $QTA,

If (not Contains([Quality Approval], $QTB & "] Approved") and not Contains([Quality Approval], $QTB & "] Rejected"), $QTB),

If (not Contains([Quality Approval], $QTC & "] Approved") and not Contains([Quality Approval], $QTC & "] Rejected"), $QTC),


etc

)  // close the List ( open bracket)
The individual elements of the List need to be separated with a comma and not an &

see the comma at the end of each line





List("; ",
If (not Contains([Quality Approval], $QTA & "] Approved") and not Contains([Quality Approval], $QTA & "] Rejected"), $QTA) ,
If (not Contains([Quality Approval], $QTB & "] Approved") and not Contains([Quality Approval], $QTB & "] Rejected"), $QTB) ,
If (not Contains([Quality Approval], $QTC & "] Approved") and not Contains([Quality Approval], $QTC & "] Rejected"), $QTC) ,
If (not Contains([Quality Approval], $QTD & "] Approved") and not Contains([Quality Approval], $QTD & "] Rejected"), $QTD) ,
  • ArchiveUser
  • Author
  • Registered
  • March 16, 2018
Great!  Thanks!
Terms & ConditionsAccessibility statement