Create a case-sensitive field for Salesforce data import

  • 0
  • 1
  • Question
  • Updated 9 months ago
  • Answered
I'm pulling in data from Salesforce that I may need to update or link back to the Salesforce record later, and it turns out the unique identifier in SF is a case-sensitive string. Any way we can create a unique field in Quick Base that is also case sensitive? Anyone figured out a work around option? Thanks!
Photo of Leanne

Leanne

  • 250 Points 250 badge 2x thumb

Posted 10 months ago

  • 0
  • 1
Photo of Evan Martinez

Evan Martinez, Community Manager

  • 8,754 Points 5k badge 2x thumb
Hi Leanne,

There is a formula function that will take a text string or text field and return all the characters in it in upper case. It is Upper([field name]). So it would be possible to create a text field marked as Unique and then a formula field which converts any text into that field into all Upper case. That way you will have a field which matches your case sensitive needs. Alternatively Lower is the formula function if you want to return the text string is all lower case. I hope this is helpfu. 
Photo of Leanne

Leanne

  • 250 Points 250 badge 2x thumb
Hi Mark,
The length of the string stays the same at 15 but the use of letters, caps, numbers is unpredictable. The alteration always occurs in the last five characters, but that is the only consistency.
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 51,166 Points 50k badge 2x thumb
I think it can be done natively, but it would be a long but repetitive formula.

Can you give some examples of the string? I’m not really understanding what you mean by the alteration is in the last five characters .
Photo of Ⲇanom the ultimate (Dan Diebolt)

Ⲇanom the ultimate (Dan Diebolt), Champion

  • 26,352 Points 20k badge 2x thumb
I am totally confused by what you are trying to accomplish other than it deals with insuring that some of your data coming in to or out of QuickBase/Salesforce needs to conform to some type of pattern (upper/lower case, uniqueness, length, etc). Some sample data and more descriptive workflow and requirements would greatly help understand what the issue is.

I can't imagine a scenario where there isn't some type of script AND/OR native solution which deals with insuring your data conforms to some type of pattern whether it be on the input or output of either platform.
Photo of Leanne

Leanne

  • 250 Points 250 badge 2x thumb
Thank you both Mark and Dan, I think through Dan's inquisition he is correct, I am totally over thinking this!! Here is an example for anyone that is still interested:

Company A ..... Saleforce unique identifier = 9878958b5eeYtl
Company B .... SF UI = 9878958b5eEYtl
Company C .... SF UI = 9878958b5eeyTL
etc.

Thinking about this as a unique identifier was my mistake. I can just load this field into a straight text field, WITHOUT making it a unique field or a key field, and keep the case sensitivity as any data entry text field would maintain. Then pull it down into excel as needed, it keeps the case as entered.
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 51,166 Points 50k badge 2x thumb
oK, well if the field does not needs to be unique, then there is no problem after all