Fetch unique 3 character code

Random != Unique.

Very curious as to why you would need to do this but here is some code to generate a string variable length with random characters. Not unique though. You should look into UUIDs if you want to achieve this.

a!localVariables(
  local!character: "A",
  local!numberOfCharactersToAppend: 2,
  local!allCharacters: "ABCDEFGHIJKLMNOPQRSTUVWXYZ",
  local!charactersToAppend: a!forEach(
    items: enumerate(local!numberOfCharactersToAppend),
    expression: a!localVariables(
      local!randomIndex: rounddown((rand() * 26 + 1), 0),
      mid(
        local!allCharacters,
        local!randomIndex,
        1
      )
    )
  ),
  concat(
    local!character,
    joinarray(local!charactersToAppend, "")
  )
)

Thanks all for the response. venkat Avuluri If i pass "AAZ" and 2. its returning "AA[" and "AA\" where it should be "ABA" and "ABB"

Yup it's gonna give you that .I just pasted sample code how it needs be done 

Mathieu Drouin said:

Very curious as to why you would need to do this

Same here ,Can I know why you need this in the first place . We can suggest alternatives 

When i add new records to the editable grid, it saves in database with generated unique code in a column. If i create 3 new rows, it needs 3 new codes to be generated to assign it to each row. There may situations where end user can delete already added row and at that time we need to recreate codes and assign to each row accordingly.

Are you sure this is a good way to solve the problem of primary key values?

shivas3219 said:

it saves in database with generated unique code in a column

In this case we can suggest multiple approaches. The approach you mentioned , I don't suggest it for various reasons  .

• One would be incremental number (simple approach). 
• Generating UUID 
• Or appending timestamp to a Application prefix or something like that(It's going to be unique every time) , the list goes on

Stefan Helzle This is not a primary key but unique code to identify the record and some other stuff.  I would have created a dictionary of unique 3 char code but it may degrade performance.

Sorry, but this will generate conflicting values as soon as multiple users create rows. There is a good reason for why we use the database to create these values.

For what purpose do you need these values? And why not just use the numeric primary key values from the database?

There will be only user who would be updating it for now. I agree that it may create some conflicting values with multiple users. This code is being used in mapping to other tables.

There will be only user who would be updating it for now. I agree that it may create some conflicting values with multiple users. This code is being used in mapping to other tables.

Well ... then, good luck with this design.

 Stefan Helzle  could you please suggest how to get number of unique codes I need, using above design gives me values till AAZ, but I need further codes for my requirement

Did you consider to let the database create the integer primary key, and then derive your alphabet code from this? Then you are sure to never have any duplicates. This needs a transformation from the decimal numeric system into a system based on the number 26. You can then store this code in a separate field.

I general I doubt that this is a good design decision, but that's up to you to decide.

For the implementation, you might want to use the reduce() function. Check my blog post for how to implement such algorithms in Appian: appian.rocks/.../

I asked GPT for how to do that:

To transform a number from the decimal system (base 10) into a numeric system using the letters A-Z (which can be interpreted as a base-26 system), you can follow these steps:

1. **Understand the Mapping**: In this system, you can map the numbers as follows:
- 1 = A
- 2 = B
- 3 = C
- ...
- 26 = Z
- 27 = AA
- 28 = AB
- and so on.

2. **Conversion Process**:
- Subtract 1 from the decimal number to adjust for zero-based indexing.
- Divide the number by 26.
- The remainder will give you the letter (0 corresponds to A, 1 to B, ..., 25 to Z).
- Continue dividing the quotient by 26 until it reaches 0.
- Collect the letters in reverse order.

### Example:
Let's convert the decimal number 28 to this system.

1. **Initial Number**: 28
2. **Subtract 1**: 28 - 1 = 27
3. **Divide by 26**:
- 27 ÷ 26 = 1 (Quotient)
- Remainder = 27 % 26 = 1 (which corresponds to A)
4. **Next Step**:
- Now take the quotient (1).
- 1 - 1 = 0 (stop here since the quotient is now 0).
- 0 ÷ 26 = 0 (Quotient)
- Remainder = 0 % 26 = 0 (which corresponds to A)
5. **Collect Letters**: The letters collected are A (from the last step) and A (from the first step), so we read them in reverse order: AA.

Thus, the decimal number 28 is represented as "AB" in this system.

### Another Example:
Convert 703 to this system.

1. **Initial Number**: 703
2. **Subtract 1**: 703 - 1 = 702
3. **Divide by 26**:
- 702 ÷ 26 = 27 (Quotient)
- Remainder = 702 % 26 = 0 (which corresponds to A)
4. **Next Step**:
- 27 - 1 = 26
- 26 ÷ 26 = 1 (Quotient)
- Remainder = 26 % 26 = 0 (which corresponds to A)
5. **Next Step**:
- 1 - 1 = 0 (stop here).
- 0 ÷ 26 = 0 (Quotient)
- Remainder = 0 % 26 = 0 (which corresponds to A)
6. **Collect Letters**: The letters collected are A (from the last step), A (from the second step), and A (from the first step), so we read them in reverse order: AAA.

Thus, the decimal number 703 is represented as "AAA" in this system.

Feel free to provide a specific decimal number if you want me to convert it for you!

Stefan Helzle Thank you very much. I will try this method.

Stefan Helzle  I have below code but its not working in every case

a!localVariables(
  local!splitByArray: char(code(ri!input)),
  local!convertedToBase26: tointeger(rule!SS_getIdByAlphabet(local!splitByArray[1])) * power(26, 2) + 
  tointeger(rule!SS_getIdByAlphabet(local!splitByArray[2])) * power(26, 1) + 
  tointeger(rule!SS_getIdByAlphabet(local!splitByArray[3])) * power(26, 0),
  local!listOfGettableNumbers: tointeger(local!convertedToBase26) + enumerate(ri!iterationCount) + 1,
  local!setOfCodes: a!forEach(
    items: tointeger(local!listOfGettableNumbers),
    expression: a!localVariables(
      local!thirdCharacter: rule!SS_getAlphabetById(tointeger(mod(fv!item, 26))),
      local!secondCharacter: rule!SS_getAlphabetById(
        tointeger(mod(rounddown(fv!item / 26, 0), 26))
      ),
      local!divisionResult: rounddown(rounddown(fv!item / 26, 0) / 26, 0),
      local!firstCharacter: rule!SS_getAlphabetById(
        rounddown(mod(local!divisionResult, 26), 0)
      ),
      concat(
        local!firstCharacter,
        local!secondCharacter,
        local!thirdCharacter
      )
    )
  ),
  local!setOfCodes
)

ri!input is 3 char code e.g "WXY" and ri!iterationCount is number of codes i need. i have given input as "WXY" but it generated wrong codes

You need a recursive expression. I created the helper function

a!localVariables(
  local!newQuotient: floor(ri!quotient / 26),
  local!newRemainders: append(
    ri!remainders,
    mod(ri!quotient, 26)
  ),
  if(
    local!newQuotient > 0,
    rule!SSH_DecTo26Helper(
      quotient: local!newQuotient,
      remainders: local!newRemainders
    ),
    local!newRemainders
  )
)

And call it like this

index(
  char(enumerate(26) + 65),
  rule!SSH_DecTo26Helper(
    quotient: ri!value,
    remainders: {}
  ) + 1
)

Line 2 represents the characters A..Z. The "+1" shifts the numbers from 0..25 to 1..26. ri!value is an integer. In your case this would be the integer primary key generated in the database.