Forum Discussion

QuickBase9's avatar
QuickBase9
Qrew Cadet
8 years ago

Create a case-sensitive field for Salesforce data import

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 creat...
Data import and export
Tables and fields
EvanMartinez's avatar
EvanMartinez
Moderator
8 years ago
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. 
  • QuickBase9's avatar
    QuickBase9
    Qrew Cadet
    8 years ago
    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:)
  • QuickBaseCoachD's avatar
    QuickBaseCoachD
    Qrew Captain
    8 years ago
    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.
  • QuickBase9's avatar
    QuickBase9
    Qrew Cadet
    8 years ago
    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.
  • QuickBaseCoachD's avatar
    QuickBaseCoachD
    Qrew Captain
    8 years ago
    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 .
  • _anomDiebolt_'s avatar
    _anomDiebolt_
    Qrew Elite
    8 years ago
    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.
  • QuickBase9's avatar
    QuickBase9
    Qrew Cadet
    8 years ago
    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.
  • QuickBaseCoachD's avatar
    QuickBaseCoachD
    Qrew Captain
    8 years ago
    oK, well if the field does not needs to be unique, then there is no problem after all
    • RobertBridal's avatar
      RobertBridal
      Qrew Member
      3 years ago

      I know this is an old one, but did anyone ever find a good way around Quickbase's issue here?

      Until today, I had no idea QuickBase had this limitation.  I now have two salesforce IDs that are identical except for a single letter being a different case. In this case, searching in reports, running queries (IE, {6.EX.'abc'}), all broken on this one.

      Example:
      00300002000010A
      00300002000010a
      (these ARE NOT equal - no matter what you believe)

      Is there an actual solution here, or do I need to do yet another workaround in Quickbase?

      Anyone reading this in the future and as aggravated as I am now, here are the two options I'm looking at - they're both duct tape, as the first doesn't scale and the second one doesn't account for non-ASCII characters:

      - I already write the salesforce ID into a "Raw" field, then have a formula field that strips any characters after the 15th, and all functions use the formula field not the raw field - given this setup, I may alter the formula field to also rewrite one of the IDs that Quickbase faceplants on.  Bad part is I'd need to have an alignment report that alerts me when duplicates happen - which is not ideal.

      - I may do something such as convert the ID.  As all characters fit within the original ASCII character set, I'm thinking convert to ASCII value and store a parallel string using Hex - IE, an "A" would be a decimal 65 in ASCII, so in the parallel case sensitive field it would be written as "41".  A lowercase "a" would be decimal 97, so it would be written as "61".  This parallel formula field would have values that were always 30 characters in length (1 original char = 2 hex chars, and salesforce is 15 chars) would only be used for comparisons, with visual element always being the original form of the ID, not the duct-tape form.

      Quickbase - if you guys have this figured out finally, please let me know - until then, duct tape it is.

      Everyone else - hope you were able to find this before it caused any issues, like incorrect executive reports!