Create a case-sensitive field for Salesforce data import

  • 0
  • 1
  • Question
  • Updated 1 year 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

  • 354 Points 250 badge 2x thumb

Posted 1 year ago

  • 0
  • 1
Photo of Evan Martinez

Evan Martinez, Community Manager

  • 11,406 Points 10k 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

  • 354 Points 250 badge 2x thumb
Hi Evan, this is actually the opposite of what I need the field to do. For example, a unique identifier in SF can be: TG68 or Tg68 or tg68 - that would show three separate unique records because it is case sensitive. So I need to maintain that cap/lower case structure if I want to maintain the unique identifier. I'll probably just have to work around this by keeping the unique identifier in excel and pulling back into excel the QB identifier, but I thought I'd ask just in case:)
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 65,522 Points 50k badge 2x thumb
Leanne. You can make a formula field in the format TG68UU.

The UU indicated that TG us Upper Upper.

Or Tg68UL

The U L indicates if the alpha is upper or lower. Then the key would be unique.

There us in fact a way to have a Key field in Quick Base be a formula field.

A formula in QuickBase can differentiate between upper a lower case. For example

The following formula would be false

A=a

So think this is east to do, especially if the length if the Salesforce Key is short.

How long is the Salesforce key field and how predictable is the format in terms of the placement of letters and numbers.
Photo of Leanne

Leanne

  • 354 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

  • 65,522 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

  • 30,064 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

  • 354 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

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