Fetch unique 3 character code

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.